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.
![notion image](https://www.notion.so/image/https%3A%2F%2Ffile.notion.so%2Ff%2Ff%2Fe1c5ba0e-562a-49b0-8505-ce5e38fc061b%2Ff08766fd-2067-4a2e-83aa-973ff829c926%2FUntitled.png%3Ftable%3Dblock%26id%3Dd115dc6e-f155-429f-94a2-53c71756de92%26spaceId%3De1c5ba0e-562a-49b0-8505-ce5e38fc061b%26expirationTimestamp%3D1725789600000%26signature%3DXwipAt9MDbr-7ov5OUS0boCAP0HoOwH_3dotc9WVFDQ?table=block&id=d115dc6e-f155-429f-94a2-53c71756de92&cache=v2)
Therefore, you can have a multiple dims to drill down, forming a hierarchy.
![notion image](https://www.notion.so/image/https%3A%2F%2Ffile.notion.so%2Ff%2Ff%2Fe1c5ba0e-562a-49b0-8505-ce5e38fc061b%2Fac549de0-43ee-4322-93e0-4c4825ef1deb%2FUntitled.png%3Ftable%3Dblock%26id%3D76e34b5e-320b-426d-b3f7-1d5244f038f3%26spaceId%3De1c5ba0e-562a-49b0-8505-ce5e38fc061b%26expirationTimestamp%3D1725789600000%26signature%3DN1ku1MIwRh105uEA3FSHn3T8Oqu6NjU4fUD234CL9Jk?table=block&id=76e34b5e-320b-426d-b3f7-1d5244f038f3&cache=v2)
Such that, the results look as follow:
![notion image](https://www.notion.so/image/https%3A%2F%2Ffile.notion.so%2Ff%2Ff%2Fe1c5ba0e-562a-49b0-8505-ce5e38fc061b%2F96b7137e-9d99-4cf2-ad95-c53c803be78d%2FUntitled.png%3Ftable%3Dblock%26id%3D71e33875-c132-4884-80bd-41eda53a3e64%26spaceId%3De1c5ba0e-562a-49b0-8505-ce5e38fc061b%26expirationTimestamp%3D1725789600000%26signature%3DIL3aDTPzhCl6qlTZFAOrnTx3byrWvMgREdX1rkNlhbY?table=block&id=71e33875-c132-4884-80bd-41eda53a3e64&cache=v2)
Summary and Comparison between non-hierarchies and hierarchies
![notion image](https://www.notion.so/image/https%3A%2F%2Ffile.notion.so%2Ff%2Ff%2Fe1c5ba0e-562a-49b0-8505-ce5e38fc061b%2Fd3872bce-820d-4f46-9fa6-d7005ffffe19%2FUntitled.png%3Ftable%3Dblock%26id%3D29b43237-3975-4944-a72e-6df370129c44%26spaceId%3De1c5ba0e-562a-49b0-8505-ce5e38fc061b%26expirationTimestamp%3D1725789600000%26signature%3Do-7_Gq2Gqf5H2LP1IEsS9OxYvDOivW8VyMQ_nqCLpmI?table=block&id=29b43237-3975-4944-a72e-6df370129c44&cache=v2)
![notion image](https://www.notion.so/image/https%3A%2F%2Ffile.notion.so%2Ff%2Ff%2Fe1c5ba0e-562a-49b0-8505-ce5e38fc061b%2F8aaed9c0-8416-4cd8-8bec-0e6abbe30958%2FUntitled.png%3Ftable%3Dblock%26id%3D772d8e12-4d9c-44e2-9b7c-29a0b06e4cd9%26spaceId%3De1c5ba0e-562a-49b0-8505-ce5e38fc061b%26expirationTimestamp%3D1725789600000%26signature%3D5bHZS3e1Ybdgh56EMLPJeMzXFROxEGitNb76c9YirxU?table=block&id=772d8e12-4d9c-44e2-9b7c-29a0b06e4cd9&cache=v2)
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.
![V1. has more fact measure](https://www.notion.so/image/https%3A%2F%2Ffile.notion.so%2Ff%2Ff%2Fe1c5ba0e-562a-49b0-8505-ce5e38fc061b%2Fb7ca2576-ff06-4fb3-b0f5-03fb30bbaf62%2FUntitled.png%3Ftable%3Dblock%26id%3Df2c097d3-eaf6-473c-8f20-07506d1343ac%26spaceId%3De1c5ba0e-562a-49b0-8505-ce5e38fc061b%26expirationTimestamp%3D1725789600000%26signature%3Dd5LSbUDmKJSR0TG_2Vpv4_02A_lJRVuhvZpr9l2usec?table=block&id=f2c097d3-eaf6-473c-8f20-07506d1343ac&cache=v2)
![notion image](https://www.notion.so/image/https%3A%2F%2Ffile.notion.so%2Ff%2Ff%2Fe1c5ba0e-562a-49b0-8505-ce5e38fc061b%2Fd7522aa7-2377-495e-9c27-03baa9b7cdc2%2FUntitled.png%3Ftable%3Dblock%26id%3D30975363-bd30-45d2-b4fb-71fbd2131fd1%26spaceId%3De1c5ba0e-562a-49b0-8505-ce5e38fc061b%26expirationTimestamp%3D1725789600000%26signature%3D0t6-wI_VPe4WESJTAOEp4kwC_x564NPRn1FOxt34AtM?table=block&id=30975363-bd30-45d2-b4fb-71fbd2131fd1&cache=v2)
![notion image](https://www.notion.so/image/https%3A%2F%2Ffile.notion.so%2Ff%2Ff%2Fe1c5ba0e-562a-49b0-8505-ce5e38fc061b%2Ff325aa6e-0faa-4150-8362-98f22b0983fc%2FUntitled.png%3Ftable%3Dblock%26id%3Daa8dba64-7639-4db8-bf44-fc1c897b7045%26spaceId%3De1c5ba0e-562a-49b0-8505-ce5e38fc061b%26expirationTimestamp%3D1725789600000%26signature%3D6pnY-xA_CGzT4ULmY8tWuvc919F2wn9yZ7tyq4vp9bI?table=block&id=aa8dba64-7639-4db8-bf44-fc1c897b7045&cache=v2)
Comparison between Determinant vs. Non-Determinant Dimensions
![notion image](https://www.notion.so/image/https%3A%2F%2Ffile.notion.so%2Ff%2Ff%2Fe1c5ba0e-562a-49b0-8505-ce5e38fc061b%2F29457667-642a-4e64-9b7c-f9fee5d2ab85%2FUntitled.png%3Ftable%3Dblock%26id%3Dc4443208-e2a7-4566-a294-ccd8e7872a7e%26spaceId%3De1c5ba0e-562a-49b0-8505-ce5e38fc061b%26expirationTimestamp%3D1725789600000%26signature%3DGFTrb-9LE0mBvcnZbKuMxt3qMoJCy42U5b4qEcEMsOA?table=block&id=c4443208-e2a7-4566-a294-ccd8e7872a7e&cache=v2)
Determinant Dimensions vs. Pivoted Fact Table
Case study (Lab): PTE Academic Test - Determinant Dimensions vs. Pivoted Fact Table
Determinant Dimensions vs. Pivoted Fact Table
![notion image](https://www.notion.so/image/https%3A%2F%2Ffile.notion.so%2Ff%2Ff%2Fe1c5ba0e-562a-49b0-8505-ce5e38fc061b%2F5d910794-3a89-440d-8a82-06a28e69ab79%2FUntitled.png%3Ftable%3Dblock%26id%3D8dd5d740-13cb-44b2-8fd5-cd54ad8d8d7f%26spaceId%3De1c5ba0e-562a-49b0-8505-ce5e38fc061b%26expirationTimestamp%3D1725789600000%26signature%3DeGHSIikigd1XxaMQ90pJ9fbvTTsrr9RXBiPIqiFF_OE?table=block&id=8dd5d740-13cb-44b2-8fd5-cd54ad8d8d7f&cache=v2)
![notion image](https://www.notion.so/image/https%3A%2F%2Ffile.notion.so%2Ff%2Ff%2Fe1c5ba0e-562a-49b0-8505-ce5e38fc061b%2F1b6164b2-f243-4279-961e-f9edf250bdba%2FUntitled.png%3Ftable%3Dblock%26id%3D6b452004-46f0-4785-a599-b3d2f890e4db%26spaceId%3De1c5ba0e-562a-49b0-8505-ce5e38fc061b%26expirationTimestamp%3D1725789600000%26signature%3D15YV8WnBKoHnAYTkmIC2RkCVN1dlKyRGzo9cq-K0pGA?table=block&id=6b452004-46f0-4785-a599-b3d2f890e4db&cache=v2)
![notion image](https://www.notion.so/image/https%3A%2F%2Ffile.notion.so%2Ff%2Ff%2Fe1c5ba0e-562a-49b0-8505-ce5e38fc061b%2F2ac7d911-928c-44d9-9e55-13d157a3d545%2FUntitled.png%3Ftable%3Dblock%26id%3Dad74e880-c73d-42eb-a422-de7875dcf8d9%26spaceId%3De1c5ba0e-562a-49b0-8505-ce5e38fc061b%26expirationTimestamp%3D1725789600000%26signature%3DLLVn4W0xi0q62TQKBndsU_WqPOGYapma_aNPDyREHa4?table=block&id=ad74e880-c73d-42eb-a422-de7875dcf8d9&cache=v2)
![notion image](https://www.notion.so/image/https%3A%2F%2Ffile.notion.so%2Ff%2Ff%2Fe1c5ba0e-562a-49b0-8505-ce5e38fc061b%2Fe0293975-bc80-406b-b631-e325f0622777%2FUntitled.png%3Ftable%3Dblock%26id%3D7c87aa11-0ca2-4b1c-b74a-c0dfda733bfb%26spaceId%3De1c5ba0e-562a-49b0-8505-ce5e38fc061b%26expirationTimestamp%3D1725789600000%26signature%3DGMlv3r0nTiJems1UU46n1QBYxFq0rvN9Ee7uKOn_AT4?table=block&id=7c87aa11-0ca2-4b1c-b74a-c0dfda733bfb&cache=v2)
![notion image](https://www.notion.so/image/https%3A%2F%2Ffile.notion.so%2Ff%2Ff%2Fe1c5ba0e-562a-49b0-8505-ce5e38fc061b%2F430f3b71-5e0b-4919-a2e1-8d403103d7ac%2FUntitled.png%3Ftable%3Dblock%26id%3D04bce0f9-7076-4b7c-905b-2640793320f5%26spaceId%3De1c5ba0e-562a-49b0-8505-ce5e38fc061b%26expirationTimestamp%3D1725789600000%26signature%3DwVianH-p585e2U1gJ5wCVhDPJTN2cu-0LYWzsLZK_RI?table=block&id=04bce0f9-7076-4b7c-905b-2640793320f5&cache=v2)
Result for including a det. dim
![notion image](https://www.notion.so/image/https%3A%2F%2Ffile.notion.so%2Ff%2Ff%2Fe1c5ba0e-562a-49b0-8505-ce5e38fc061b%2Fafbd8b54-ec0f-40ee-b930-06eca4dab429%2FUntitled.png%3Ftable%3Dblock%26id%3Dd2395adf-dc0b-4a9b-9ea8-82efddf2f757%26spaceId%3De1c5ba0e-562a-49b0-8505-ce5e38fc061b%26expirationTimestamp%3D1725789600000%26signature%3D89y3RJ-9mb2mHBbp1-oRXuuwzFGtrz_eghZHrLESOr8?table=block&id=d2395adf-dc0b-4a9b-9ea8-82efddf2f757&cache=v2)
Result for including a Pivoted Fact Table
![notion image](https://www.notion.so/image/https%3A%2F%2Ffile.notion.so%2Ff%2Ff%2Fe1c5ba0e-562a-49b0-8505-ce5e38fc061b%2Faddea356-6533-4feb-8151-4d5cecf7b04c%2FUntitled.png%3Ftable%3Dblock%26id%3D06c9075c-55b6-4017-bc4f-79c1adf8ae6a%26spaceId%3De1c5ba0e-562a-49b0-8505-ce5e38fc061b%26expirationTimestamp%3D1725789600000%26signature%3DK_Db_R1mr2nDgeGh4jyk0BX4HtP6Yxzj7ag7XMR_7wM?table=block&id=06c9075c-55b6-4017-bc4f-79c1adf8ae6a&cache=v2)
Detailed procedure
![notion image](https://www.notion.so/image/https%3A%2F%2Ffile.notion.so%2Ff%2Ff%2Fe1c5ba0e-562a-49b0-8505-ce5e38fc061b%2Ff19fcdda-8c24-48a0-8536-b8b6f4144fc2%2FUntitled.png%3Ftable%3Dblock%26id%3D38c6505e-38aa-4c64-b7eb-9ea201a9da00%26spaceId%3De1c5ba0e-562a-49b0-8505-ce5e38fc061b%26expirationTimestamp%3D1725789600000%26signature%3D1-hVSW88BXCH0PMe3aY7-RxRh5XQrHdRY-RZLNbGwNA?table=block&id=38c6505e-38aa-4c64-b7eb-9ea201a9da00&cache=v2)
Comparison between Determinant Dimensions vs. Pivoted Fact Table
![notion image](https://www.notion.so/image/https%3A%2F%2Ffile.notion.so%2Ff%2Ff%2Fe1c5ba0e-562a-49b0-8505-ce5e38fc061b%2F1cd057ae-2f0d-4690-bf89-9f8273295a39%2FUntitled.png%3Ftable%3Dblock%26id%3D9973c0d2-445f-40c5-af16-9824fc0f1c4a%26spaceId%3De1c5ba0e-562a-49b0-8505-ce5e38fc061b%26expirationTimestamp%3D1725789600000%26signature%3DEOkPUbkxwqG9K5aitMBcNc-uoB5VerlA_7ztM_PyW8o?table=block&id=9973c0d2-445f-40c5-af16-9824fc0f1c4a&cache=v2)
- 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