7.8 KiB
Definition
A Dimension Table contains descriptive information that provides context for the events and measurements stored in a Fact Table.
If a Fact Table tells us:
What happened?
Dimension Tables help answer:
Who? What? Where? When? How?
Examples include:
- Content
- Customer
- User
- Product
- Platform
- Device
- Geography
- Date
Simple Mental Model
Think:
Fact = What happened?
Dimension = Describe what happened.
If the Fact Table says:
A viewing event lasted 125 seconds.
Dimensions tell us:
Who: User 7821
What: Episode 1 of Show A
When: July 19, 2026
Where/How: Roku
Together, Facts and Dimensions turn an event into something the business can analyze.
How It Works
Imagine a Fact Table called:
fact_viewing
It contains:
| content_id | platform_id | date_id | watch_seconds |
|---|---|---|---|
| 501 | 3 | 20260719 | 125 |
The Fact Table knows:
content_id = 501
But 501 doesn't mean anything useful to a business user.
A Dimension Table called:
dim_content
could contain:
| content_id | title | series | genre | language |
|---|---|---|---|---|
| 501 | Episode 1 | Show A | Drama | French |
Now we know what Content 501 represents.
Instead of storing:
Episode 1 / Show A / Drama / French
inside every viewing event, the Fact Table stores the key:
501
and connects it to dim_content.
Example
Consider another Dimension Table:
dim_platform
| platform_id | platform | device_type |
|---|---|---|
| 1 | Web | Desktop |
| 2 | iOS | Mobile |
| 3 | Roku | OTT |
| 4 | Android TV | OTT |
The Fact Table contains:
platform_id = 3
The Dimension Table tells us:
3 = Roku
Now the business can ask:
How many hours were watched on Roku?
Or:
How does OTT viewing compare with mobile viewing?
The Fact Table provides the measurable event.
The Dimension Table provides the context needed to group and analyze those events.
The Date Dimension
One of the most common Dimension Tables is:
dim_date
It might contain:
| date_id | date | day | month | quarter | year | weekend |
|---|---|---|---|---|---|---|
| 20260719 | 2026-07-19 | Sunday | July | Q3 | 2026 | Yes |
You might wonder why an entire table is needed just for dates.
A Date Dimension makes it easier to analyze data by:
- Day
- Week
- Month
- Quarter
- Year
- Weekend vs weekday
Organizations can also add business-specific calendars:
- Fiscal year
- Fiscal quarter
- Broadcast season
- Programming season
This allows consistent time-based analysis across multiple Fact Table.
Natural Keys and Surrogate Keys
Dimension Tables commonly introduce the distinction between a Natural Key and a Surrogate Key.
A Natural Key comes from the business or source system.
Examples:
- Employee number
- Product SKU
- ISBN
- External content ID
A Surrogate Key is generated internally by the data platform.
For example:
content_key = 501
The number 501 may have no business meaning.
A Dimension Table could contain:
| content_key | jwplayer_id | title |
|---|---|---|
| 501 | abc123 | Show A |
Here:
content_key = 501
is the Surrogate Key.
jwplayer_id = abc123
is the source identifier and could potentially act as a Natural Key.
Surrogate Keys are useful because source systems can change.
For example, a company might use:
JW Player → New Video Platform
The analytical data model can maintain its own internal identifiers rather than making its entire structure dependent on identifiers controlled by an external vendor.
This concept becomes particularly important when studying Slowly Changing Dimensions.
How It Fits Into the Bigger Picture
A simplified architecture could look like:
Sources
↓
ETL vs ELT
↓
Medallion Architecture
↓
Bronze
↓
Silver
↓
Gold
↓
Fact Table + Dimension Tables
↓
Star Schema
↓
Semantic Layer
↓
Power BI
Dimension Tables make analytical data easier for humans and BI tools to understand.
Instead of analyzing meaningless IDs, users can analyze:
- Show title
- Genre
- Platform
- Device
- Country
- Month
This is one of the ways raw technical data becomes business-ready information.
My Company / Real-World Context
For a media company, useful Dimension Tables might include:
dim_content
Describes:
- Program
- Episode
- Series
- Genre
- Language
dim_platform
Describes:
- Web
- Mobile
- Roku
- Apple TV
- Android TV
dim_device
Describes:
- Desktop
- Mobile
- Tablet
- Connected TV
dim_date
Describes:
- Day
- Month
- Quarter
- Year
- Broadcast season
These dimensions could connect to a Fact Table such as:
fact_viewing
allowing Power BI users to analyze:
Watch hours by platform
Unique viewers by content
Viewing sessions by month
Engagement by device
The Fact Table provides the number.
The Dimension Table provides the "by what?"
CTO Perspective
Dimension Tables may look like a technical modeling detail, but they have important governance implications.
Imagine one system says:
French
another says:
FR
and another says:
fr_CA
If every Power BI report handles these independently, the organization can end up with inconsistent results.
A governed Dimension Table can establish a standardized representation.
The same applies to:
- Content categories
- Platforms
- Departments
- Regions
- Products
- Customer segments
Dimension Tables can therefore become an important part of Data Governance because they help establish shared business definitions and classifications.
A CTO should care less about personally designing dim_platform and more about ensuring the organization has consistent definitions across systems and reports.
Questions to Ask
- What business entity does this Dimension Table represent?
- What attributes describe it?
- What is its Natural Key?
- Does it use a Surrogate Key?
- Which source systems feed this dimension?
- How are conflicting source values standardized?
- Who owns the business definition?
- How are historical changes handled?
- Is this dimension shared across multiple Fact Table?
- Are Power BI reports using the same governed dimensions?
Meeting Scenario
Situation:
Two Power BI reports show different viewing totals by platform.
One report categorizes:
Roku → Connected TV
while another categorizes:
Roku → OTT
The teams are debating which report is correct.
Possible response:
"It sounds like the issue isn't necessarily with the viewing data itself, but with how we're classifying platforms. Before changing either report, can we confirm whether we have a governed platform dimension and agree on the business classification we want to use?"
You could then add:
"Once that definition is established centrally, both reports should consume the same classification rather than maintaining separate platform mappings."
This moves the discussion from:
Which analyst has the correct Power BI report?
to:
What is our organization's governed definition?
That's a Data Governance problem rather than simply a reporting problem.
Key Takeaways
- A Dimension Table provides descriptive context for a Fact Table.
- Facts answer what happened; dimensions answer who, what, where, when, and how.
- Dimensions allow data to be grouped and analyzed using business-friendly attributes.
- Natural Key usually originate from business or source systems.
- Surrogate Key are internally generated identifiers.
- Governed dimensions help create consistent definitions across reports and systems.
- Fact Tables and Dimension Tables commonly come together in a Star Schema.
Related Concepts
- Fact Table
- Grain
- Measure
- Natural Key
- Surrogate Key
- Slowly Changing Dimensions
- Star Schema
- Medallion Architecture
- Data Warehouse
- Data Governance
- Semantic Layer