Hyperion Essbase 9.3.1 – OBE Series – Designing Essbase Databases – BSO Outlines & Data Load – Global Schema – Part 1
Posted by Venkatakrishnan J on December 11, 2008
The OBE series on Oracle OLAP here inspired me to actually write down this new series for Essbase. In this article/blog series we shall see how to go about designing BSO & ASO cubes based on the data from the Global Schema. We shall see how to go about designing outlines(along with the sparse and dense settings), designing rule files, loading data and also currency conversions. All these would be based on the global schema (which you can install from the link above). I would try to cover the various aspects of outline builds and the various properties in the outlines. Let us start with identifying the database tables that we shall be using in this series. Listed below are the tables.
1. CHANNEL_DIM 2. CUSTOMER_DIM 3. PRODUCT_CHILD_PARENT 4. TIME_DIM 5. UNITS_FACT 6. PRICE_AND_COST_FACT
So, basically we would have 4 normal dimensions(Channel, Customer, Product & Time) and an Accounts dimension (or a measure dimension) containing 3 measures (UNITS, UNIT_PRICE and UNIT_COST). UNITS has a grain of all the 4 dimensions (Channel, Customer, Product & Time). UNIT_PRICE and UNIT_COST has a grain of only 2 dimensions (Product and Time). So, to start with lets create a sample Application & database called Global->Global using Block Storage. The first step in loading a block storage cube is in creating the dimensions and identifying which dimension is sparse and which is dense. Typically this activity is the most important step while designing a block storage cube since this is instrumental in determining the Cube Aggregation time (this is referred as calculation time in BSO). So, lets create these dimensions first in the Outline.
Now, the next step is in identifying which dimension is sparse and which is dense. By default, in a Block Storage cube each and every sparse dimension combination is stored in a row-wise format (assume rows in a relational table). Each of these unique combination of sparse members’ memory locations are stored in a easily retrievable index. And the dense dimension member combination is stored as a multidimensional array called as a block. The size of the block determines the data retrieval and calculation performance. Typically, the block size can be anywhere between 8K to 64K(but there are cases wherein block sizes of the order of 200K is desired). In our case, we shall use the below sparse and dense settings.
Channel Dimension – Sparse
Customer Dimension – Sparse
Product Dimension – Sparse
Time Dimension – Dense
Measures Dimension – Dense
In order to modify this we need to go to the outline properties and set the sparse/dense settings.
Once this is done, the next step is to determine an approximate number of dimension members for each dimension that we would expect to store in our cube. This is something that can be done after loading each dimension or before. We shall do that now. All we need to do is to arrange the sparse dimensions in the increasing order of number of stored members. In our case, Customers would have more stored members than Product and Channel. And Products dimension would have more stored members than Channel dimension. We have done this activity to determine the order of the dimensions in the outline. As a general rule (there can be exceptions), it is recommended to arrange the dimensions in an hour glass format. This is depicted as shown below
So, in our case, arrange the dimensions as shown below. And also make the Measures dimension as the Accounts dimension. The advantage in Essbase is that we have the opportunity to set the aggregation, storage and other properties on a member by member basis. These properties can be automatically loaded through rule files as well. Do not make the 3 measures to roll up. Also, make the Accounts as a label only member.
I wanted to have this as a seperate blog entry since this is very crucial in loading an Essbase Cube. In the next blog entry we shall see how to go about loading the dimensions using rule files. We shall see 2 approaches. One is level based hierarchy rule files and the other is parent based hierarchy rule files.