fbpx

Star Schema Foundations: Dimension Tables

This page serves as a reference for my course “Star Schema Foundations” Module on Identifying Dimensions

Dimension Table Definition

Dimensions provide the “who, what, where, when, why, and how” context surrounding a business process event.

Dimension Table Types

Denormalized Dimensions

A flattened table containing all attributes for a specific entity (dimension). By definition, all dimension tables should be denormalized for easy access. This is where you save your users a lot of time by simplifying the joins.

Role Playing Dimensions

A role playing dimension is one that is joined to multiple times from the fact table. Typically these are date dimensions that can be joined to by every date in the fact table. Examples would be having both “Order Date” and “Ship Date” in your fact table join to the same dimension. .

Outrigger Dimension

An outrigger dimension is similar to a Role Playing Dimension however rather than being joined to by a fact table, it is joined to by other dimension tables. This is similar to how snowflaking works, however, it is only 1 extra join and is also typically used for date dimensions.

Junk Dimension

A junk dimension typically stores simple indicators or lookup values that have few attributes other than a name.

Dimension Table Architecture

Surrogate Keys

A dimension tables primary key that does not depend on any source system but is created by the data warehouse itself.

Natural Keys

The primary key of the dimension row from the source system which it came. This ID will persist with the row even if there are new sibling records such is the case with slowly changing dimensions.

Flags and Indicators

Often dimensions contain attributes that represent true or false values (boolean). These values can also be indicators for analysts on how to use the dimension itself.