As i had written previously here, one of the methods of loading Essbase cubes is by using the Hyperion Data Integration Management and its corresponding Essbase Adapter. In that blog entry i had written about the capabilities of Hyperion DIM product (which is nothing but Informatica underneath). Today, lets see how we can go about loading a simple Essbase Cube using the Essbase Adapter. In order to load an Essbase Cube(using the Essbase Adapter) we would have to be aware of 3 different loading mechanisms that are specific to Hyperion DIM. They are
1. Type 1 – Here, all the dimensions are treated normally and the measures are treated as different columns. One would have to have a seperate column populating the lowest level for each dimension and a seperate column for each of the measures. For example, our source should table should look like this
In this case, our fact table has 3 dimension keys CITY, PRODUCT and MONTH. And, we also have 2 more columns SALES and COGS where both of them correspond to measures. If you have data in this format then use this load technique to load the data into the Essbase Cube/Database.
2. Type 2 – Here all the dimensions are treated normally and the measures are populated in a single column. And the different measures are identified by another column. For example, our source table populating the cube should look like this.
As you see above, our fact table has 4 dimension keys CITY, PRODUCT, MONTH and ACCOUNT. We have only one measure and the type of measure is identified by the ACCOUNT column. So, if you have source data in this fashion, use Type 2 method to load your Essbase cubes.
3. Type 3 – This is for handling value based hierarchies in the source data. So, if your source data contains data with parent child columns (hierarchy is obtained by traversing the parent child columns) then use the Type 3 method to load your essbase cubes.
In our example, lets start with loading a simple Essbase cube/database using the Type1 data load method. Our sample cube contains 2 dimensions LOB and YEAR. There is also another ACCOUNT dimension with only one measure AMOUNT. Our fact table contains data in the below shown format
So, import this table into the source. Then import our target Essbase cube (in our case, the dimensions of the cube have already been loaded i.e the essbase cube already has an outline). When you import an Essbase Cube ensure that you are importing it as Type1.
And finally, this is how your final cube would look like within Hyperion DIM.
Now, create a direct one to one mapping between your fact table and your Essbase Cube
Once this is done, just execute the above mapping using the Workflow Manager.
In the session log you can find out how many rows have been loaded and applied. Now, the question is how does this work. Since, we already have the outline defined, what Essbase Adapter does is it checks the lowest levels of each dimension hierarchy and loads the corresponding data from the fact table. And then it builds up the other summaries based on what summarization has been defined for each of the levels.