Hierarchies

What and Why Hierarchies?

Like normalisation in ERD, having a hierarchy means that you drill down the information into a deeper level. We do this for the sake of efficiency.

For example, imagine a million rows in this table, in terms of storage, you do not want to store many duplicate values.

Therefore, you can have a multiple dims to drill down, forming a hierarchy.

Such that, the results look as follow:

 

Summary and Comparison between non-hierarchies and hierarchies

Determinant Dimensions

What is Determinant Dimensions?

• A Determinant Dimension (or a Determinant Attribute) must be used in retrieving the fact, in order to make the retrieved data more meaningful.

• In other words, you MUST always add this dim to perform any meaningful calculation on the fact measure.

• And we should say that it is critical to use the Determinant Attribute in the query, either as a filtering mechanism (in the WHERE clause) or in the display (in the SELECT clause) in the SQL command.

Case study (Lecture Activity): Petrol - Determinant Dimension

Problem: Analysis will be UNREASONABLE if reporting without petrol type.

Solution: Add a MUST-ADD dimension, in which we call this as determinant dim.

Determinant vs. Non-Determinant Dimensions

Case Studies

Case study (Lecture): Olympic - Determinant vs. Non Determinant Dimensions

There are two versions of star schema: one with det dim and one without det dim.

Comparison between Determinant vs. Non-Determinant Dimensions

Determinant Dimensions vs. Pivoted Fact Table

Case study (Lab): PTE Academic Test - Determinant Dimensions vs. Pivoted Fact Table

Result for including a det. dim

Result for including a Pivoted Fact Table

Detailed procedure

Comparison between Determinant Dimensions vs. Pivoted Fact Table