Slowly Changing Dimensions (SCD)

Context: FIT3003_MOC · Building Dimension Tables assumes a member’s attributes never move — SCD is the answer when they do · (Chapter 6; verified against the W12 revision chapter) Parent Framework: Star Schema

Quick Revision

• 🎯 Objective: fold the temporal (historical) aspect of a dimension record into the warehouse ➔ a measure must be reportable against the attribute value that was true at the time of the fact, not the value true today.
• 📦 Core Components: Types 0 / 1 ➔ no history at all | Types 2 / 3 / 6 ➔ history kept inside the dimension | Type 4 ➔ history split into a separate table (a bridge / weak entity).
• ⚡ Key Constraint: overwriting a changed attribute silently re-prices the past — every historic fact row is then reported at the current value.

📝 How It Works

1. The failure it fixes — Bookshop case study

• Temporal attribute ➔ an attribute whose value has a life-time (C1’s price \45.95$isvalidonlyJan2007–Jul2007)\cdot \ast \ast temporaldimension\ast \ast ➔adimensionwhose\ast \ast record\ast \ast hasalife-time\cdot thelife-timeisthe$(\text{StartDate}, \text{EndDate})$ pair.
• “Slowly” ➔ for records that change slowly over time (a book’s price) ⛔ not applicable to rapidly changing data · original types 1, 2, 3; newer types 0, 4, 6.
• Operational source ➔ $\text{BOOK}(\underline{\text{BookID}},\text{BookTitle},\text{Author})$ joined to $\text{BOOKTRANSACTION}$, with price history held apart in $\text{BOOKPRICEHISTORY}(\underline{{\text{BookID}}^{\ast },\text{StartDate},\text{EndDate}},\text{Price},\text{Remarks})$.
• The naive star ➔ $\text{BookSalesFACT}(\underline{{\text{TimeID}}^{\ast },{\text{BranchID}}^{\ast },{\text{BookID}}^{\ast }},\text{Number\_of\_Books\_Sold})$ with the current Price stored flat inside $\text{BookDIM}$.
• Symptom ➔ CSIRO Diet (C1) sells far more in Dec2007, yet the report shows \45.95$everymonth;\ast HarryPotter6\ast reports$$30.95$inMar2008thoughitsoldat$$10.00$ — arithmetically correct, commercially wrong.
• C1’s real history ➔ Jan2007–Jul2007 \45.95$full\cdot Aug2007–Oct2007$$36.7520%$off\cdot Nov2007–Jan2008$$23.00$\ast \ast halfprice\ast \ast \cdot Feb2008–Dec9999$$45.95$ full ➔ the Dec2007 spike was the discount.
• Open-ended sentinel ➔ the current price row carries $\text{EndDate}=\text{Dec}9999$, so “the price now” is a range test, not a max().
• After the fix ➔ a second measure $\text{Total\_Sales}$ can be added to $\text{BookSalesFACT}$ once the price valid at sale time is reachable.

2. The type ladder

• Type 0 — original ➔ stores the initial value of the record and never touches it again.
• Type 1 — latest ➔ overwrites with the latest value (H6 ➔ \10.00$). Shared weakness of 0 and 1 ➔ neither records the history of changes at all.
• Type 2 — versioned rows ➔ history is not separated from the main dimension; new records keep being added: $\text{BookDIM}$ gains $\text{StartDate},\text{EndDate},\text{Price},\text{Remarks},\text{CurrentFlag}$ and a sequence number is appended to the original BookID (C1_1 … C1_4, H6_1 … H6_3, DV_1) — each version is its own member, and the fact’s FK points at the version: Dec2007 sales carry C1_3, Mar2008 sales C1_4.
• Type 3 — previous column ➔ a simplification of Type 2 assuming the complete history is not necessary: one row per member, CurrentPrice beside PreviousPrice (C1 \45.95 / $23.00$\cdot H6$$10.00 / $30.95$\cdot DV$$27.95 /$ Null when never changed).
• Type 4 — history table ➔ a new dimension holds the full change history; $\text{BookDIM}$ keeps only the stable attributes and the price columns move to $\text{BookPriceDIM}$. The same book keeps one BookID.
• Type 4 for a changed business rule (W11) ➔ when Semester 1’s end date moves (15 Jul ➔ 30 Jun from 2021), $\text{SemesterHistoryDIM}(\underline{{\text{SemesterID}}^{\ast },\text{VersionNo}},\text{StartYear},\text{EndYear},\text{StartDate},\text{EndDate},\text{Remark})$ keeps both versions, EndYear null marking the one in force ➔ Active Data Warehousing.
• Type 6 — 2 $+$ 3 ➔ no separate identifier for the same book, but the entire history is kept: one row per version, each carrying its own CurrentPrice and the PreviousPrice before it (C1: Null ➔ $45.95$ ➔ $36.75$ ➔ $23.00$): $\text{BookDIM}(\text{BookID},\text{BookTitle},\text{Author},\text{StartDate},\text{EndDate},\text{CurrentPrice},\text{PreviousPrice},\text{Remarks},\text{CurrentFlag})$.

3. Type 6 breaks the star’s cardinality

• The problem ➔ BookID now repeats across versions, so $\text{BookDIM}$ — $\text{BookSalesFACT}$ is no longer $1$–$m$ but $m$–$m$: a fact row’s BookID matches every version of that book.
• Three fixes ➔ ① add a new surrogate key to $\text{BookDIM}$ (Surrogate Key) ② add $\text{StartDate},\text{EndDate}$ to the fact, beside BookID ③ add an associative (bridge) table between $\text{BookDIM}$ and the fact (Bridge Tables).

⚙️ Core Implementation

🔹 Type 4 — the shape the lecture builds

Mermaid erDiagram — history split out of BookDIM

erDiagram
  TIME_DIM      ||--o{ BOOK_SALES_FACT : qualifies
  BRANCH_DIM    ||--o{ BOOK_SALES_FACT : qualifies
  BOOK_DIM      ||--o{ BOOK_SALES_FACT : qualifies
  BOOK_DIM      ||--o{ BOOK_PRICE_DIM  : "price over time"
  BOOK_SALES_FACT {
    VARCHAR2 TimeID FK
    VARCHAR2 BranchID FK
    VARCHAR2 BookID FK
    NUMBER Number_of_Books_Sold
  }
  BOOK_DIM {
    VARCHAR2 BookID PK
    VARCHAR2 BookTitle
    VARCHAR2 Author
  }
  BOOK_PRICE_DIM {
    VARCHAR2 BookID PK
    DATE StartDate PK
    DATE EndDate PK
    NUMBER Price
    VARCHAR2 Remarks
  }

💡 Common Mistake: Minting a new BookID per price change ➔ that is the Type 2 move; Type 4 exists precisely so the book keeps one identity and the fact’s FK stays stable.

🔹 Type 4 — the correct report (range join $+$ listagg)

Price valid in the sale month, every price listed

select F.TimeID, F.BranchID, F.BookID, B.BookTitle, B.Author,
       listagg(P.Price, ';') within group (order by P.Price) as Price,
       F.Number_of_Books_Sold
from BookSalesFact F, BookDim B, BookPriceDim P
where F.BookID = B.BookID
and B.BookID = P.BookID
and to_date(F.TimeID, 'MonYYYY') >= to_date(P.StartDate, 'MonYYYY')
and to_date(F.TimeID, 'MonYYYY') <= to_date(P.EndDate, 'MonYYYY')
group by F.TimeID, F.BranchID, F.BookID, B.BookTitle, B.Author,
         F.Number_of_Books_Sold;

• Why listagg ➔ the fact is at month grain; when more than one price period overlaps a month the range join returns several prices, and listagg folds them into one cell (Report 4: C1 in Jan2008 ➔ $23.00;$45.95) instead of duplicating the row. 💡 Common Mistake: Comparing MonYYYY strings directly ➔ 'Dec2007' > 'Mar2008' alphabetically; convert both sides with to_date before the range test.

⚖️ Core Decision Matrix

Type	Where history lives	Rows per member	Recovers the price at sale time?	Cost
0	nowhere — initial value frozen	1	❌	reports drift as reality moves
1	nowhere — overwritten	1	❌	the past is silently re-priced
2	in the dimension, as extra rows with suffixed IDs	many	✅ full	one book is split across version IDs; CurrentFlag finds “now”
3	in the dimension, as extra columns	1	⚠️ one step back only	anything older than the previous value is lost
4	in a separate dimension	1 $+$ $n$ history rows	✅ full	one more join (a range join) per historic query
6	both (2 $+$ 3)	many, same BookID	✅ full $+$ previous value	dimension–fact becomes $m$–$m$ ➔ needs a fix (§3)

When It Flips: Type 3 is enough only while "the previous value" is the whole business question. The moment a report must span more than one change — C1 went full price ➔ $20\%$ off ➔ half price ➔ full price — Types 2, 4 or 6 are the only correct answers.

⚠️ Common Mistakes

• 💡 Treating Type 0 and Type 1 as different for reporting ➔ they differ in which single value survives, not in whether history exists; both answer historic questions wrong.
• 💡 Keying a Type 6 fact on BookID alone ➔ the ID repeats per version, so each fact row joins every version and the measure multiplies — apply one of the three §3 fixes.
• 💡 Calling Type 4’s history table a plain dimension ➔ its key is the composite $(\text{BookID},\text{StartDate},\text{EndDate})$; it is a weak-entity / bridge-shaped table hanging off the parent, not a second axis of the star.

🧠 Active Recall

The fact already stores TimeID. Why can it not just look up the price for that month, instead of the warehouse carrying an SCD at all?

Answer

• Short answer: because a flat Price column in the dimension holds one value with no time attached — there is nothing for TimeID to be matched against.
• Why: A dimension attribute is unqualified by time ➔ $\text{BookDIM}.\text{Price}$ describes the book, not the book-on-a-date, so the join returns the same figure for every TimeID. The temporal key must be stored ➔ Types 2, 4 and 6 all introduce $(\text{StartDate},\text{EndDate})$ so the sale month can fall inside a range. Type 3 fakes it ➔ PreviousPrice gives an ordering with no dates, so it can rank two values but never place a fact between them.

Type 2 and Type 4 both keep the complete history. On what grounds does the design choose between them?

Answer

• Short answer: on whether the member may keep one identifier — Type 2 mints a suffixed ID per version (C1_3, C1_4), Type 4 keeps C1 and versions a separate table.
• Why: Type 2 moves identity into the fact ➔ the fact’s FK names the version, so the join is a plain equality, but “all sales of C1” must re-assemble C1_1…C1_4. Type 4 keeps the star’s grain ➔ $\text{BookDIM}$ stays one row per book and the FK never changes; the price is reached by a range join on the sale month, at the cost of an extra join (and listagg when periods overlap a month).

Type 6 needs no separate identifier per version. What does that cost the star, and how is it repaired?

Answer

• Short answer: the dimension–fact relationship becomes $m$–$m$; repair with a surrogate key, dates in the fact, or a bridge table.
• Why: Repeated BookID ➔ four C1 rows share one ID, so a fact row keyed only on BookID matches all four. Each fix restores a unique match ➔ a surrogate key makes each version unique · $(\text{StartDate},\text{EndDate})$ in the fact selects the version by date · a bridge resolves the $m$–$m$ explicitly.