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
 

Temporal data warehousing using bridge table

Summary

A temporal data warehousing uses the concept of the Bridge Table (or a Weak Entity), where the history is maintained in a bridge table.
  • Maintaining the history of certain attributes is important in order to make associative analysis more accurate when analysing the reports produced by the fact and dimensions.
  • However, certain degree of caution when joining the fact table and the temporal dimension, especially when the level of granularity of time between the fact and the temporal dimension is not the same.
  • Temporal data warehousing is also known as Slowly Changing Dimensions(SCD).
  • Different types will server different purposes of the data warehousing

Temporal Attributes

notion image
notion image
notion image

Case Study (Lecture): A Bookshop (using bridge table)

notion image
notion image

Temporal Dimensions

notion image
notion image

Slowly Changing Dimensions (SCD)

Summary (SCD)

Tables
We’ve summarized the techniques for tracking dimension attribute changes in Figure 5-17. This chart highlights the implications of each slowly changing dimension technique on the analysis of performance metrics in the fact table. ( The Data Warehouse Toolkit )
We’ve summarized the techniques for tracking dimension attribute changes in Figure 5-17. This chart highlights the implications of each slowly changing dimension technique on the analysis of performance metrics in the fact table. ( The Data Warehouse Toolkit )
 (Here)
(Here)
notion image
 
notion image

Slowly Changing Dimensions (SCD)

SCD includes 6 types (i.e., Type 0, 1, 2, 3, 4, 6).
notion image
notion image
notion image
notion image
notion image
 

Implementation of SCD in SQL

Type 0: Keep 舊 value - 易

notion image

Type 1: Keep 新 value - 難

notion image
 

Type 2: 加行 - 難

Type 3: 加欄 - 難

notion image
notion image

Type 4: 加Dim - 易

Type 6 = Type 2 + Type 3 - 難

notion image
notion image

Creating Fact Tables

notion image
notion image
notion image
notion image
 
 
 

 

Other Case Studies

Case Study (Lab): A Bookshop
Case study (Lecture Activity): Sessional Jobs Case Study
Monash University employs its students to do various jobs, such as tutoring, programming, etc. These jobs are called sessional jobs. For each sessional job, students need to sign a contract. For example, to do tutoring (one or more units), the student will sign a contract with Monash for one semester.
These sessional workers (e.g. sessional tutors) need to claim their work hours every week. This claim will need to be approved by a designated person in the faculty. Every fortnight, the sessional workers will get their pay.
The operational database, which keeps track of this system, is shown by the following E/R diagram
notion image
notion image
 
 
FIT3003 - W5 - Level of aggregationThe Nothingness of Money - By Lawrence Yeo