Welcome to All Things Data Integration: Announcements, Insights, Best Practices, Tips & Tricks, and Trend Related...

Using ODI 12c for Data Integration with Oracle Hyperion Planning

Posted for Ayush Ganeriwal

Though the current Oracle Data Integrator 12c (ODI 12c) release does not include Oracle
Hyperion Planning Knowledge Modules, the Outline Load Utility that comes with Oracle Hyperion
Planning can be used for data integration, loading and extracting
both data and metadata from Oracle Hyperion Planning. This article discusses the use of Oracle Data
Integrator and the Outline Load Utility to achieve end to end data integration
with Oracle Hyperion Planning.

 For data or metadata load use cases ODI is used
to first prepare the required flat file and then kick off Outline Load Utility
process to push data into Oracle Hyperion Planning. For extraction use cases
ODI first runs the Outline Load Utility to extract data or metadata into a flat
file which then transformed the flat file through ODI mapping.

For details on the Outline Load Utility please refer to Planning
Documentation in the Oracle
Enterprise Performance Management documentation library

Loading Data into Oracle Hyperion Planning

In the below ODI package the source data from DB is first
transformed into a comma separated value (CSV) file in the db_to_file mapping
and then Outline Load Utility is run through an OS Command to push the data
into Oracle Hyperion Planning.

In above example the following flat file is generated by the
mapping step.

After the Outline Load Utility execution the data can be
viewed through Oracle Hyperion Planning User Interface.

The OS command used above and its parameter descriptions are
as follows

OutlineLoad.cmd -f:c:\password.txt /A:VisPlan1 /U:demoadmin
/M /I:c:\data\TrialBalanceData.csv /TR /L:c:/outlineLoad.log

- -f = Location of encrypted password file

- /A: = Application name

- /U: = User Name

- /M = Generate header records.

- /I: = Input File

- /L: = Log file

- /X: = Exception file

- /TR = Driver specified in data file. 

Loading Outline Members into Oracle Hyperion Planning

To load outline members, a parent child flat file is
generated through the ODI mapping in the first step and then the Outline Load
Utility is kicked off to create the outline members.

Each of the outline members is specified on a separate line
in the CSV file in the parent child format. There can be additional attributes
included with each member as a comma separate values but if none specified then
default values of the attributes will be used. In this example only the parent
child hierarchy is specified forcing the default values to be used for member

The above flat file is loaded into Oracle Hyperion Planning
outline as follows.

The command and parameters used to load outline members are
as follows.

OutlineLoad.cmd -f:c:\password.txt /A:VisPlan1 /U:demoadmin
/M /I:c:\data\ODIAccounts.txt /D:Account /L:c:/outlineLoad.log /X:c:/outlineLoad.exc

- /D:= Dimension to be loaded

- All other parameters are same as described
earlier in the article

Extracting Data from Oracle Hyperion Planning

Oracle Hyperion Planning uses Oracle Essbase for storing its
data so there could be multiple approaches for extracting date out of Oracle
Hyperion Planning.

1. Using
Outline Load Utility

2. By
executing MAXL or Calc Script

3. Using
the ODI Knowledge Modules for Oracle Hyperion Essbase

In this article we will use the first approach only. The
below package is created with OS Command as the first step to invoke Outline
Load Utility to extract data in a flat file. The flat file is then transformed
and loaded into database in mapping TransformPlanningData.

The below flat file shows the data extracted by the Outline
Load Utility

The command used for data extraction and its parameter
description is as follows

OutlineLoad.cmd -f:c:\password.txt /A:VisPlan1 /U:demoadmin
/ED:c:/data/VisPlan1out /CP:c:/data/VisPlan1Dump.Properties
/L:c:/outlineLoad-out.log /X:c:/outlineLoad-out.exc

- /ED:= Output file name where data is exported

- /CP:= Specifies a file that contains command line arguments that, in
conjunction with command line arguments, constitutes the options for execution.
The properties file has the extra parameter /EDD: that specifies the format of
the exported Planning Driver Member formatted data files as follows

- The remaining parameters are the same as already
described earlier in the article

Extracting an Outline Members out of Oracle Hyperion Planning

Similar to the Data Extraction process, the Outline Load Utility is invoked in the
first step of the package to extract the member metadata in the flat file which
is then transformed and loaded into a database table through the mapping.

The flat file generated contains following data

The Outline Load Utility command and parameters used here
are as follows

OutlineLoad.cmd -f:c:\password.txt /A:VisPlan1 /U:demoadmin /E:c:/data/VisPlan1Account.csv
/D:Account /L:c:/outlineLoad-out.log /X:c:/outlineLoad-out.exc

- /E:= Exports the dimension specified with the /D
switch to the specified output file

- Rest all parameters are same as decribed earlier
in the article


There are no Knowledge Modules available for Oracle Hyperion
Planning in ODI 12c, but the same result can be easily achieved using the
combination of Outline Load Utility and ODI 12c.

Join the discussion

Comments ( 1 )
  • yashwant Thursday, January 8, 2015

    What is the moto behind not giving KM for Hyperion Planning and showing this demo of loading and extracting data and metadata indirectly using Outline load utility and ODI

Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.