~12 min read

Modeling Fundamentals

The Foundation of Every Great Report

Star schema, filter flow, and building models that work.

Model Architecture Series
Scroll to explore

Why Model Data?

Three reasons a well-designed model changes everything

Deduplication

Store each value once. "Minerals" appears in one dimension row, not repeated across thousands of transactions.

Performance

Smaller model size, faster refresh, better compression. The engine thrives on narrow, typed columns with low cardinality.

Filter Propagation

Relationships create automatic filter paths. Select a category in a slicer, and every measure recalculates instantly.

Dimensions & Facts

The two types of tables that make up every model

Imagine one giant flat table with every column crammed together. It works, but look at all the duplication:

CargoName Class Origin Cycle Quarter MerchantName Sector Units Credits
Stardust OreMineralsTitan2224Q1Apex CorpCore150₵450,000
Stardust OreMineralsTitan2225Q1Apex CorpCore180₵540,000
Stardust OreMineralsTitan2226Q2Nova LtdRim200₵600,000
Plasma CrystalsMineralsAndara2224Q3Apex CorpCore200₵600,000
Nav BeaconTechPolar2225Q2Zenith CoMid600₵30,000
Void SilkTextilesVerdant2225Q4Nova LtdRim350₵52,500
Stardust OreMineralsTitan2225Q2Zenith CoMid160₵480,000
Nebula ThreadTextilesTitan2226Q3Apex CorpCore300₵45,000
Repeated values that could be stored once

The solution: Separate your data into dimension tables (the "who, what, when, where") and fact tables (the numeric events). Dimensions hold unique descriptive rows. Facts hold the transactions, linked by keys. This eliminates redundancy and creates the filter paths your measures depend on.

This is a star schema. Dimensions radiate outward like spokes from the fact table hub, connected by one-to-many relationships (1 dimension row to * many fact rows):

Dimension

Cargo

  • CargoKey (PK)
  • CargoName
  • Class
  • Value
Dimension

Stardate

  • StardateKey (PK)
  • Cycle
  • Quarter
  • Month
Dimension

Merchants

  • MerchantKey (PK)
  • MerchantName
  • Sector
  • Segment
1 1 1 * * *
Fact Table

Trades

  • TradeKey (PK)
  • CargoKey (FK)
  • StardateKey (FK)
  • MerchantKey (FK)
  • Units
  • TradeTotal

Filter Flow

See how filters propagate through the star schema in real time

This is what star schema enables. When someone selects "Minerals" in a slicer, the filter hits the Cargo dimension first, then flows down the relationship into the Trades fact table. Only matching rows survive. Try it yourself:

Cargo Class

Cycle

Cargo (Dimension)
Stardate (Dimension)
Trades (Fact)
Rows in Context
20 / 20
filtered rows
Total Units
4,500
SUM( Trades[Units] )
Total Revenue
$4,972,000
SUM( Trades[Credits] )

Star vs Snowflake

Two schemas, one clear winner for Power BI

Star Schema

Cargo
Stardate
Merchants
↓   ↓   ↓
Trades
Recommended for Power BI
VS

Snowflake Schema

CargoClass
↓
Cargo
CycleYear
↓
Stardate
Merchants
↓   ↓   ↓
Trades
Avoid in Power BI

Query Performance

Single join per dimension. Engine optimized for this pattern.
Multiple chained joins. More work for the engine.

DirectQuery Support

Fewer joins means faster live queries to the source.
Each extra join compounds latency in DirectQuery mode.

DAX Simplicity

Measures stay short. Every dimension attribute is one RELATED() away.
Longer formulas, extra variables, and helper tables just to reference a normalized column.

Self-Service Usability

Flat dimensions are intuitive. Users browse and filter naturally.
Normalized tables confuse business users looking for fields.

Analytic Queries

Every visual in Power BI generates a query with three ingredients

Filter

Narrow the data. Slicers, page filters, report filters, and row-level security all restrict which rows participate.

Group

Split the remaining data into groups. Fields on rows, columns, or axes define the granularity of the result.

Summarize

Aggregate each group. SUM, AVERAGE, COUNT, or any measure expression evaluates once per group.

Where Do These Filters Come From?

Click any element in the report below, or any source chip, to see how they connect:

Filter Group Summarize
Power BI Report
Cycle ▾
2224
2225
2226
Origin ▾
All ▾
Class ▾
All ▾
Cargo
Stardust Ore
Plasma Crystals
Void Silk
Table Visual
CargoOriginClassUnitsRevenue
Stardust OreTitanMinerals340$1.0M
Plasma CrystalsAndaraMinerals220$661K
Nav BeaconPolarTech600$30.8K
Cryo FlaskCobaltTech400$20.4K
Void SilkVerdantTextiles350$52.8K
Nebula ThreadTitanTextiles250$37.8K
Revenue by Cargo
+62% $1.0M
Stardust
-41% $661K
Plasma
+78% $52.8K
Void S.
+33% $37.8K
Nebula
-67% $30.8K
Nav B.
+80% $20.4K
Cryo
Total Revenue
$1.8M 12.4% vs Prior Cycle
MAMJJASONDJF
Avg per Cargo
$303.8K 8.2% vs Prior
MAMJJASONDJF
Cargo Count
6 3.4% vs Prior
MAMJJASONDJF
Filters
Filters on this visual
Cargo is not blank
Filters on this page
Class = Minerals
Filters on all pages
Cycle >= 2224

Click to Explore

Click any element in the report above, or any source chip, to see how they connect.

The takeaway: Every visual you see in Power BI is the result of an analytic query that filters, groups, and summarizes your model. A clean star schema makes these queries fast, predictable, and easy to extend with DAX.

Intuitive Fields List

A model should be business-friendly, not just technically correct

Fields (Before)
dim_cargo
cargo_key
cargo_name_text
cls_cd
fct_trades_data
cgo_key_fk
trade_amount_sum
unit_cnt
Confusing for users
VS
Fields (After)
Metrics
Base Measures
Total Revenue
Total Units
Cargo
Cargo Name
Class
Origin
Self-service ready

Friendly Names

Remove prefixes (dim_, fct_), abbreviations, and underscores. Use plain business language: "Cargo Name" not "cargo_name_text".

Display Folders

Group measures into folders (e.g., "Measures", "KPIs", "Time Intelligence") so users can find what they need without scrolling.

Hide Technical Columns

Keys, foreign keys, and sort columns should be hidden from the report view. Users should never see "CargoKey" or "SortOrder".

Descriptions

Add descriptions to measures and key columns. They appear as tooltips in the fields list, helping users understand what each field means.

What's Next

You've got the foundation. Now let's build on it.

Continue Learning

Understanding DAX: The Language Behind Power BI

Now that you understand the model, learn how DAX evaluates measures against it. Syntax, functions, and the critical concept of filter context.

Part 1 of 3

Need help designing your star schema or optimizing your semantic model?

We help teams build clean, performant data models from the ground up. Star schemas, naming conventions, measure architecture, and everything covered in this guide.

Schedule a Call Our Offerings

Discussion

Loading comments...
1 / 8
**On screen:** Title slide with "Modeling Fundamentals" - This is a quick refresher on the foundations. Everything that follows in the workshop builds on these concepts. - If you already work with star schemas daily, think of this as a vocabulary alignment. *Transition: "Let's start with why we bother modeling at all."*
**On screen:** Deduplication card - One row per cargo item instead of thousands of repeated strings. Less storage, better compression.
**On screen:** Performance card - VertiPaq thrives on narrow, low-cardinality columns. Separated tables compress dramatically better.
**On screen:** Filter Propagation card - The real payoff. Relationships create the automatic filter paths that make your measures work without extra code. *Transition: "So what do these separated tables actually look like?"*
**On screen:** Flat table with duplicate values highlighted in red - Point out the repeated "Stardust Ore / Minerals / Titan" rows. Every transaction carries the full cargo description. - Ask: "How many unique cargo items are there?" (6). "How many times does 'Minerals' appear?" (4 times in just 8 rows). *Transition: "Now watch what happens when we separate this."*
**On screen:** The Solution callout - Separate your data into dimension tables (who, what, when, where) and fact tables (numeric events). Dimensions hold unique descriptive rows. Facts hold transactions, linked by keys.
**On screen:** Star schema diagram with animated arrows - Hub and spokes. Fact table in the center, dimensions radiating out. - One-to-many relationships: one Cargo row matches many Trades rows. - The dashed arrows show the direction filters flow: from dimension down to fact. *Transition: "Let's see that filter flow in action."*
**On screen:** Interactive filter flow demo with clickable slicers - Click "Minerals" in Cargo Class. Watch how the Cargo dimension grays out non-mineral rows, then the Trades fact table follows suit. - Click "2225" in Cycle. Now both filters apply simultaneously. - Point out the metric cards updating in real time. The formula never changes; the context does. *Transition: "This pattern is called a star schema. There's another option called snowflake. Let's compare."*
**On screen:** Side-by-side star vs snowflake diagrams - Star: flat dimensions, one join per dimension. Simple and fast. - Snowflake: normalized dimensions with sub-tables. More joins, more complexity. - For Power BI specifically, star schema wins on every axis we're about to cover. *Transition: "Let me show you the four reasons why."*
**On screen:** Query Performance card - Single join per dimension means the engine scans fewer paths. Star schema is what VertiPaq was built for.
**On screen:** DirectQuery Support card - Every extra join in a snowflake becomes a round-trip to the source. Star keeps it minimal.
**On screen:** DAX Simplicity card - RELATED() crosses one relationship in a star. In a snowflake, you need workarounds or bridging tables.
**On screen:** Self-Service Usability card - Business users see flat, intuitive dimension tables. No confusion about which normalized sub-table has their field. *Transition: "Now let's see what happens on the report side."*
**On screen:** Section header: "Analytic Queries" - Every visual in Power BI generates a DAX query. Three ingredients, every time.
**On screen:** Filter card - Slicers, page filters, report filters, RLS. All of these narrow which rows participate before anything else happens.
**On screen:** Group card - Fields on rows, columns, or axes split data into buckets. Each bucket is a group.
**On screen:** Summarize card - Measures aggregate each group: SUM, AVERAGE, COUNT, or any DAX expression evaluates once per group.
**On screen:** Interactive PBI wireframe - Click the slicer to see which chip lights up. Click the table to see grouping highlighted. - The filter pane connects to the Filter chip. KPI cards show summarization. - Reinforce: every element you see is a source of automatic filtering, grouping, or summarization.
**On screen:** The Takeaway callout - Every visual is an analytic query: filter, group, summarize. A clean star schema makes these queries fast, predictable, and easy to extend with DAX. *Transition: "Last piece: making your model user-friendly."*
**On screen:** Before/after fields pane comparison - Left: technical names, prefixes, abbreviations. Users won't know what "cls_cd" means. - Right: clean names, display folders, measures grouped together. This is self-service ready.
**On screen:** Friendly Names card - Remove prefixes, abbreviations, underscores. "Cargo Name" not "cargo_name_text". Plain business language.
**On screen:** Display Folders card - Group measures into logical folders. Users can find what they need without scrolling through a flat list.
**On screen:** Hide Technical Columns card - Keys, foreign keys, sort columns. Users should never see "CargoKey". Hide anything they don't need.
**On screen:** Descriptions card - Add descriptions to measures and key columns. They appear as tooltips in the fields list.
**On screen:** CTA card linking to DAX Foundations - Wrap up: you now have the modeling vocabulary. Star schema, dimensions, facts, filter propagation, analytic queries. - Everything we do next (calculation groups, field parameters, RLS) builds on this foundation.