Special Dimension Scenarios

Transform and Analyze Data with Microsoft Fabric

Luis Silva

Solution Architect - Data & AI

Date dimension

Date field is generally present in fact tables
Date dimension is the most commonly used dimension for analysis
Supports filtering and aggregating by date

Illustration showing a star schema with a date dimension and a sample usage producing a chart showing sales per month

Date dimension

Sample date dimension table with columns DateKey, Date, Year, Month, Day, Fiscal Quarter, Fiscal Month and Is Holiday

Natural Key: date data type
Surrogate key: integer data type (ISO 8601 date format YYYYMMDD).
- Space efficient
- Automatically Sorted
- Human readable
Should include fields for Year, Month and Day.
Other attributes useful for analysis

Time dimension

Some facts might require data to be at the time-of-day level.
Dedicated time dimension table.

Illustration showing a star schema with a time dimension and a sample usage producing a chart showing sales per time of day range

Time dimension

Natural key: time data type
Surrogate key: integer data type, using HHMM or HHMMSS format
Grain of minutes: 60 x 24 = 1,440 rows
Grain of seconds: 60 x 60 x 24 = 86,400 rows

Conformed dimensions

Conformed dimensions relate to many fact tables
Common example: Date dimension
Help to ensure consistency across models supporting different areas.
Example, product and date dimension shared by sales and marketing star schemas.

Diagram of two star schemas, sales and marketing, sharing the conformed dimensions of product and date

Role-Playing dimensions

Dimension that is referenced multiple times in a fact table
Instead of duplicating the dimension table, the same dimension plays different roles
Example: Order Date, Shipping Date and Delivery Date

Diagram of a star schema with a fact table that has three date columns: order date, shipping date and delivery date, all related to the same date dimension

Multivalued dimensions

Common scenario: One-to-Many relationships between facts and dimensions

Star schema showing one-to-many relationships between the fact and the dimension tables

Multivalued dimensions

Special scenario: Many-to-Many relationships
Bridge tables (factless tables) store pairs of related dimension keys
Example: Bridge table Salesperson Region links a salesperson with multiple sales region

Star schema showing one-to-many relationships between the fact and the dimension tables. In addition, a bridge table links the Salesperson and the Sales Region tables

Let's practice!

Transform and Analyze Data with Microsoft Fabric