Day 36. Power BI data models that stay fast

⏱ 4h · learn 70m · lab 140m · ship 15m · review 15m🏷 restaurants, hotelsBefore: Week 5

By the end of today you can

Connect Power BI to Azure SQL and choose Import, DirectQuery or composite
Build a star model with correct relationships, cardinality and filter direction
Use Power Query with query folding and parameters
Save the report as a PBIP project so it lives in git

1. Why this matters

Most slow or wrong Power BI reports are model problems, not visual problems. If Week 5's star schema arrives in Power BI intact, DAX stays simple and visuals are fast. Today you build that model on your restaurant and hotel marts.

2. Storage modes

Mode	How it works	Use when
Import	Data compressed into the model (VertiPaq), refreshed on a schedule	Default. Fastest visuals; most DAX freedom
DirectQuery	Every visual sends SQL to the source live	Data too big to import, or near-real-time needed; source must be fast
Composite / Dual	Mix: big fact in DirectQuery, dims in Import/Dual; aggregations table imported	Large facts with summary-level dashboards
Direct Lake	Reads Delta/Parquet in OneLake directly (Fabric only)	Fabric shops

For a medium company on Azure SQL with a few GB of marts, Import with scheduled refresh after the pipeline finishes is almost always right.

3. The model

Load marts only (mart schema with the report_reader login from Day 23). Never raw or staging.
Relationships: one-to-many, from dimension (one) to fact (many), single direction (dimension filters fact). Avoid bi-directional and many-to-many unless you know exactly why.
Mark dim_date as the date table. Turn off Auto date/time in options (it creates hidden tables per date column).
Hide fact foreign keys and technical columns. Put measures in a dedicated _Measures table.
Several dates on one fact (booking created, arrival)? One active relationship to dim_date, the other inactive and used with USERELATIONSHIP in DAX (role-playing).

Maths and principle

Principle: VertiPaq compresses each column with dictionary + run-length encoding, so model size depends on distinct values per column, not rows.

Equation: column size \(\approx N \cdot \lceil \log_2 D \rceil\) bits (dictionary indexes) + dictionary of \(D\) values, then shrunk further by run-length encoding on sorted data. A datetime with seconds has huge \(D\); split it into a date and a time (or drop the time).

Analogy: information entropy: a column with few distinct values carries few bits per row. Where it breaks: high-cardinality text (order IDs, GUIDs) barely compresses. Don't import it unless needed for a visual.

Try it: 50M rows, column A with 4 distinct values, column B with 1M distinct. Index bits per row? (A: 2 bits; B: 20 bits: 10× bigger before RLE.)

4. Power Query and query folding

Power Query (M) shapes data on the way in. Query folding means your steps are translated into SQL and run in Azure SQL; once a step can't fold (some custom columns, merges with non-foldable sources), every later step runs on the gateway or desktop, row by row. Right-click a step → View Native Query to check. Best practice: do transformations in dbt; keep Power Query to selecting tables and columns, and parameters.

Use parameters for server and database (SqlServer, SqlDatabase) so the same report points at dev or prod.

5. PBIP: Power BI in git

Save as Power BI Project (.pbip): the report and semantic model become folders of text files (TMDL for the model, JSON for the report), diffable and reviewable in pull requests. Commit the PBIP folder; keep .pbix exports for convenience only. (.pbix files are binary and may contain imported data. Keep synthetic data only.)

The 5-year lens

Model size and refresh time are the two numbers to know for every report you own. DAX Studio (free) with VertiPaq Analyzer shows size per column; remove or split the top three and models often halve. Tabular Editor 2 (free) edits measures and runs best-practice rules. Mentioning both in an interview signals you've worked on real models, not just built visuals.

Lab

Install Power BI Desktop (Windows, free). Connect to your Azure SQL mart (or the local SQL Server container) with the reader login, Import mode, using server/database parameters.
Load fct_order_lines, fct_restaurant_daily, dim_date, dim_restaurant, dim_menu_item, dim_channel, plus hotel fct_bookings and otb_by_stay_date.
Set relationships (1:*, single direction), mark the date table, disable auto date/time, hide keys, create _Measures.
Check query folding on each query. Add one non-folding step on purpose and see View Native Query grey out.
Run VertiPaq Analyzer in DAX Studio; screenshot the model size; remove one high-cardinality column and compare.
Save as PBIP into chain-dashboards/powerbi/. Commit and look at the diff after changing one measure.

Ship

New repo chain-dashboards. Commit feat(powerbi): star model on Azure SQL marts, PBIP format.