DW - Maintaining Dimension Hierarchies

Data is the business asset which is audited and protected. It is very important to design our database correctly, up to whatever normal form we can bear.

What are Dimension Hierarchies?

In the data warehouse concept, understanding of dimension hierarchies are most important part to give a beauty in our data warehouse or data mart model. 
Before design your data model to maintaining the dimension hierarchies, you must have the better understanding of the business concept and data flow because dimension hierarchies play a huge role in query performance for a modern DW/BI system. For example, pre-computed aggregations are one of the most valuable tools to improve query performance which is stored for intermediate hierarchy levels and transparently used in queries.

Important point: In dimension hierarchal data model, a unique primary key must be identified at each level and if these keys are artificial surrogate keys, then they should be hidden from the business users in the final single, flat de-normalized dimension table in the presentation layer of the data warehouse. In a geography dimension hierarchal data modal is the best example to explain its importance because city name alone is not an identifier column; it needs to be some combination of city, state, and perhaps country.

From the beginning of the data model, you must have to maintain the product dimension hierarchies and should need to think about the cons and pros of the purposed hierarchal relationships because a dimension may be contain two or more logical levels. The recommended sequence for creating logical levels is to create a parent level and then create child levels, working down to the lowest level.

The multidimensional data model is composed of logical cubes, measures, dimensions, hierarchies, levels, and attributes. The simplicity of the model is inherent because it defines objects that represent real-world business entities and business analysts know which business measures they are interested in examining, which dimensions and attributes make the data meaningful, and how the dimensions of their business are organized into levels and hierarchies. Dimensional attributes help to describe the dimensional value. They are normally descriptive, textual values.

An analytic database contains snapshots of historical data, derived from data in a legacy system, transactional database, syndicated sources, or other data sources. Three years of historical data is generally considered to be appropriate for analytic applications.

To identify the hierarchies that organize the levels within each dimension. To identify the relationships within each dimension and their hierarchies, we will group the levels in the correct order of summarization and in a way that supports the identified types of analysis.

In the Product hierarchy, the rollup sequence from the base to the top level is:

Business Unit -> Buyers --> Category --> Sub Category --> Items

In the above dimension hierarchal data model, you can see that Item Dimension stay in the granular level of the product hierarchal chain and easily capable to introduce its top level information.  To know more, click on 

1. Data Warehouse Architecture and Multidimensional Model. 
2. Data Warehouse - Dimension tables.
3. Data Warehouse - Fact tables.
4. Data Warehouse - Conceptual Modeling.
5. Data Warehouse - Star schema.
6. Data Warehouse - Snowflake schema.
7. Data Warehouse - Fact constellations. 
8. Collaboration of OLTP and OLAP systems. 
9. Major differences between OLTP and OLAP. 
10. Data Warehouse - Multidimensional Cube