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.