DIMENSIONS in EssBase
Essbase dimensions are classified as standard dimensions and attribute dimensions . standard dimensions are further classified into sparse and dense dimensions. For every unique sparse member combination , an index will get formed and block will be form for the same combination
Number of potential indexes=number of potential blocks
= number of members from one sparse dimension with
Number of members from other sparse dimension and so on
In the below example , assi9gned products is sold on one month and it is not sold in other months , we have two measures , sales and this company cells the products in all states . sample data
Jan2007 coke ap sales 1000
feb2007 pepsi ap sales 1000
mar2007 mdew ap sales 1000
apr2007 orange ap sales 1000
may2007 mango ap sales 1000
imagine the same data UP,TN DELHI,. So there are 40 records in the source file .
MARKET&MEASURES ARE DENSE DIMENSIONS (ASSUME)
PRODUCT&TIME ARE SPARSE DIMENSIONS (ASSUME)
THERE ARE 5 unique sparse combinations (jan 2007, coke feb 2007 pepsi
May2007 , orange)
Potential indexes are 25 . potential blocksare25
Actual indexs are 5, actual blocks are 5
data exits for majority of the member combinations . dense dimensions with member combination . dense dimensions effect the size of block.
Number of cells =multiply the number of members one dense dimension with number of members from other dense dimension and so on
Each cell occupies 8 bytes
Block size = number of cells *1.
The ideal block size 8k to 100k
Dense dimension will decide the number of cells and block size.in the above example let’s assume to members from measures dimension and 4 members from market dimension . there will be 8 cells per block and each cell occupies 8 bytes . the total block size is 64 bits
- Double click on the outline . click properties tab . scroll down . under down data storage select “true ” for auto configure , that means the system will select the sparse and dense select “false ” for auto configure and we can select either sparse or dense
- Double click on properties of database click statistics tab, to see the block size .etc.
The dimensions are characteristics of other standard dimensions . attribute cannot exits on its own always associated with a base standard dimension . attributes cannot exits on it own . always associated with a base standard dimension . attributes are always sparse of nature . we don’t load data for attributes member combinations . we load data in standard number combination .
- Attribute dimensions must exits in the end of the outline .
- The members in attribute dimensions will not have any consolidation operator .
- The storage property for attributes dimension members is dynamic calc.
- Attribute dimension most associated with only one standard dimension
- An attribute dimension must be associated with sparse dimension only .
- Where as a base sparse dimension can have any number of attribute dimensions .
- Only level 0 members can be associated with level 0 members of base dimension
- There are 4 types of attribute dimensions available . those are text , numeric ,Boolean and date . by default the attribute type is “text”
- There are 5 in-built calculations available . those are SUM,COUNT,MIN,MAX and AVERAGE.
To create attribute dimension: Select the lost dimension in the outline and select “ATTRIBUTE” from the tool bar.
To associate to a base member: Select a standard space member, Right click and click Edit member properties and click attributes tab. Select the attribute dimension from the right hand side window and click assign. Click ok.
To Associate attribute member to base members: Right click on the level 0 member from standard space dimension (from the above step). Click edit member properties. Click associate tab. Select level 0 members from right hand side and select again. Click ok.
Creating Attribute members using Rules files:
This rules file create members under base dimension, creates member under attribute dimension and also associates attribute members to base members.
SODA COKE COKE-1L 1L
SODA COKE COKE-2L 2L
SODA PEPSI PEPSI-1L 1L
SODA PEPSI PEPSI-2L 2L
- Open data prep editor.
- Open data file (or click file->, write the SELECT statement in the select window)
- Map the first column to Gen 2, map the second column to Gen 3, map the third column to Gen 4, and for the last column, For the last column, select the product dimension and under file type, double click on the attribute dimension and select the required attribute dimension. The member will be same as previous column.
- Click OK.
- Click options. Dimension build settings, Dimension build settings tab again. Select the products dimension, Under Attribute members, select “Allow association changes”. Click OK. Validate and save the rules file.