Loading Academy Hub...
Master dimensional data modeling for enterprise reporting: Kimball methodology, Fact tables (additive, semi-additive, non-additive), Conformed Dimensions, Surrogate keys, Star Schema vs Snowflake Schema, and VertiPaq compression.
[!NOTE] Ralph Kimball's dimensional modeling remains the golden standard for enterprise analytical reporting. In Power BI, designing a clean Star Schema is the single most important factor determining report rendering speed and DAX formula simplicity.
| Architectural Dimension | Star Schema | Snowflake Schema | Power BI VertiPaq Impact |
|---|---|---|---|
| Structure | Fact table directly related to flat dimensions (1-hop relationships). | Dimensions normalized into sub-dimensions (multi-hop relationships). | Star Schema is up to 5x faster in Power BI. |
| Relationship Complexity | Simple, single-hop relationships. | Complex chained relationships. | Chained relationships increase query joins and memory usage. |
| DAX Measures | Clean, fast, straightforward DAX. | Requires complex traversals across multiple tables. | DAX engine optimizes single-hop filter propagation natively. |
| Storage Engine | VertiPaq columnar compression excels on de-normalized tables. | Normalization provides negligible storage benefits in columnar stores. | VertiPaq compresses repeated strings into integer dictionaries. |
2026)Fact -> Dimension): Power BI’s VertiPaq columnar engine compresses repetitive dimension strings using dictionary encoding and hash-scans tall narrow Fact tables containing pure integer surrogate keys and numeric measures (SalesAmount, Qty). Flat single OBT (One Big Table) imports explode dictionary RAM sizes and trigger ambiguous relationships.Single (One -> Many, Dimension filters Fact). Bi-directional relationships (Both) inject subtle ambiguity errors across multi-fact stars and cripple DAX storage engine caching; handle M:N or dimension slicing cleanly inside DAX using CROSSFILTER(...) strictly inside targeted measures.Mark as Date Table) and avoid floating-point or high-cardinality timestamp join keys (split Date + Time or use integer DateKey 20261113!).Review the production code, company interview Q&As, incident traces, and comprehension quiz inside this lesson.
-- Dim Date: Essential Conformed Dimension for Time Intelligence
CREATE TABLE dbo.dim_date (
date_key INT NOT NULL PRIMARY KEY, -- e.g. 20260301
full_date DATE NOT NULL,
year SMALLINT NOT NULL,
quarter TINYINT NOT NULL,
month TINYINT NOT NULL,
month_name VARCHAR(10) NOT NULL,
is_weekend BIT NOT NULL
);
-- Fact Sales: High-Volume Transactional Fact Table
CREATE TABLE dbo.fact_sales (
sales_key BIGINT IDENTITY(1,1) PRIMARY KEY,
date_key INT NOT NULL FOREIGN KEY REFERENCES dbo.dim_date(date_key),
customer_key INT NOT NULL,
product_key INT NOT NULL,
sales_amount DECIMAL(12, 2) NOT NULL,
quantity INT NOT NULL
);Power BI Performance Analyzer: Visual Display took 16,420 ms. DAX Query: 14,800 ms (Formula Engine: 82%, Storage Engine: 18%).