Business Intelligence – Oracle

Archive for December 15th, 2008

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.

Posted in All Posts, EPM, Hyperion Essbase | 2 Comments »