FIT3003 - W6 - Temporal data warehousing

type

Post

Created date

Sep 18, 2022 09:01 AM

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

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 )

Slowly Changing Dimensions (SCD)

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

Implementation of SCD in SQL

Type 0: Keep 舊 value - 易

Type 1: Keep 新 value - 難

Type 2: 加行 - 難

Type 3: 加欄 - 難

Type 4: 加Dim - 易

Type 6 = Type 2 + Type 3 - 難

Creating Fact Tables

Other Case Studies

Case Study (Lab): A Bookshop

Lab-07-Temporal [Solution].pdf

292.6KB

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