Essbase Database - Hierarchy Concept!!

When we talk about Multidimensional Database, we talk about Dimension, Data, Metadata & Hierarchy . Lets talk on about we define the Dimension Structure in Essbase Cube which will give us enough understanding on hierarchy and its impact on data Aggregation/Consolidation inside the Cube.


What is Hierarchy:

For each dimension, you can organize the set of its members in a hierarchy.

The Hierarchy is defined with the relation of Parent and Child

Top to Bottom , goes as Generation Starts with Gen1,Gen2 etc
Bottom to Top goes as level.- Starts with Level0,Level1 etc

In the Above Example : Jan,Feb March- Level0 members. These are also called leaf members. Data in Essbase cube always get loaded at level0 and it gets aggregated to upper level as per the Aggregation/Consolidation operator defined during the Dimension Build.

Same way Year is GEN1,Quater1 is GEN2, Jan/Feb/Mar are GEN3 members.

Quarter1 is Parent of January
January is called Children of Quarter1


Generation:
Generation defines a member’s location within the outline hierarchy from the top of the dimension.

Level:
Level defines a member’s location within the outline hierarchy from the bottom of the dimension.

Genealogy:

Genealogy names relationships between members in the outline.

When we talk about Multi Dimension and Mutili Dimension Database, Hyperion Essbase try to look at your organization data in 6-D 

• Account - This one is the Measures
• Year       - Calendar Year
• Time Period  - Weeks,days,Months,Qtrs,YearTotal
• Scenario - Budget,Forecast,Actual, Budget vs Actual, Budget vs Forecast etc
• Version  - Working, 1st Pass, 2nd Pass, Reviewed, Final
• Entity    - Some company called it Geography, Departments, Divisions etc. all depending on how you want to look at your data. This is a key dimension when you want to track the data in Multiple currency . Each Geo /location/country having different local currencies whereas the Organization to report the Profit& Loss or Revenue in reporting currency (can be USD or INR depending on the Comp requirement)

If  your requirement goes to more dimension- then you can add Custom dimensions like Product,Customer, Project etc. This is purely your organization requirement and design decision stand point.  

Note: the Performance of the Multidimensional database directly depends on how many dimension you are adding and how they are defined in the essbase Cube.

Multidimensional Operations: There are 4 keys operation we talk when someone ask me what can i do better in a multidimensional database.

• Selection (slice and dice) defines a sub cube.
• Aggregation (roll up) defines dimension reduction.
• Navigation to more detailed data (drill down).
• Visualization operations (pivot).

Slice and Dice:

Ex: Sales in California during January and February by product

Aggregation(Roll Up) & Drill Down

Ex: Total Sales by product or  Ex:Supporting data for sales in California during 1st quarter

Pivot:

Ex:Reorientation of the data cube Display of a 3-D data cube in a series of 2-D planes

Continue reading the Next blog to understand the Architecture of Essbase Cube and hoe to create Multi-Dimensional Database using Hyperion Essbase....