Dimension Tables

Dimension tables provide the descriptive context needed to understand and analyze the measurements stored in fact tables. They answer questions such as who, what, when, and where.

While fact tables capture business measurements and events, dimension tables provide the context through which those facts are analyzed.

• The "Nouns": Think of dimension tables as the nouns of the business. Each dimension represents a business entity involved in a business process, such as Customer, Product, Store, Employee, or Date. Dimensions are shared across multiple fact tables to provide a consistent view of the business.
• Descriptive Attributes: Dimension tables primarily contain descriptive attributes used to filter, group, label, and explore facts. For example, a Product dimension might contain product_name, brand, category, and department.
• Table Structure: A dimension table typically contains a surrogate key - usually a generated integer - along with descriptive attributes and, where applicable, the business key from the source system.
• Physical Characteristics: Dimension tables are typically wide and shallow - they contain relatively few rows compared with fact tables but can have many descriptive columns.

Dimensions provide the filtering and grouping capabilities used in Business Intelligence. For example, when a user asks for "Total Sales by Region," the Region attribute comes from a dimension table.

Types of Dimensions

In a robust dimensional model, you will encounter several specialized types of dimensions that solve specific design challenges.

Conformed Dimensions

Conformed dimensions are the "glue" that holds the enterprise data warehouse together. They are dimensions designed to have exactly the same meaning and content regardless of which fact table they join.

Because they are shared, they allow you to easily query and combine data across different, unrelated business processes (e.g., comparing "Sales revenue" vs. "Marketing spend" by Campaign).

Example (A Conformed Date Dimension):

Date Key	Full Date	Year	Month	Day of Week	Quarter
20240101	2024-01-01	2024	Jan	Monday	Q1
20240102	2024-01-02	2024	Jan	Tuesday	Q1

Both a Sales Fact and an Inventory Fact can independently point to the 20240101 Date Key, ensuring enterprise-wide reporting consistency.

Role-Playing Dimensions

Sometimes, a single physical dimension table is joined to a fact table multiple times, with each join representing a different logical "role."

Example (Dates in an Order Fact Table):

Instead of creating three separate date tables for ordering, shipping, and delivering, you use SQL aliases to join the exact same Date dimension three times.

Order ID	Order Date Key	Ship Date Key	Delivery Date Key
9001	20240101	20240103	20240105
9002	20240102	20240102	20240106

Each foreign key points to a different row in the shared Conformed Date Dimension.

Degenerate Dimensions

A degenerate dimension is a dimension key that lives directly in the fact table but does not have a corresponding dimension table. This happens when the key itself is the only piece of information, with no additional descriptive attributes.

Example (Invoice Numbers):

An invoice number is highly useful for grouping line items back into a single transaction, but all the actual descriptive attributes (customer, date, store) have already been split into separate dimension tables.

Date Key	Product Key	Store Key	Invoice Number (DD)	Sales Amount
20240101	102	55	INV-9001-A	$1,000
20240101	103	55	INV-9001-A	$500

The Invoice Number is stored as text in the fact table, acting as a dimension without a dedicated table.

Junk Dimensions

When a business process generates several low-cardinality flags and indicators (e.g., "Payment Method", "Order Status", "Is Expedited"), putting them directly in the fact table creates text clutter. However, creating a separate dimension table for each individual flag results in too many foreign keys.

A junk dimension solves this by grouping all these miscellaneous flags into a single dimension table. The fact table then only needs one foreign key pointing to the combined row.

Example (Order Flags Junk Dimension):

Junk Key	Order Status	Payment Method	Is Expedited
1	Pending	Credit Card	Yes
2	Pending	Credit Card	No
3	Completed	PayPal	Yes

The fact table stores Junk Key = 1 instead of three separate text flags.

Slowly Changing Dimensions (SCD)

Dimensions are not always static; their descriptive attributes can change over time (e.g., a customer moves to a new address, or a product changes its category). Handling these changes is critical for accurate historical reporting.

There are several standard techniques to manage this, known as Slowly Changing Dimensions.

Type 0: Retain Original

The dimension attribute never changes. Once it is written, it remains static forever.

• Example: A customer's "Original Sign-Up Date."

Type 1: Overwrite

The old attribute value is simply overwritten with the new value. No historical record is kept.

• Use Case: Fixing data entry errors (e.g., correcting the spelling of a customer's name).
• Impact: All historical reporting will now reflect the new value as if it had always been that way, permanently altering past reports.

Type 2: Add New Row (Historical Tracking)

A new row is added to the dimension table to capture the new attribute values, while the old row is preserved. This is the most common technique for accurate historical reporting.

• Mechanism: Requires adding an effective_date, an expiration_date, and an is_current flag to the dimension table.
• Example: If a customer moves from NY to CA, a new row is created for the CA address. Old fact records continue to point to the NY surrogate key, while new fact records point to the new CA surrogate key.

Type 3: Add New Column (Limited History)

Instead of adding a new row, a new column is added to the existing row to track the "previous" value, while the primary column is updated with the "current" value.

• Use Case: When you only need to track the immediate previous state (e.g., current_sales_territory vs. previous_sales_territory).
• Impact: Only one level of history is maintained.

Type 4: History Table

The current data is kept in the main dimension table, while all historical changes are moved to a separate "history" or "audit" table.

• Use Case: Useful when changes are very frequent and you want to keep the primary dimension table small and fast for current-state queries.

Type 5: Mini-Dimension (Type 4 + Type 1)

This is a hybrid approach. The fact table joins to a primary dimension (Type 1) for the current profile, while also joining to a separate historical "mini-dimension" (Type 4) that tracks rapidly changing demographic attributes over time.

• Use Case: When dealing with very large dimensions (e.g., 50 million customers) where tracking history with Type 2 would cause explosive, unmanageable table growth.

Type 6: Hybrid Configuration (1 + 2 + 3)

Type 6 intelligently combines the techniques of Types 1, 2, and 3 (and mathematically, 1 + 2 + 3 = 6).

• Mechanism: A new row is added for historical tracking (Type 2). However, every row for that entity also contains a "current attribute" column. When a change happens, this "current" column is overwritten (Type 1) across all historical rows for that entity. It may also keep a "previous" attribute column (Type 3).
• Impact: It allows users to query historical facts using either the historical attribute value (what was true at the time) or the current attribute value (what is true today), all without complex SQL logic.

Type 7: Dual Dimension Keys

In this advanced hybrid, the fact table contains two foreign keys for the exact same dimension entity.

• Mechanism: One key is a durable business key that always points to the "current" Type 1 dimension profile. The second key is a surrogate key that points to the "historical" Type 2 dimension row that was active at the time the fact event occurred.
• Impact: Similar to Type 6, this allows seamless reporting on either the "as-was" historical state or the "as-is" current state, but it resolves the complexity by leveraging two foreign keys in the fact table rather than constantly overwriting dimension rows.

Overlapping Dimension Types

Dimension types are not necessarily mutually exclusive. A dimension can have multiple characteristics at the same time because each classification describes a different aspect of the dimension.

Combination	Can they coexist?	Example / Explanation
SCD + Conformed	Yes	A Customer dimension can be a Type 2 SCD while being shared across Sales, Support, and Marketing fact tables.
SCD + Role-Playing	Yes	An Employee dimension can serve multiple roles, such as Sales_Rep, Support_Agent, or Manager, while tracking changes to attributes like department or title.
SCD + Junk	Usually no	A junk dimension typically contains combinations of low-cardinality flags and indicators. These are generally static and don't require historical tracking.
SCD + Degenerate	No	A degenerate dimension is an identifier stored directly in the fact table, without a separate dimension table or descriptive attributes. Therefore, there is no dimension table on which to apply SCD techniques.

Key Takeaway

These classifications describe different characteristics of dimensions:

• SCD → how changes are handled over time
• Conformed → how a dimension is shared across business processes
• Role-Playing → the different roles a dimension can serve
• Junk → how miscellaneous low-cardinality attributes are grouped
• Degenerate → a dimension identifier stored directly in the fact table

Therefore, combinations such as SCD + Conformed and SCD + Role-Playing are perfectly valid.