Essbase - Load and Export SQL Data

Essbase can load and export data from/to relational database directly. Need to configure the ODBC driver before the data load and export.

• Configuring Data Sources on Windows

In the Windows Server, select Start, then Administrative Tools, and then Data Sources (ODBC).


Select "System DSN", you can find a system data source called ODS was already here. It is the DataDirect ODBC drivers provided by the Essbase installation. You can click "Add..." to add another data source.


If your data source is Oracle, select DataDirect 7.0 Oracle Wire Protocol (Or other data source for other database) and then click "Finish".



In the General Tab, input the Data Source Name, Host, Port Number and SID.


Switch to Security Tab, input the User Name here. And then click "Test Connect" for testing.


Input the Password and then click "OK"


It shows the following message if the information is correct. Click "OK" to continue.


Click "OK" to confirm, and then you can find the new added data source.

• Configuring Data Sources on UNIX (Version 11.1.2.3)

In the UNIX server, open the $ARBORPATH/bin/.odbc.ini file and add or change the data source configuration. Make sure the data source name is under [ODBC Data Sources] and it invokes the correct database driver. For example.

[ODBC Data Sources]

Oracle Wire Protocol=DataDirect 6.1 Oracle Wire Protocol

Roll down to the [Oracle Wire Protocol] part. If you have another name of the data source, copy this part of configuration and then rename it.

Change the configuration for the HostName, LogonID, Password, PortNumber and SID.

Then we can try to load data from SQL in EAS. Login to EAS, create a rules file for the target database.

Click File > Open SQL in the rules file.

Confirm the target database information and then click OK to continue.

You can find the SQL data sources you created before. Select Oracle Wire Protocol, input the SQL statement for the source data. And then click "OK/Retrieve" to retrieve data.

For Oracle database, you can use Oracle Call Interface (OCI) as an alternative to ODBC to significantly improve data load and dimension build performance. Use the following syntax for the Data Source Name: host:port/Oracle_service_name


Input User name and Password for the SQL. Then click OK.

You can find the source data can be retrieved in the rules file. Actually, the field names of the SQL table will be generated to the field name of the rules file automatically. Click "Field Properties" to have a check.

You can do some simple mappings in the Global Properties Tab.

Switch to the tab "Data Load Properties", you can check or change the field definition here.

Press "Next >>" for the other fields, make sure to check the box "Data field" for the data field. Then click "OK" to continue.

Click "Data source properties" to change the data source properties, normally it doesn't need to change if the source is relational database. (It may need to change the settings for the flat file source.)

Click "Data load settings" for the data load configurations.

There are three data load options in "Data Load Values" tab, normally we use the setting of "Overwrite existing values". But in some cases if there are duplicated dimension combination records in the source data and we want to aggregate the data together, we use "Add to existing values" option.

If we want to clear data combinations before the data load, switch to the "Clear Data Combinations" tab. For the cases if the source data changed frequently and we need to reload data after the change, we will need to clear the existing data first and then reload the data again, make sure there is no dirty data left in the target environment. Select the Combinations to clear from the Dimension list and then continue.

"Header Definition" - If the number of fields of the source data is less then the dimension numbers of the target Essbase cube, you need to define the header definition. For example, if you have no version field in the source data, but you have the "Version" dimension in the target Essbase cube. Then you need to specify one of the version to load the data. (e.g. Final)

After all the settings are done in the rules file, you can save to continue.

Input the File name and click "OK"

Now we can load the data from the relational database with the saved rules file. Right click the target database, click "Load data..."

Select SQL as the Data Source...and then click "Find Rules File"

Select the rules file we created before.

Then scroll to the right of the Data Load setting, input the SQL User Name and Password, click "OK"

The data load log shows as below.

You can find the data was loaded to Hyperion Planning/Essbase successfully.

Next, we can try how to export Essbase data to relational database with DATAEXPORT command in a calculation script. First, create a calculation script as below.

We can export Essbase data to relational database with the following command. "Oracle Wire Protocol" is the ODBC driver we created before, and we also need to specify the table name, user name and password of the target database.

Save the script and then execute, you can find the data output to the relational database successfully.