Using ODI 12c for Data Integration with Oracle Hyperion Planning
By Madhu Nair on Jul 07, 2014
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 /X:c:/outlineLoad.exc
- -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 attributes.
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.