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
Case Study (Lecture): A Bookshop (using bridge table)
Temporal Dimensions
Slowly Changing Dimensions (SCD)
Summary (SCD)
Tables
Slowly Changing Dimensions (SCD)
SCD includes 6 types (i.e., Type 0, 1, 2, 3, 4, 6).
Implementation of SCD in SQL
Creating Fact Tables
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
- Author:Jason Siu
- URL:https://jason-siu.com/article/15740c8e-2f56-4d83-9eb1-ac36aeaec63f
- Copyright:All articles in this blog, except for special statements, adopt BY-NC-SA agreement. Please indicate the source!
Relate Posts
FIT3003 - W8 - CUBE & ROLLUP in SQL (With partial CUBE and partial ROLLUP) (W8)
FIT3003 - W7 - Determinant dimensions
FIT3003 - W8 - SQL Notes about Cumulative and Moving Aggregates
FIT3003 - W8 - SQL Notes about RANK() OVER (PARTITION BY x ORDER BY y)
FIT3003 - Unique SQL functions in Oracle Database
FIT3003 - W11 - Active Data Warehousing