Business Intelligence – Oracle

Hyperion Data Integration Management 9.3.1 – Loading Data into Essbase Cubes using Type1

Posted by Venkatakrishnan J on May 8, 2008

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.

    

Advertisements

2 Responses to “Hyperion Data Integration Management 9.3.1 – Loading Data into Essbase Cubes using Type1”

  1. […] analyze any dimension members as Type 1 or Type 2. If you had gone through my previous blog entry here, i would have briefly explained what Type 1 and Type 2 actually mean. By default, when you import […]

  2. […] We basically have 2 hierarchies. In the normal Fiscal year terms, January and Febraury months fall under Quarter 1. But for some companies like Oracle where Fiscal and Financial years are different, January and the Febraury months fall under Q3 of the Financial year. Once this done lets load the data using Essbase Integration Services or Hyperion DIM. In my case, i have used the DIM product to load the data. For more details on how to load data using DIM, check my blog entry here. […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: