Multi Valued Dimension
Ch.02: DWH | DWH Components | Data Modeling | Dimension Types
Lesson Notes
Shrunken Rollup Changing Dimension Lesson Notes:
Video
Multi-valued dimensions
When the relationships between the dimension member and the fact are many to many which means the dimension members are lower granularity than the facts.
Fact table should contains one-to-one relationship with the dimension. So, we introduce the Bridge table when we need to related multiple dimensions values with one record.
Multi-valued dimensions Examples
Patients can have multiple diagnoses.
Students can have multiple majors.
customers can have multiple account.
Authors can have multiple publications.
Multi-valued dimensions Article Sales Example
Assume we need to report the sales of article and we have some articles has more than one author.
Each author has weighting factor for each article.
According to the report we need to check each author and associate with the articles they have authored. How can we model this case?
Assume the first article has only one author Moustafa, and the second article has two authors Ahmed & Amr.
Implementation 1
Multi-valued dimensions (Implementation-1)
What are the problems in this implementation?
We can’t get the weighting factor for each author.
Duplicated rows in sales.
Implementation 2
Multi-valued dimensions (Implementation-2)
Final Implementation
Example Reference
- Example in this video taken from this link https://www.nuwavesolutions.com/bridge-tables/
Further Reading
Dimensional Modeling: In a Business Intelligence Environment. The book is free, and you can download it from this link. You can read the following:
- Chapter 6.3.10 Multi-valued dimensions page 288
- Chapter 6.3.11 Use of bridge tables page 291