type
Post
Created date
Sep 18, 2022 09:01 AM
category
Data Science
tags
Data Warehousing
status
Published
Language
English
From
School
summary
slug
password
Author
Jason Ching Yuen Siu
Priority
Featured
Featured
Cover
Origin
Type
URL
Youtube
Youtube
icon
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
Note: here we note .
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 theSELECT
clause) in the SQL command.
Case study (Lecture Activity): Petrol - Determinant Dimension
Determinant Dimension
Determinant vs. Non-Determinant Dimensions
Easy as it seems, sometimes we can choose not to include a dominant dimension, for the two reasons:
- Complexity of values needed for fact measure in the table is low (e.g., min and max price in Petrol Case VS number of medal in Olympic).
- Only has simple calculation like COUNT, instead of Avg.
We illustrate the necessity of including Det. Dim. through the case of Olympic, shown below:
Case Studies
Case study (Lecture): Olympic - Determinant vs. Non Determinant Dimensions
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
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
- Author:Jason Siu
- URL:https://jason-siu.com/article/b75b3ffc-4a5c-46bf-a405-5e45e15d61d8
- Copyright:All articles in this blog, except for special statements, adopt BY-NC-SA agreement. Please indicate the source!
Relate Posts