Hyperion Essbase 9.3.1 – OBE Series – Designing Essbase Databases – BSO Outlines & Data Load – Global Schema – Part 2 – Building Dimensions
Posted by Venkatakrishnan J on December 14, 2008
In the last article we saw how to go about designing the outline for an Essbase Block Storage Cube. Today we shall see how to go about loading the various dimensions using rule files. Rule Files, as the name suggests, helps in loading data as well as dimension members using certain rules. Loading a dimension means, loading the various hierarchies that comprise a dimension. In our example, we have 2 hierarchies for Customer dimension, 1 hierarchy for Channels, 1 hierarchy for Time and 1 for Product dimension. Lets start with Customer Dimension first. Before starting to build these dimensions, the first step is to identify which would be the primary hierarchy and which would be the alternate hierarchy. In Essbase, it does not matter what hierachy type it is since every hierarchy is treated in the same way. But it is mandatory to understand how the members would be present in each of the hierarchies. For example, in some cases, we can have hierarchies wherein the members within & across the hierarchies are completely unique. But in some cases, we can have the same members shared across multiple hierarchies. Lets look at the screenshot from AWM of the Customer Dimension hierarchies.
As you see, we basically 2 different hierarchies sharing the same level column called SHIP_TO. So, let us start with building the first hierarchy Shipments. In order to do that, we shall create a new rule file. In the rule file let us enter the SQL below.
SELECT TOTAL_CUSTOMER_ID, TOTAL_CUSTOMER_DSC, REGION_ID, REGION_DSC, WAREHOUSE_ID, WAREHOUSE_DSC, SHIP_TO_ID,SHIP_TO_DSC FROM CUSTOMER_DIM
Ensure that you are pointing to the right SQL source. Once that is done, click on OK/Retrieve which will retrieve the data back within the rule file.
Since we are loading a dimension, we need to set the data source property as Dimension Build.
After that, we need to set the dimension build specific properties. There are 6 types of dimension build methods available in Essbase. They are shown below
Generation References - This facilitates column based loading from data sources. Typically used while loading a level based hierarchy. Level References - This is similar to Generation Type loading. But this is bottom up loading instead of a top down approach. Typically used while loading a level based hierarchy. Parent Child References - This is used when source data is in the form of a parent child hierarchy. Typically used while loading a value based hierarchy. Add as Sibling With match - Typically used while loading members as siblings to other members. Add as a Sibling to Lowest level - Typically used while loading members as siblings to the lowest level members. Add as a Child of - Typically used while loading members as child of a spefic member.
In our case, since we are loading a level based hierarchy, we shall be using Generation References method. Also, there are quite a few options that we have within each dimension build method. They are given below
Each option has its own significance. The significance of each method is given below.
Allow Moves - This will allow a member to change its parent. Allow Property Changes - This will allow changes to the property of a member like Alias, Aggregation Property, Time Based Aggregation etc Allow Formula Changes - This will allow formula for a member to be dynamically populated Allow UDA Changes - This will allow UDA changes to a member
So, in our case this is what we shall use for the primary Segments Hierarchy.
Once this is done, we need to set the Field Based property for each column obtained from Our SQL Query. This is to designate the member, Alias etc. In Essbase, the same member name cannot be used across dimensions. So, we shall not be using the Dimension IDs as our member names as other dimensions would have the same ids. So, for each column containing the dimension ids, let us just ignore them.
So, we shall mark Field2, Field4, Field6 and Field8 as Generation2, Generation3, Generation4 and Generation5. So, our rule file should look like the one shown below
Then validate the rule file and just save it as Segments. Once that is done, right click on the Global database and click on load data. Then choose the rule file and start building the dimension.
So, effectively we should get the dimension as shown below in the outline.
Now, that we have built the primary hierarchy, the next step is to build the alternate hierarchy. Building the alternate hierarchy is pretty tricky. One of the main reasons for that is that the same members(the lowest SHIP_TO level) would have to be made as shared in the alternate hierarchy. If we use the same logic as above, the lowest level members would move to the new hierarchy instead of getting shared. So, we shall start with a rule file which will build the 1 st 3 generations (without the SHIP_TO level) in the alternate Market-Segment hierarchy. The rule file would be similar to the one above without the SHIP_TO column.
Now, lets load the new hierarchy through the new rule file. The outline should look like the one shown below
Now, the next step is to somehow automate the process of making the SHIP_TO members to automatically come under the Generation5 of the alternate hierarchy as shared members. This is done through another rule file. This rule file would be loaded as a parent child dimension load. The main reason for splitting this up is, only a Parent Child dimension load would automatically share an existing member in the outline. For level and generation references methods it is not possible to share a member that already exist in the outline i.e to share a member the parent member should also come in the same sql. So, use the below SQL in the new rule file.
SELECT ACCOUNT_DSC, SHIP_TO_DSC FROM CUSTOMER_DIM
Also, use the parent child dimension build property. Do not set the Allow moves property.
Now, load the data from this new rule file. This will result in the outline shown below
As you see the new members in the alternate hierarchy get shared automatically. Just make the alternate hierarchy not to roll up to the topmost member. That is done by changing the aggregation operator of Total Market.
Now that we have built the customer dimension, we shall see how to go about building the Product, Time and Channel dimensions in the next blog entry.