Business Intelligence – Oracle

Archive for the ‘Informatica’ Category

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.

    

Posted in All Posts, Hyperion DIM, Hyperion Essbase, Informatica, OBI EE Plus | 2 Comments »

Hyperion Data Integration Management (DIM) – An option for loading data into Essbase cubes – First Impressions

Posted by Venkatakrishnan J on April 22, 2008

Before Oracle actually acquired Hyperion, Hyperion and Informatica had an OEM agreement(this is still in place like the OEM between Oracle and Informatica for BI Applications) which allowed Informatica to load data directly into Essbase Cubes. Also, Hyperion rebundled the software (under the purview of the OEM) as Hyperion Data Integration Management. What this suite basically does is, it provides ETL options to a variety of Hyperion related products like Essbase, Planning and Financial Management. This suite basically contains the following components.

     1.   Informatica PowerCenter Client
     2.   Informatica PowerCenter Server
     3.   Informatica PowerCenter Repository Server

Also, it contains adapters to the following Hyperion Components

     1.   Hyperion Enterprise
     2.   Hyperion Essbase
     3.   Hyperion Planning
     4.   Hyperion Financial Management
     5.   Hyperion Translation Manager

Using the above adapters, one can load data directly into Essbase, Planning etc using the Informatica. On the same lines, Oracle Data Integrator (formerly sunopsis) also has the above adapters. You can download the above components from Edelivery. Remember to read the licensing aggrement atleast once since this entails the use of a third party software (Informatica). The installation of the software is pretty straightforward. All we would need is to install the DIM first and then followed by the adapters. Then the adapters would have to be registered with the repository server in order for the connections to work. The architecture for the Hyperion Data Integration Management is shown below

    

One of the very good features of the Hyperion Data Integration Management is that it has the capability to automatically normalize an Hyperion Cube i.e if you want to load a data warehouse from a Hyperion Cube, all you would have to do is to use the Normalize option which would automatically split the cube into its corresponding dimension and Fact tables. For example. look at the screenshot below. This shows you a simple cube containing the Products and Channels Dimensions. As you would see at a high level, it contains the cartesian product of all the dimension attribute values (very similar to what we have in OLAP Views of Oracle OLAP). I just hope we can have this feature out of the box (instead of going through an adapter).

    

Below is another sample screenshot showing how the normalization would split up the cube into its corresponding relational sources (based on the demo application).

    

I can imagine a lot of use for this DIM product since it has excellent Administration as well as Data Load/Unload capabilities on a Hyperion Essbase Cube. I believe ODI (Oracle Data Integrator) also has this capability. Yet to give that a whirl. To summarize, there are different ways of loading data into an Essbase cube. They are

1. Using Hyperion Administration Services
2. Using Hyperion Integration Services
3. Using Hyperion Data Integration Management and its Essbase Adapter
4. Using Oracle Data Integrator and its Essbase Adapter

Depending on your needs/environment, you have that flexibility to use the tool of your choice. In later blog entries lets see how to go about loading an Essbase Cube using the Hyperion DIM product. 

Posted in All Posts, Hyperion Essbase, Informatica | 9 Comments »