Conformed & Shrunken Dimensions
Conformed Dimension
Conformed dimensions are the "glue" that holds the enterprise data warehouse together. They are dimensions designed to have consistent meaning, structure and content regardless of which fact tables use or reference them.
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 |
This dim_date can be shared by multiple unrelated fact tables. Any fact table can reference the 20240101 Date Key to represent January 1, 2024, ensuring enterprise-wide reporting consistency.
Example (A Conformed Time-of-Day Dimension):
To prevent the date dimension from becoming too large, the time of day is usually stored in its own separate conformed dimension. A time dimension typically contains one row for every minute or second of a 24-hour day, with attributes like hour, shift, peak/off-peak and business-hours flag.
| time_key | time_of_day | hour | minute | am_pm | shift |
|---|---|---|---|---|---|
| 083000 | 08:30:00 | 08 | 30 | AM | Morning |
| 144500 | 14:45:00 | 14 | 45 | PM | Day |
Just like the date dimension, a single dim_time table can be shared across sales, support, and system log fact tables to consistently analyze activity by hour, minute, or business shift.
Shrunken Dimension
A shrunken dimension (or shrunken rollup dimension) is a subset of a conformed dimension. It contains either a subset of the attributes (columns) or a subset of the rows (for a higher grain) from the base dimension.
Shrunken dimensions are used when a fact table captures data at a higher level of aggregation than the atomic base dimension.
Example (A Shrunken Date Dimension):
If a forecasting fact table is built at the monthly grain, it cannot join to a standard daily dim_date table. Instead, it joins to a shrunken dim_month dimension, which drops the day-level attributes and retains only one row per month.
Base Dimension (dim_date):
| date_key | full_date | day_of_week | month_name | quarter | year |
|---|---|---|---|---|---|
| 20240101 | 2024-01-01 | Monday | January | Q1 | 2024 |
| 20240102 | 2024-01-02 | Tuesday | January | Q1 | 2024 |
Shrunken Dimension (dim_month):
| month_key | month_name | quarter | year |
|---|---|---|---|
| 202401 | January | Q1 | 2024 |
| 202402 | February | Q1 | 2024 |
The dim_month dimension contains a subset of columns (attributes) and a subset of rows (higher grain) compared to the base dim_date.