Dimensional Modeling - SCD Type 4: Add Mini Dimension

A mini dimension is a concept in data warehousing used to handle certain types of rapidly changing or high-cardinality dimension attributes in a more efficient way. It is essentially a smaller, specialized dimension table derived from a larger parent dimension. Let me break it down carefully:

---

1. Purpose

• Some dimensions in a data warehouse (like Customer or Product) have many attributes, some of which change frequently.

• Storing these frequently changing attributes in the main dimension leads to large, slowly changing dimensions (SCDs).

• Mini dimensions separate these rapidly changing or volatile attributes into a smaller table, reducing the size of the main dimension and improving query performance.

---

2. Key Characteristics

• Derived from a main dimension: Typically contains only a subset of columns, especially those that change often.

• Smaller in size: Easier to maintain and index.

• Linked to fact tables: Fact tables store a foreign key to the mini dimension instead of repeatedly storing all attributes.

• Optimizes slowly changing dimensions (SCDs): Works well for SCD Type 2 scenarios where history tracking is required.

---

3. Example

Imagine a Customer dimension:

CustomerID	Name	Address	MaritalStatus	AgeGroup	LoyaltyLevel

• MaritalStatus and AgeGroup change frequently.

• Name and Address change rarely.

You can create a mini dimension:

Mini Customer Dimension

MiniCustomerID	MaritalStatus	AgeGroup

• Fact table stores MiniCustomerID instead of storing MaritalStatus and AgeGroup directly.

• Main Customer dimension stores static information (Name, Address).

---

4. Benefits

• Reduces storage requirements in fact tables.

• Improves query performance (smaller joins).

• Simplifies handling of frequently changing attributes.

• Keeps main dimension table smaller and more stable.

---

5. Typical Use Case

• High-cardinality attributes like demographics, segment codes, or marketing categories that change often.

• Data marts for sales, marketing, or CRM reporting, where you need historical accuracy but don’t want huge main dimension tables.

---

In short:

A mini dimension is a smaller dimension table that contains a subset of attributes (usually rapidly changing ones) from a larger dimension, linked to fact tables to optimize storage and query performance.