3.4 Dimensional Modeling

Dimensional Modeling Concepts

Dimensional Modeling is a database design discipline pioneered by Ralph Kimball that is specifically optimized for data warehousing, analytical querying, and Business Intelligence (BI) reporting. It stands in contrast to the normalized Entity-Relationship Model (ER Model) commonly used in Online Transaction Processing (OLTP) systems. While 3rd Normal Form (3NF) relational models focus on minimizing data redundancy and maintaining transactional integrity, dimensional models focus on query performance, simplicity, and intuitive representations of business processes. The primary objective is to present data in a structure that matches how business users think and to execute complex analytical queries with maximum speed.

Star Schema vs. Snowflake Schema

In dimensional design, database structures are organized into two primary schema types: the Star Schema and the Snowflake Schema.

• Star Schema: This is the simplest and most common dimensional design. It consists of a centralized Fact Table surrounded by multiple Dimension Tables. The diagram resembles a star shape, where the fact table forms the hub and the dimensions form the radial points. In a star schema, dimension tables are completely denormalized, meaning that hierarchies (such as Customer -> City -> State -> Country) are flattened into a single table. This flat structure eliminates the need for complex multi-table joins, yielding high query performance and making the schema easy for BI tools to query.
• Snowflake Schema: This is a variation of the star schema where one or more dimension tables are normalized, splitting hierarchical attributes into separate, related tables. For example, a product dimension might link to a separate subcategory table, which then links to a category table. Although the snowflake schema reduces data redundancy and saves disk storage space, it introduces multiple join levels (multi-hop joins). This increases query complexity, degrades query performance, and can confuse business users trying to build self-service reports.

Anatomy of Fact Tables

A Fact Table is a central table in a dimensional schema that stores the quantitative measurements, metrics, or facts resulting from a business process event. Fact tables typically consist of numeric measures and a set of foreign keys that link to the surrounding dimension tables.

The Grain (Granularity)

Declaring the Grain is the single most critical step in designing a fact table. The grain defines exactly what a single row in the fact table represents (e.g., 'individual line item on a retail sales invoice', 'daily balance snapshot of an account', or 'a single medical procedure check-in'). The grain must be established before identifying dimensions or facts. Mixing grains within a single fact table (such as storing individual daily transactions alongside monthly aggregates) is a severe architectural error that corrupts query results and leads to double-counting.

Measure Additivity Rules

Numeric facts inside a fact table are classified based on their behavior when aggregated across dimensions:

• Additive Facts: These facts can be meaningfully summed across all dimensions in the schema. For example, Sales_Amount or Quantity_Sold can be summed over time, customer, store, or product. These are the most common and useful facts.
• Semi-Additive Facts: These facts can be summed across some dimensions but not others. A classic example is Account_Balance or Inventory_Quantity. You can sum account balances across different customers or branches at a point in time to get a total balance, but you cannot sum balances across the time dimension (adding Monday's balance to Tuesday's balance yields a meaningless number). To aggregate semi-additive facts across time, you must use averages or end-of-period values.
• Non-Additive Facts: These facts cannot be summed across any dimension, usually because they represent ratios, averages, or unit prices (e.g., Profit_Margin_Percent or Unit_Price). To aggregate non-additive facts, you must sum the underlying additive facts first (e.g., sum of profits divided by sum of sales) and then perform the mathematical calculation at the aggregated level.

Special Fact Table Types

1. Transaction Fact Table: Records a discrete event at a point in time (e.g., a customer purchasing a product). Rows are only appended when an event occurs.
2. Periodic Snapshot Fact Table: Captures status at regular, pre-defined intervals (e.g., monthly bank statements or weekly inventory counts). A row is written for every active entity at the end of each period, regardless of whether a transaction occurred.
3. Accumulating Snapshot Fact Table: Records the progress of a process that has a clear beginning and end with multiple defined milestones (e.g., order fulfillment from placement, to shipment, to delivery). A single row is created when the process starts and is updated repeatedly as each milestone is completed.
4. Fact-Less Fact Table: A fact table that does not contain numeric measurements or facts. It merely records an occurrence or relationship between dimensions. For example, a student attendance table contains foreign keys to Student, Class, and Date. Although it has no numeric facts, the count of rows represents the attendance volume.

Anatomy of Dimension Tables

Dimension Tables contain descriptive, textual context (attributes) about business entities. Attributes represent the 'who, what, where, when, why, and how' of a business process and are used for filtering, grouping, and labeling in analytical reports.

Surrogate Keys vs. Business Keys

• Business Keys (Natural Keys): These are the primary identifiers used in operational systems (e.g., a Customer ID or Product SKU). They often contain alphanumeric characters and are subject to change if the operational system is upgraded or replaced.
• Surrogate Keys: A surrogate key is a system-generated, sequential integer assigned as the primary key of a dimension table in a data warehouse. It has no business meaning. Using surrogate keys is a fundamental best practice in dimensional modeling because:
  1. System Independence: It insulates the data warehouse from operational source system changes (e.g., if a system migration changes the format of Customer IDs).
  2. Performance: Joins on single integer keys are significantly faster than joins on alphanumeric strings.
  3. History Tracking: It enables the data warehouse to maintain multiple historical versions of a record for the same business key (as required by Slowly Changing Dimensions).

Conformed Dimensions and Bus Architecture

A Conformed Dimension is a dimension table that has exactly the same structure, keys, and values across multiple fact tables in an enterprise. The Date dimension is the most common conformed dimension. Under Ralph Kimball's Data Warehouse Bus Architecture, conformed dimensions are the integration glue that links disparate data marts. Without conformed dimensions (e.g., if the Sales department and the Shipping department use different, non-conformed customer lists), it is impossible to perform a Drill-Across query. A drill-across query combines metrics from two distinct fact tables (such as comparing sales amounts against shipping volumes) in a single report by joining them on their shared conformed dimensions.

Slowly Changing Dimensions (SCDs)

Data warehouses must track changes to dimension attributes over time to preserve historical context for reports. Operational databases typically overwrite old data, but data warehouses use Slowly Changing Dimension (SCD) strategies to manage changes:

• SCD Type 1 (Overwrite): The new value overwrites the old value in the row. No historical data is preserved. This is used to correct errors (e.g., fixing a typo in a street name or phone number) or when history is irrelevant.
• SCD Type 2 (Add Row): This is the industry-standard approach for preserving history. When an attribute changes, a new row is inserted into the dimension table, generating a new surrogate key. The old row remains unchanged. System columns like Effective_Start_Date, Effective_End_Date, and Active_Indicator are added to manage which record is current. All historical sales transactions point to the surrogate key that was active at the time of the sale, maintaining historical reporting accuracy (e.g., associating historical sales with the customer's previous state prior to their relocation).
• SCD Type 3 (Add Column): A column is added to the existing row to store the previous value. This allows comparing the current value with the immediate previous value (e.g., Current_Region and Previous_Region), but it does not support tracking multiple historical changes or querying historical states beyond the immediate past.

CDMP Exam Tips and Pitfalls

• Kimball's Four-Step Process: The CDMP exam frequently tests the order of Kimball's dimensional design process. The steps must be followed in this exact sequence:
  1. Select the business process (e.g., retail sales).
  2. Declare the grain (e.g., individual line items on a receipt).
  3. Identify the dimensions (e.g., date, product, store, customer).
  4. Identify the facts (e.g., sales quantity, unit cost, sales amount).
• Surrogate Keys and Type 2: Understand that surrogate keys are mandatory for SCD Type 2. Because a single business key (e.g., customer ID 1001) will have multiple rows in the dimension table representing different historical addresses, the business key can no longer serve as the primary key. A unique surrogate key is required for each row.
• Aggregation Pitfalls: Remember that semi-additive facts (like inventory balances) cannot be summed across the time dimension. If asked how to aggregate inventory over time, the correct answer involves averaging the balances or taking the value at the end of the period, not simple summation.