🕵️‍♂️ Dimensions Deep Dive & SCDs

🕵️‍♂️ Dimensions Deep Dive

In Chapter 15, we learned that Dimensions answer the Who, What, Where, and When. Now, let's look closer at the different types of Dimensions and how they behave.

🎭 1. Slowly Changing Dimensions (SCD)

Dimensions don't change very often, but they do change. A customer might get married and change their name, or move to a new city. How a Data Warehouse handles that change is called SCD (Slowly Changing Dimensions).

Type 1: Overwrite (No History)

You simply erase the old data and write the new data over it.

• Analogy: Using a pencil and an eraser in your address book.
• Result: You lose all history. If a customer moves from Delhi to Mumbai, all their past sales in Delhi will now look like they happened in Mumbai.

Type 2: Add New Row (Full History)

You keep the old row, and add a completely new row for the same person, marking the old one as "inactive" and the new one as "active".

• Analogy: Creating a brand new page in your address book and writing "OLD" on the previous page.
• Result: You have a perfect historical record. (This is the most common method in Data Warehouses).

Type 3: Add New Column (Partial History)

You keep the same row, but add a new column for "Previous City" next to "Current City".

• Analogy: Crossing out the old address and writing the new one right next to it.
• Result: You can see the current and the immediately previous state, but nothing before that.

🏷️ 2. Special Types of Dimensions

Sometimes, Dimensions don't fit the standard mold. Here are the special cases you need to know:

Conformed Dimension

A dimension that is shared across multiple Fact tables perfectly.

• Example: The Date dimension. You use the exact same Date table for both the Sales Fact Table and the HR Fact Table. It is the ultimate "Single Source of Truth."

Degenerate Dimension

A dimension that doesn't have its own separate table because it has no extra attributes. It just sits directly inside the Fact table.

• Example: An Order_Number or Invoice_ID. It's not a measurable fact (you don't SUM order numbers), but it doesn't need its own table either.

Junk Dimension

A single table created to hold random, miscellaneous "yes/no" flags or small text fields so they don't clutter up the Fact table.

• Analogy: The "junk drawer" in your kitchen where you put random batteries, rubber bands, and pens.
• Example: Is_Discounted (Yes/No), Payment_Type (Cash/Card).

Role-Playing Dimension

A single Dimension table that plays multiple "roles" in the same Fact table.

• Example: The Date dimension. A Sales Fact table might link to the Date table twice: once for Order_Date and once for Ship_Date. The single table is "playing two roles."

🪜 3. Dimension Hierarchies

Dimensions often naturally group together in a Hierarchy (a logical flow from big to small). This is what allows managers to "drill down" in a report.

Example Hierarchy: Location

• Top Level: Country (India)
• Next Level: State (Maharashtra)
• Lowest Level: City (Mumbai)

When a business user looks at a dashboard, they might see Sales by Country. If they click "India", the hierarchy tells the reporting tool to break the data down by State, and then by City.

🧪 Practice Drill

// Try answering these:
// Q1. A customer changes their last name. The business wants to maintain a perfect historical record of all their purchases under their old name, as well as new purchases under their new name. Which SCD Type should they use?
// Q2. An invoice number is stored directly in the Fact table because it has no other descriptive attributes. What type of dimension is this?
// Q3. A company creates one central `Date` dimension and uses it for Sales, HR, and Inventory fact tables. What is this called?
// Q4. A Fact table has an `Order_Date` and a `Delivery_Date`. They both point to the exact same `Date` dimension table. What is this concept called?
// Q5. Match the SCD Type to its behavior:
// | Behavior | SCD Type |
// |---|---|
// | Keeps limited history by adding a "Previous_Value" column. | ? |
// | Destroys history by updating the data in place. | ? |
// | Preserves full history by adding a brand new row. | ? |

← Previous Topic | Next Topic → Next Topic