Sales Management Customer Relationship Human Resources Business Performance BI & Data Quality IT Tools & Vendors

Sign-in   Register
Establishing 'Making it Happen' as a 'Formal & Predictable' Discipline
  Dimensional Model Simple Hierarchy  

ENCYCLOPEDIA→   Enterprise Intelligence  →   -  Data Analysis/OLAP  →   -  Business Hierarchies in OLAP and Data Warehouse  → 

OLAP and Data Warehouse Dimensional Model Hierarchy

The subject of hierarchies is relevant to both OLAP and Execution-MiH Delivery - Data Warehousing/Marting. Modeling of data is done, both in DW and OLAP, keeping the hierarchies in mind. However, OLAP is the platform where the hierarchies are manifested in their final shape for the purpose of analysis.

The subject of hierarchies is relevant to both OLAP and Data Warehousing/Marting. Modeling of data is done, both in Data Warehouse and OLAP, keeping the hierarchies in mind. However, OLAP is the platform, where the hierarchies are manifested in their final shape for the purpose of analysis.

Definition of DW/OLAP Hierarchy

Hierarchies are the paths over which any data (OR measure) is summarized. As you perform various Vertical and Horizontal navigation operations, you move along with these paths of hierarchies. 'Office—> City—> State—> Country—> Continent—>' Globe is one such example of a hierarchy for a location dimension. In this hierarchy, office is at the lowest level of the ladder and globe at the highest. So you can roll-up sales revenue measure figures from office level, way up to the global level.

In terms of basic definitions linked to a DW/OLAP hierarchy-

A dimension level, which is participating in the hierarchy (OR a step in the ladder of hierarchy) is called a Level . For example 'city' in location dimension hierarchy will be a level. The sequence of these levels is called the Path . For example- the 'Office—> City—> State—> Country—> Continent—> Globe' is the hierarchy path. The first OR the lowest level of hierarchy is called Leaf (office in the example) and highest OR last level is called Root (Globe in the example). Within the two consecutive levels, the higher level is called the Parent level and lower is called Child (for example 'City' is parent for 'office' and child for 'state' level).

Business hierarchies are not limited to Business Intelligence. Business hierarchies exist since the data model was invented. If you look at your typical Entity Relationship diagram in your transaction system data models, you have child and parent entities. Child and parent entities are nothing, but representation of a hierarchy. One has to take a note, that Business intelligence dimensional modeling in most cases, does not invent hierarchies. These hierarchies exist in the data models of transaction OR source systems, and organizational data models & business processes. For example, if you haven't got a linkage between a Sales unit to a Business unit defined in your transaction system, don't expect your OLAP to have that hierarchy defined. In other words, just like data, the input on hierarchies 'mostly' comes from the source OR Source Systems Mapping.

With reference to entity-relationship diagrams in transaction systems- A child and parent entities are reflected in your database design as referential integrity. For example- In the referential integrity you have 'office master table', having a 'city-code field', which is linked to the 'city-master table'. 'City master table' will have the 'state-code field', which will be linked to the 'state-master table'. This is an example of office—>city—>state hierarchy of location. A transaction system is able to navigate the information from the lowest level of hierarchy to highest level. That is why, a data warehouse can have the storage in dimensional (de-normalized) model form OR relational model (normalized) form, without impacting the concept of hierarchy.

As you will see in the Additivity of Measures chapter, the hierarchies drive the additivity OR aggregation rules in big way. You should be reading the hierarchy chapter before you, go to the measures chapter.

There are different kinds of hierarchies, and each hierarchy has a different role and a context. Before we go into this classification, let us list three main factors, on which different kind of hierarchy structure are created.

  • The level-cardinality: This means - if a child level in hierarchy can belong to one OR more than one dimension levels.
  • The instance-cardinality: This means - if a child instance in hierarchy can belong to one OR more than one parent instances.
  • The Analysis criteria: This means- if you are using levels with in hierarchy path for one OR more than one analysis criteria. For example- you can use an office for geography as well as sales organization criteria.

Type of Data Entity Hierarchies

Strict OR Simple Hierarchies

These are the hierarchies, which can be represented by a tree structure, whereby :

  • Each level in the tree has only one possible parent level, AND
  • Each instance can belong to only one defined level AND
  • Criteria for analysis is same.

Therefore, in a simple hierarchy, a child will have only one parent, and parent will have only one child level. The simple hierarchies can be further categorized into symmetric, asymmetric, generalized and non-covering hierarchies.

Non-Strict Hierarchies

In a non-strict hierarchy-

  • Each level in the tree has only one possible parent level AND
  • Criteria for analysis is same, but
  • Each instance can belong to more than one instance in the parent dimension level.

Multiple and Alternate Path Hierarchies

In Multiple and Alternate path hierarchy-

  • Each level in the tree can have more than one possible parent level AND
  • Each instance can belong to more than one instance in the parent dimension level AND
  • Criteria for analysis is same.

Parallel Path Hierarchies

In this hierarchy structure there is flexibility on all factors-

  • Each level in the tree can have more than one possible parent level AND
  • Each instance can belong to more than one instance in the parent dimension level AND
  • Criteria for analysis is different
 

  Dimensional Model Simple Hierarchy  
 
 
All Topics in: "Business Hierarchies in OLAP and Data Warehouse" Chapter
 OLAP Data Warehouse Dimensional Model Business Entity Hierarchy →  Simple -Strict- Asymmetric Symmetric Generalized Hierarchy →  Dimensional Model Business Entity non-Strict Hierarchy →  Dimensional Model Business Entity Multiple-Path Hierarchy →  Dimensional Model Entity Parallel-Path Hierarchy → 
 
 
More on Hierarchies in OLAP & DW
Dimensional Model Simple Hierarchy
Dimensional non Strict Hierarchy
Multiple Path Hierarchy
Parallel Dimensional Hierarchy
BUY BI & Data Management Vendors & Tools Evaluation Kit
Read more...
BUY largest on-line Data-Quality Management Kit
Read more...
Additional Channels
Principles & Rules
Free Templates
Glossary
Key Performance Indicators



Most Popular Zones with list of pages crossing 25000 hits  →→→ 
Maximising Sales Performance
Sales Material logistics and Distribution
Sales Leads follow-up and Closure
Sales Channel Data Management
Sales productivity
Sales force density
Read more...
  Customer Relationship Management
Customer Service and Support - Strategic Role
Customer Value and Profitability- BI
Customer Segmentation Parameters
Customer Knowledge and Organizational Knowledge
Customer Service and Support Overview
Read more...
  Human Resources & Leadership
Maximize the output first and then the potential
Lead Change
Act with Decisiveness
Feedback does not mean only negative feedback
Leadership Development- Setting the Context
Read more...
 
 
Business Performance & Planning
External Info Assessment Report
Strategic Planning leadership commitment
Shifting the mind-set to leading Indicators- KPIs
Internal Info Assessment Report
SWOT Assessment Report
Read more...
  Business Intelligence & Data Quality
Business process based data-marts
Data Quality Approach Finalization
Metadata Management definition - What is metadata?
Data Warehouse Testing is Different
Commentary is must in a Scorecard
Read more...
  IT Vendors & Tools Management
OLAP Performance Management
Delivery Evaluation Performance warranty
Vendor Company structure Evaluation
OLAP Scalability
Metadata Tool administration Security
Read more...