For specifics on Dimension Build Settings and Data Load Settings, please reference the Administration section.
EXAMPLE #1 - Essbase Dimension Building Example (Parent/Child Build)
For this section, you need the 3 attached files:
- Test.otl : Shell outline to use for this example, all dimensions populated except 'Product'- which we will be building here.
- PROD1.rul : Rules file to build the Product dimension.
- ProductDimBuild.txt : Data file utilized to build the dimension. Let's analyze the ProductDimBuild.txt file first:
- Products laid out using Parent/Child references with the following column headers and associated data:
"PARENT0,Product"
"CHILD0,Product"
"ALIAS0,Product"
"PROPERTY0,Product"
"UDA0,Product"
Now let's move onto the Rule file for Product:
- In order to utilize the row header from the Text file as my dimension name, you need to enable the following option:
Data Source Properties >
Header Tab >
Input "1" without quotes in the "Record containing dimension building field names:"
(Keep in mind, the syntax needs to be exact, otherwise you will get an error)
- We are using Parent/Child references, we need to enable this option:
Dimension Build Settings >
Dimension Build Settings Tab >
Double Click "Product" dimension name >
Check "Use Parent/Child references" under the Build Method section.
- We are creating an alternate hierarchy we need to allow property changes on the member:
Dimension Build Settings >
Dimension Build Settings Tab >
Double Click "Product" dimension name >
Check "Allow Property Changes" under the Existing Members section
EXAMPLE #2 - Essbase Dimension Building & DataLoad Sample (Building a Dimension and Loading Data from your Data)
This is a little trickier and not always the common practice, but there are times when you need to dynmically build a dimension from the data. For this example, we will build the Product dimension from our data. Keep in mind, this sample data does not include Parent/Child references, therefore, it will create a "flat hierarchy".
For this section, you need the 3 files:
- Test.otl : This is the same outline as above
- PROD2.rul : This is an new Product Rules File
- ProductDimBuildData.txt : Data file utilized to build the dimension and load data.
- In order to utilize the row header from the Text file as my dimension name, you need to enable the following option:
Data Source Properties >
Header Tab >
Input "1" in the "Record containing data load field names:"
(Keep in mind, the syntax needs to be exact, otherwise you will get an error, we do not need this ability during the dim build, but we do during the data load)
- Highlight Column 1:
Field Properties >
Dimension Build Properties >
Double Click the "Product" dimension name >
Double Click Level
Make sure 0 is in the Number field
..FYI, a flat hierarchy is the same as all members being at the leaf level (or level 0).
- Once the above step is complete, cycle through all other columns and "Ignore field during dimension build"
- Since We are using Level references, we need to enable this option:
Dimension Build Settings >
Dimension Build Settings Tab >
Double Click "Product" dimension name >
Check "Use Level references" under the Build Method section.
You can also sort the members here, if desired.
Let's switch gears and load this same file as data. You can toggle your view between Data Load Fields and Dimension Build Fields. Since our data was set up efficiently from our source system and the column headers came in as the dimension names, this step just became easier. The only thing you need to consider is whether to append your data to existing records or overwrite. In this case, I will be overwriting, but this is how you do it:
Data Load Settings >
Data Values Section >
Overwrite Existing values
Add to Existing Values
Subtract from existing values
Remember, we loaded this data to the level 0 location, in order to see the data at the hierarchical rollups, we need to run a calc script, calc-all will work in this case.