UsingODI 11g With Hyperion Planning and Essbase 11122-Part2

Using ODI 11g with Hyperion Planning and 2014 Essbase 11.1.2.2 – 11.1.2.2 – Part2 Part2

USING ODI 11G WITH HYPERION PLANNING AND ESSBASE 11.1.2.2 – 11.1.2.2 – PART2 PART2 Abstract This technical article is intended to demonstrate the usage of Oracle Data Integrator (ODI) 11g in conjunction with Hyperion Planning and Essbase 11.1.2.2. Part-2 of this series illustrates data integration capabilities of ODI 11g with Hyperion Essbase 11.1.2.2 with seven typical ETL scenarios. The scenarios discussed can be used as re-usable guidelines to implement advanced cases of the same in a real time data integration project involving Hyperion and ODI.

Saptarshi Bose Oracle EPM Consultant, Wipro Technologies

This D i s c l a i m e r : This

article draws references from ODI 11g documentations from Oracle. At certain points, further reading references are also provided using URLs from ODI online documentation library. Besides, it takes references from certain websites on ODI 11g and 10g.

Using ODI 11g with Hyperion Planning and Essbase 11.1.2.2 – 11.1.2.2 – Part2 Part2

Table of Contents Introduction .............................................................................................. ............................................................................................................................ .............................. 2 Hyperion Essbase Knowledge Modules ................................................. ....................................................................................... ...................................... 2 Hyperion Essbase 11.1.2.2 and ODI 11g Data Integration POC P OC Cases ........................................... ........................................... 2 Case 1: Hyperion Essbase Outline Extraction to a Flat File ............................................ ........................................................ ............ 2 Case 2: Hyperion Essbase Outline Extraction to a RDBMS Table created on the Fly ................... 18 Case 3: Data Load to Hyperion Essbase (without using Rules File) using Join Transformation Transformation on RDBMS Tables ............................................................................... ................................................................................................................... .................................... 29 Case 4: Metadata Load to Hyperion Essbase from a RDBMS Table .......................................... .......................................... 48 Case 5: Metadata Load to Hyperion Essbase from a Flat File .................................................. 63 Case 6: Hyperion Essbase Data Extraction Using Calc Script Method to a RDBMS Table created on the Fly .................................................................................................. .............................................................................................................................. ............................ 67 Case 7: Hyperion Essbase Data Extraction Using MDX Query Method to a RDBMS Table created on the Fly .................................................................................................. .............................................................................................................................. ............................ 79 List of Abbreviations .......................................................................... .............................................................................................................. .................................... 89

pg. 1


Table of Contents Introduction .............................................................................................. ............................................................................................................................ .............................. 2 Hyperion Essbase Knowledge Modules ................................................. ....................................................................................... ...................................... 2 Hyperion Essbase 11.1.2.2 and ODI 11g Data Integration POC P OC Cases ........................................... ........................................... 2 Case 1: Hyperion Essbase Outline Extraction to a Flat File ............................................ ........................................................ ............ 2 Case 2: Hyperion Essbase Outline Extraction to a RDBMS Table created on the Fly ................... 18 Case 3: Data Load to Hyperion Essbase (without using Rules File) using Join Transformation Transformation on RDBMS Tables ............................................................................... ................................................................................................................... .................................... 29 Case 4: Metadata Load to Hyperion Essbase from a RDBMS Table .......................................... .......................................... 48 Case 5: Metadata Load to Hyperion Essbase from a Flat File .................................................. 63 Case 6: Hyperion Essbase Data Extraction Using Calc Script Method to a RDBMS Table created on the Fly .................................................................................................. .............................................................................................................................. ............................ 67 Case 7: Hyperion Essbase Data Extraction Using MDX Query Method to a RDBMS Table created on the Fly .................................................................................................. .............................................................................................................................. ............................ 79 List of Abbreviations .......................................................................... .............................................................................................................. .................................... 89

pg. 1


Introduction This technical article is intended to demonstrate the usage of Oracle Data Integrator (ODI) 11g in conjunction with Hyperion Planning and Essbase 11.1.2.2 for typical ETL (Extract-Transform-Load) scenarios involved in a Hyperion Planning and Essbase project. Part-2 of this series illustrates the data integration capabilities of ODI 11g when used in conjunction with Hyperion Essbase 11.1.2.2 with seven typical use cases. The cases discussed can be be used as re-usable guidelines to guidelines to implement advanced cases of the same in real time project scenarios. A key purpose of this article is to document the implementation steps of a set of POC ( “Proof of Concept”) scenarios to scenarios to demonstrate ODI 11g data integration features and capabilities with Hyperion Planning and Essbase 11.1.2.2 for one of the top US Bank. Demonstration of the POC scenarios to the client management is intended to expand the scope of current Hyperion engagement in the bank in the forthcoming calendar year of 2015.

Hyperion Essbase Knowledge Modules Knowledge Module

Description

RKM Hyperion Essbase

Reverse-engineers Essbase applications and creates data models to use as targets or sources in Oracle Data Integrator interfaces

IKM SQL to Hyperion Essbase (DATA)

Integrates data into Essbase applications.

IKM SQL to Hyperion Essbase (METADATA)

Integrates metadata into Essbase applications

LKM Hyperion Essbase DATA to SQL

Loads data from an Essbase application to any SQL compliant database used as a staging area.

LKM Hyperion Essbase METADATA to SQL

Loads metadata from an Essbase application to any SQ L compliant database used as a staging area.

Hyperion Essbase 11.1.2.2 and ODI 11g Data Integration POC Cases Case 1: Hyperion Essbase Outline Extraction to a Flat File A model folder “ODI_ESSBASE_POC_MODELS” “ODI_ESSBASE_POC_MODELS” is created in ODI DN to host Essbase related models. Inside that folder a model is created named “SampleBasic_Reversed”.

pg. 2

Using ODI 11g with Hyperion Planning and Essbase 11.1.2.2 – Part2

Next, RE is done using the KM – “RKM Hyperion Essbase” .

pg. 3


pg. 4


The RE session is checked from ON.

Once RE is complete, the model is checked from inside the model folder.

pg. 5


pg. 6


This POC case is focused on extracting the Measures dimension; therefore, the Measure datastore is highlighted here.

Next, a target flat file “Meaures_Metadata_Extract_POC.csv” is created in the location showed below and the header rows along with alphabets in the next line are entered. These alphabets r ender the columns as String type automatically when RE is done.

pg. 7


Next, a target file model is created named – “Samp_Basic_Extract_File_Target”.

A new datastore is added pointing to the target file.

pg. 8


File properties are entered.

Next, RE is performed on the datastore.

pg. 9


Post RE, columns of the file are shown up in the datastore as shown below:

pg. 10


Next, an interface named – “POC_MEASURE_DIM_EXTRACT” is created.

Next, mapping is done between source and target. Source: Essbase Measure Datastore Target: Flat file with Measures Properties definition embedded.

pg. 11


pg. 12


Next, Flow tab settings are done. End to end data flow for this POC scenario can be summarized as: Essbase

ODI Staging Layer or Sunopsis Memory Engine


IKM SQL to File Append

Figure 1: Essbase Outline Extraction to Flat File

pg. 13

Flat File


Details on the settings on LKM Hyperion Essbase METADATA to SQL can be read at: http://docs.oracle.com/cd/E28280_01/integrate.1111/e12644/hyperion_essbase.htm In the POC scenario, following settings are done on the LKM –

In the POC scenario, following settings are done on the IKM –

pg. 14


Next, the interface is saved and executed.

pg. 15


Session is checked from the ON –

pg. 16


Session ran successfully. Next, the output file is checked to confirm that the extraction was successful.

pg. 17


Case 2: Hyperion Essbase Outline Extraction to a RDBMS Table created on the Fly This POC case is similar to Case 1, the only difference being the extraction happens on a RDBMS table created on the fly using a Yellow interface in ODI. A new interface named “POC_MEASURE_DIM_EXTRACT_DB” is created under the ODI project “ODI_POC_PROJECT”.

Note: In this case, unlike Case 1, Staging Area Different from Target is not checked and the destination is identified by the Oracle RDBMS logical schema. Next, mapping is done, and target datastore is created dynamically using the source datastore using “Add to Target” option.

pg. 18


pg. 19


Target datastore is named as “MEASURES_EXTRACT_DB” – eventually this would be the name of the target table to be created on the fly when this interface is executed.

Next, on the “Flow” tab, LKM and IKM settings are done. End to end dataflow is described as –

Essbase

ODI Staging Layer on RDBMS

RDBMS Table

Oracle Database Domain


IKM SQL Control Append

Figure 2: Essbase Outline Extraction to a Relational Database Table

pg. 20


pg. 21


Interface is saved, and we can see that a Yellow interface is created.

pg. 22


Next, the interface is executed.

From ON it is observed that 17 rows are processed.

pg. 23


Analyzing the “Session Task Integration” step, the code generated by ODI to create the target table on the fly can be found.

pg. 24


pg. 25


Next, from the “Mapping” tab on the interface target table data is observed to confirm the insertions.

pg. 26


Target table in Oracle database

pg. 27


The table is checked logging in via SQL developer as well.

pg. 28


Case 3: Data Load to Hyperion Essbase (without using Rules File) using Join Transformation on RDBMS Tables In this POC case, two source tables are there namely –  

SRC_PRD_BY_LOC_T – Storing Products sold by location SRC_SALES_T – Storing Sales figures by Products

Aim of this POC case is to load the sales figures by product to Sample.Basic Essbase cube using the two above mentioned source tables. A new model is created named – “SRC_ESSBASE_DATA_LOAD_TABLES” to host the source datastores.

pg. 29


pg. 30


A selective RE is done on the tables mentioned above.

pg. 31


Once RE is complete, the datastores are created within ODI repository. Note, that the constraints associated with the table are also reversed. In case such constraints are not there at source table level, they can be created at ODI level as well.

Data is the source datastores can be viewed from ODI as shown below:

pg. 32


pg. 33


Constraints can be viewed as – as –

pg. 34


Next, a new interface is created with the name – name – “POC_RDBMS_TO_ESB_DATA_LOAD”. “POC_RDBMS_TO_ESB_DATA_LOAD”.

pg. 35


In the “Mapping” tab the two source tables are joined using a filter transformation as shown below –

pg. 36


Then the source and target are mapped -

pg. 37


Here, the end to end data flow can be depicted as – ODI Staging Layer or Sunopsis Memory Engine

RDBMS Tables

LKM SQL to SQL

Essbase

IKM SQL to Hyperion Essbase (DATA)

Figure 3: Load Data to Essbase from RDBMS Tables Next, Settings are done on the “Flow” tab for LKM and the IKM.

pg. 38


pg. 39


pg. 40


pg. 41


Some of the key options to take a note for the IKM are: 



pg. 42

CLEAR_DATABASE :- This will execute the following calculation script commands for block storage cubes (CLEARBLOCK ALL, CLEARBLOCK UPPER, CLEARBLOCK NONINPUT), for ASO cubes cubes one can only select ALL and it will clear all data. MAXIMUM_ERRORS_ALLOWED :- If this is set to 0 it will ignore any errors such as a member not found and keep trying to load.






COMMIT_INTERVAL :- This number will be the amount of records of data that are sent to essbase in a chunk. Setting this amount to a large value can cause performance issues if any records that get rejected. RULES_FILE :- With this IKM, no rules file is required to load data, but an important thing to note is that without a rules file the data will always be sent as “overwrite” and one needs to use a rules file to add to existing data.

Next, the interface is executed-

From ON, it is observed that the interface has failed. Reason is highlighted below.

pg. 43


The data type selected wrongly as shown below caused the interface to fail. For Essbase to store the AMOUNT column it has to be a number.

pg. 44


This is corrected at ODI level –

pg. 45


Interface is saved, closed and opened again such that the change takes effect. Next, it is executed again. This time it runs successfully.

pg. 46


Checking the data connecting the Sample.Basic cube from Smart View shows that intended intersections were loaded properly using the join between the two source tables to fetch the data.

Note: Such an interface will load the data into essbase without any rules file; any da ta which couldn’t be loaded will be logged in the error log file. When a data load runs a chunk of the source data is grabbed, the amount of data in each chunk depends on the value being set in the COMMIT_INTERVAL option of the IKM, the default being 1000 records. What happens now is the data is streamed in using an API until all the records have been processed and the data is then committed to essbase. This process is repeated until all the chunks of source data have been processed. This process works fine unless there are some invalid records e.g. records with members that don’t exist in the dimension it is being loaded against. The

pg. 47


data will not be rejected until the end, and so with the commit interval set to 1000, 1000 records of data will still be sent to essbase and it will be all rejected if any of it is invalid. ODI tries to load each record one by one from the start of the chunk until it has processed all the records in the chunk. So if the commit interval is 1000 then it will try and load each of them 1000 records one by one until it has processed the 1000 records and then goes back into the stream loading of the next chunk, if it hits another error then it will go into single record update again. This adds an extremely large overhead in the processi ng time and it is a really inefficient way of processing data, hopefully this would be rectified soon. But, till then playing around with the commit interval settings, ensuring good source data quality and loads of testing are the only means of using this IKM. Else, using a rules file in the IKM option would help in catching the error in a better way.

Case 4: Metadata Load to Hyperion Essbase from a RDBMS Table This POC case, deals with the process of loading metadata from a RDBMS table to Essbase. Primary requirement for the same is to create an ODBC connectivity or link for Essbase, because, the IKM to be used in the ODI interface demands a SQL type rules file to fire a query on the RDBMS table to pull the metadata. Basically, the IKM works as a wrapper for the rules file. The source table name is “SRC_PRODUCT_METADATA_T”. This table stores product information on Camera line. The “CAMERATYPE” column is the attribute for a specific model.

pg. 48


Steps to create ODBC link are given below: 1.From “Administrative Tools” on Windows “Data Sources (ODBC)” is navigated.

2.Next, the “DataDirect 7.0 Oracle Wire Protocol” is selected.

pg. 49


3.Next, “System DSN” is selected and the driver is setup as shown –

pg. 50


Finally the DSN – ORA_ODIDB_SDSN is created.

pg. 51


Next, EAS is opened to create a SQL type rules file.

pg. 52


The SQL data source is selected and a query is created as shown below.

pg. 53


On doing an “Ok/Retrieve” data is fetched from the RDBMS table as shown-

pg. 54


The hierarchy of cameras is required to be loaded in the “Product” dimension in Sample.Basic; and member 900 is required to be added to the Product hierarchy.

Next, a dimension build rules file “PRDLOAD.rul” is created to build the hierarchy.

pg. 55


Next, a model is created in ODI named – “SRC_ESSBASE_METADATA_LOAD_TABLES”. Then the source table is reversed to create the source datastore.

For the target as model named – “SampleBasic_Reversed_For_MetadataLoad” is created. Sample.Basic is reversed for metadata load purpose. The “Product” datastore under the model would be the target in this case.

pg. 56


Next, an interface is created named- “POC_METADATA_LOAD_TO_SAMPLE_BASIC_FROM_RDBMS”

pg. 57


Mapping is done as shown below –

Here, the end to end data flow can be depicted as – RDBMS Tables

LKM SQL to SQL

ODI Staging Layer or Sunopsis Memory Engine

Essbase


Figure 4: Load Metadata to Essbase from RDBMS Tables

pg. 58


On the “Flow” tab, the dataflow is generated as –

The IKM settings are shown below:

Note: The SQL type rules file “PRDLOAD” is mentioned here directly as it is being picked from the server path. This can be kept at a local path and the fully qualified name can also be mentioned here. The server path option works fine when ODI and Essbase server are installed on the same box.

pg. 59


Next, the interface is saved and executed. From ON, the status is observed. It shows that 7 rows are processed.

pg. 60


pg. 61


From EAS it is confirmed that metadata got added successfully.

Attributes tagging is shown as -

pg. 62


Case 5: Metadata Load to Hyperion Essbase from a Flat File This POC case is similar to case-4, except the source is a flat file. End to End data flow can be depicted as – ODI Staging Layer or Sunopsis Memory Engine

Flat File

LKM File to SQL

Essbase


Figure 5: Load Metadata to Essbase from Flat File Since this case is similar to case-4 all the steps to load metadata are not shown in this document except certain key highlights.

pg. 63


The source file is of the following format –

The “Mapping” window for the interface “POC_LOAD_METADATA_FILE_TO_ESB” created for this POC case is shown below –

pg. 64


The “Flow” tab is shown below along with the IKM is shown below –

Note: The rules file “ProdLoad” is kept at a local path and the fully qualified name is mentioned. The server path option works fine when ODI and Essbase server are installed on the same box. In this case, the ODI installation and Essbase server being connected to are physically separated.

pg. 65


pg. 66


Once the interface is executed all the data gets processed but with a glitch!! The Node member “900” its alias and rollup property is not properly updated –

This doesn’t happen when the rules file is executed directly. This happens only when it is called from the IKM. This is the key issue that was identified while setting up this POC case.

Case 6: Hyperion Essbase Data Extraction Using Calc Script Method to a RDBMS Table created on the Fly This POC case deals with data extraction from Essbase using Calc script DATAEXPORT method and works for BSO cubes only. The DATAEXPORT script first fetches the data in a file and then loads it to a target table created on the fly using a yellow interface. Key assumption: Essbase is hosted in the same server where ODI is installed. Else, this method demands that ODI agent is installed and running in the remote Essbase server. The extract file will also have to write to location which will be accessible by ODI, this is not so bad if ODI is on the same server as essbase but becomes more of an issue if they are separate. To demonstrate this case Essbase was installed on the ODI box and the topology was setup accordingly pointing to Sample.Basic.

pg. 67


pg. 68


Next, a DATAEXPORT CalcScript is written which would fetch the data to a file on the local path.

It is important to use - DataExportDimHeader On; as ODI will consider the first 2 records to be header information. The extract goes to the following folder –

Extract file on running the script looks like the following –

pg. 69


Next, a model is created and Sample.Basic in the local installation is RE.

pg. 70


pg. 71


Next, a yellow interface is created –

pg. 72


pg. 73


End to end dataflow can be depicted as –

Essbase


RDBMS Table




Figure 6: Extract Data from Essbase to a RDBMS Table usi ng CalcScript On the “Flow” tab LKM and IKM settings are done as the following –

pg. 74


pg. 75


Next, the interface is saved and executed. On successful completion of the session, from ON it is observed that there were 6 inserts on the target table.

pg. 76


Data in the target table is viewed from ODI level –

pg. 77


pg. 78


The same is verified from SQL developer as well –

Case 7: Hyperion Essbase Data Extraction Using MDX Query Method to a RDBMS Table created on the Fly This POC case deals with data extraction from Essbase using MDX query. This might not be a very efficient method to extract data from Essbase, but for smaller data chunks can work fine without the following issues coming in the way:  

No need for Essbase server and ODI to be installed in the same box. No need for ODI agent to be running in the Essbase server

First, a new model is created named – “Sample_Basic_Scenario_as_Data”

pg. 79


Customized RE is done. Note the RKM options set to extract the data.

pg. 80


Next, a Yellow interface is created such that the target table is created on the fly copying the source datastore definition.

pg. 81


pg. 82


pg. 83


The data extraction MDX query is created as the following –

The query is run EAS console to give the following result. It shows 12 rows. Therefore, the expectation from the yellow interface is that the target table to be created on the fly should have 12 rows being fetched from Essbase and inserted into it.

This query is saved in the local folder path on the server where ODI is installed –

pg. 84


Next, on the “Flow” tab, LKM and IKM settings are done. End to end dataflow is described as –

Essbase


RDBMS Table




Figure 7: Essbase Outline Extraction to a Relational Database Table using MDX Query

pg. 85


Note the MDX file name and the path are specified in these IKM options

pg. 86


Next, the interface is executed –

12 rows are successfully processed-

The data in the target table created on the fly is checked from the interface “Mapping” window -

pg. 87


The same is checked from SQL developer –

pg. 88

UsingODI 11g With Hyperion Planning and Essbase 11122-Part2

Recommend Documents