Hyperion Enterprise: Batch Load

In order to automate the process of extracting and loading data from Hyperion Enterprise there are a couple of steps that need to be accomplished:

• Data needs to be extracted from Hyperion Enterprise. If you want to automate this you will need Hyperion Autopilot.
• Process the Hyperion Enterprise data with a batch-procedure
• Schedule both steps using Windows Scheduler.

This article describes these tasks individually.

Extract data from Hyperion Enterprise using Hyperion Autopilot

Hyperion Autopilot is a software tool that comes with Hyperion Enterprise and helps you to automate administrative tasks. For every category two tasks are required:

1. Change Category task
2. Extract Enterprise Data task

The Extract Enterprise Data task needs to be configured with the following settings:

• File name: extract the file to the location where CXO can load the file from, name the file similar as the category
• Entity list: specify the list with all entities to extract
• Account list: specify the list with all accounts to extract.
• Start & end period: specify the periods to extract, this is typically first period to last period
• Format: specify a format for extraction, the format needs to meet the following requirements:
  • Precision of 9 decimals ;
  • Scale is put to 0 (units);
  • The Data View is Category-to-Date.

Process the Hyperion Enterprise data with a batch-procedure

To start the extraction of data from Hyperion Enterprise through a scheduled task, you first have to create a batch file with the appropriate command line. Give the batch file a logical name like HE_dataload.bat. You can put it on any location on the CXO application server, but note that the Active Directory user used for the scheduled task has write and execute rights on that folder.

This command-line has the following structure:

"C:\Program Files\CXO Solutions\CXO-Cockpit\Design Studio\CXO.Cockpit.Administration.exe" @app:hetest @cat:ACTUAL @cat:BUDGET @cat:ACT_BUD @trans:1 @process:1

The location of the executable CXO.Cockpit.Administration.exe usually is as in the two examples above, but is can also be on another drive (D:).

The arguments:

• @app: The CXO-Cockpit application to which the data should be loaded, transformed and processed
• @cat: The HE category to load. This parameter needs to be repeated for every category that should be loaded.
• @trans: Indicates if the data should be calculated and transferred to the fact database. Should be 0 or 1.
• @process: Indicates if the cube should be processed using the fact database. Should be 0 or 1.
• @bulk: Indicates if the data file should be uploaded through SQL server-side bulk load. Should be 0 or 1. Note that since version 4.1 this no longer gives any performance benefit. Default = 0 (No).

Scheduling the extraction task

In the Control Panel you can create a task that invokes the batch at a certain moment or with a certain interval. Read more about scheduling task in the Schedule Windows Tasks article.