Business Intelligence – Oracle

Hyperion Essbase 9.3.1 – OBE Series – Designing Essbase Databases – BSO Outlines & Data Load – Global Schema – Part 3 – Building Dimensions

Posted by Venkatakrishnan J on December 15, 2008

In the last article, we saw how to go about building rule files for loading level based primary and alternate hierarchies for the customer dimension. In this article we shall see how to go about building the remaining dimensions. We shall start with the Product dimension first. The source for our Product dimension is PRODUCT_CHILD_PARENT table. As you see, this dimension contains the Product hierarchy as a value based hierarchy. The idea is to build a rule file to load this hierarchy. When the SQL for the rule file is built, it has to be ensured that the parents come first in the Query result. So, we shall be using the query shown below in our rule file to build the hierarchy.

SELECT
NVL(PARENT_PRODUCT,'Product'),
PRODUCT_DSC
FROM
(SELECT PRODUCT_DSC, PRODUCT_ID, PARENT_ID, LEVEL LVL
FROM
PRODUCT_CHILD_PARENT
CONNECT BY PRIOR PRODUCT_ID = PARENT_ID
START WITH PARENT_ID IS NULL) A,
(SELECT PRODUCT_DSC AS PARENT_PRODUCT, PRODUCT_ID
FROM
PRODUCT_CHILD_PARENT) B
WHERE
A.PARENT_ID = B.PRODUCT_ID (+)
ORDER BY A.LVL

As you see above, the query ensures that we are getting the parent and child member name (not the ids) in the query itself. Also, the order by LEVEL ensures that we are loading the parent first. The property for the rule file is listed below

       

       

As you see above, we are using the Parent Child build property to load the value based hierarchy. Save and validate the rule file. Then just load the Product dimension using this rule file. The ouline should look like the one shown below

       

Now that we have loaded the Product dimension, the next step is to load the Channels and Time dimensions. Again, we shall be creating 2 seperate rule files for loading Channels and Time dimensions. Since the procedure remains the same, i would just provide the SQLs used in the rule files along with their properties.

SELECT
YEAR_DSC, QUARTER_DSC, MONTH_DSC
FROM
TIME_DIM

 

SELECT
TOTAL_CHANNEL_DSC, CHANNEL_DSC
FROM
CHANNEL_DIM

       

       

       

       

Now, load both the rule files to the Essbase Outline. This will load all the dimension members of Time and Channels. So, the outline should be complete now with all the dimension members.

       

Next we shall see how to go about loading data into this Essbase Cube. Also we shall be seeing how to go aggregating portions of the Essbase Database using calculation scripts.

Advertisements

2 Responses to “Hyperion Essbase 9.3.1 – OBE Series – Designing Essbase Databases – BSO Outlines & Data Load – Global Schema – Part 3 – Building Dimensions”

  1. Betsy said

    while loading SQL data into Essbase i get the error stating that “There is no data source defined create one to continue”. This is error comes even after creating the DSN. Kindly help me in this regard

    • jb said

      Betsy – I get the same error message. I missing the step on how to get the Global Tables loaded before creating the rule files and SQL.

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: