Dimension Tables Structure

Dimension tables act as the descriptive soul of the data warehouse. While fact tables capture the magnitude of business events through numerical values, dimension tables provide the context necessary to interpret those numbers. Without strong dimensions, a fact table is merely a collection of meaningless digits. Here is an examination of the structural requirements for building effective dimension tables that support intuitive filtering, grouping, and labeling in analytical queries.

The Role of Context in Analytics

A well-designed dimension table answers the "who, what, where, when, why, and how" of a business event. If a fact table records that a sale occurred, the dimension tables describe the customer who bought the item, the product that was sold, the store location, and the promotion that drove the sale.

The primary goal when structuring these tables is to provide a user-friendly interface for the data analyst. When an analyst writes a query or uses a BI tool, they interact almost exclusively with dimension attributes to constrain and group their data. Therefore, dimension tables contain text-heavy, descriptive attributes rather than abbreviated codes or IDs.

For example, an operational database might store a status as "S" or "P". In a dimension table, this attribute should be expanded to "Shipped" or "Pending". This verbose approach reduces the cognitive load on the analyst, removing the need to look up lookup codes during query construction.

Anatomy of a Dimension Table

Dimension tables are characteristically "wide and shallow." They contain relatively few rows compared to fact tables, thousands or millions versus billions, but they possess many columns. Each column represents a specific attribute of the entity being modeled.

A standard dimension table consists of three main component types:

1. Primary Key (Surrogate Key): A comprehensive data warehouse usually assigns a system-generated integer to uniquely identify each dimension row. This isolates the data warehouse from changes in the source system's key generation logic.
2. Natural Keys: The original identifier from the operational source system (e.g., customer_id, product_sku). These remain in the table to facilitate ETL updates and cross-referencing.
3. Descriptive Attributes: The text fields used for filtering and grouping.

Consider the following structure of a Product Dimension. It consolidates data that might be scattered across five or six normalized tables in an operational system.

The structure of a denormalized dimension table consolidates attributes into a single view, simplifying access paths for queries.

Denormalization and Hierarchy Flattening

In operational databases (OLTP), design focuses on eliminating redundancy through normalization. If a product belongs to a category, and that category belongs to a department, a normalized design creates three separate tables. To get the department for a product, you must perform two joins.

In dimensional modeling, we prioritize query performance and ease of use over storage efficiency. We intentionally introduce redundancy by "flattening" these hierarchies into a single table. This process is known as denormalization.

When you flatten a hierarchy, the relationship between the grain of the dimension (e.g., Product) and its parent levels (Category, Department) becomes explicit in the row structure.

Normalized (OLTP) Approach: $Product \xrightarrow{join} Category \xrightarrow{join} Department$

Dimensional (OLAP) Approach: $ProductDim(Product, Category, Department)$

This flattening allows analysts to group by any level of the hierarchy without managing complex joins. For instance, calculating sales by Department becomes a simple GROUP BY department_name on the Product Dimension, rather than traversing multiple tables.

Attributes and Cardinality

When selecting attributes for a dimension, it is helpful to consider their cardinality, the number of unique values in a column. High-cardinality columns are unique or nearly unique identifiers (like phone numbers), while low-cardinality columns represent groupings (like generic colors or states).

Analytical queries often perform best when filtering on low-cardinality columns. However, dimension tables must handle both.

The following chart visualizes the typical distribution of attribute cardinality within a dimension table. While the primary key is unique (100% cardinality), most descriptive attributes share values across many rows, creating the groups used for aggregation.

Cardinality distribution varies significantly across attributes. Note the logarithmic scale; grouping attributes (State, Gender) have far fewer unique values than identifiers.

Handling Nulls and Unknown Members

A common challenge in dimension table structure is handling missing data. In a transaction feed, you may encounter a record where the customer_id is missing or invalid.

In a relational database, one might leave the foreign key as NULL. However, in a dimensional model, referential integrity between the fact and dimension table is strict. If a fact row has a NULL foreign key, it may be excluded from queries that perform an inner join, causing the aggregate totals to be incorrect.

To resolve this, dimension tables should include specific rows to handle "Unknown", "Not Applicable", or "Late Arriving" data. These rows usually carry a negative integer Surrogate Key (e.g., -1, -2) to distinguish them from valid system keys.

Implementation Strategy:

1. ID -1 (Unknown): Used when the source system sends a NULL or invalid identifier.
2. ID -2 (Not Applicable): Used when the context does not apply to the specific transaction (e.g., a "Promotion Dimension" for a sale that had no promotion).

This practice ensures that SUM(Sales) returns the same global total regardless of how the user slices the data.

The Flag and Indicator Pattern

Dimension tables often contain boolean flags (True/False). To make these more usable for reporting, it is standard practice to convert cryptic boolean codes into readable text strings.

Instead of a column is_corporate_client containing 0 or 1, structure the dimension with a column client_segment containing values "Corporate" and "Retail". This avoids the need for analysts to use CASE statements in their reporting layer to decode the meaning of the flag.

If multiple binary flags often appear together, consider combining them into a single "junk dimension" or a descriptive state column if they represent mutually exclusive states. This keeps the main dimension table clean and focused on the primary entity attributes.