Úvod do optimalizace datového modelu
Co se naučíte
- Pochopení VertiPaq enginu a komprese dat (Value, Dictionary, RLE)
- Rozdíl Storage Engine vs Formula Engine
- Identifikace pomalých dotazů pomocí Performance Analyzer
a DAX StudioNa blogu Jiřího Neorala: Power BI - Performance Analyzer — jak používat Performance Analyzer krok za krokem
- Běžné chyby: bidirectional filtry, CALCULATE v iteratorech, fact-to-fact M:N bez bridge
- Praktické tipy pro optimalizaci cardinality a datových typů
VertiPaq Engine (xVelocity)
VertiPaq je in-memory columnar storage engine Power BI. Místo řádků (row store), ukládá data po sloupcích (column store), což umožňuje efektivní kompresi a dotazování.
Column Store vs Row Store
| Vlastnost | Column Store (VertiPaq) | Row Store (SQL Server) |
|---|---|---|
| Organizace | Data seskupena po sloupcích | Data seskupena po řádcích |
| Kompresi | Vysoká (Value, Dictionary, RLE) | Základní (page-level) |
| Čtení jednoho sloupce | ✓ Velmi rychlé | Pomalejší (musí čtít řádky) |
| Psaní | Pomalejší, refresh model nutný | Optimalizováno pro write |
| Paměť | Nízká (díky kompresi) | Vyšší |
Kompresní techniky
| Typ | Popis | Příklad |
|---|---|---|
| Value Encoding | Pro numerické sloupce: ukládá hodnoty přímo jako celá čísla s optimálním bitovým rozsahem (alternativa Dictionary Encoding pro vhodná čísla) | SalesAmount → interně 64-bit int bez slovníku |
| Dictionary Encoding | Vytvoř slovník unique hodnot, ulož reference | ["USA","Canada","Mexico"] → 0,0,1,1,2,0 |
| Run Length Encoding (RLE) | Zaznamenej pouze změny hodnot | AAABBC → A:3,B:2,C:1 |
Cardinality: Přítel nebo Nepřítel?
Cardinality = počet unique hodnot ve sloupci. Je to hlavní nepřítel komprese VertiPaq.
| Scenario | Cardinality | Komprese | Výkon |
|---|---|---|---|
| Sloupec "Country" | ~200 unique | ✓ Výborná | ✓ Rychlé |
| Sloupec "OrderID" | 1 000 000 unique | ✗ Špatná | ✗ Pomalé |
| Sloupec "TransactionID" (GUID) | Každá unikátní | ✗ Katastrofální | ✗ Velmi pomalé |
Storage Engine vs Formula Engine
Storage Engine (SE)
SE má za úkol čtení dat z VertiPaq columnar storage. Je rychlý, optimalizovaný pro hromadné operace. Vrací raw data nebo agregáty do cache.
- ✓ Čtení sloupců z disk/memory
- ✓ Aplikování filtrů na úrovni storage
- ✓ Agregace (SUM, COUNT atd.)
- ✓ Vrácení výsledků v cache
Formula Engine (FE)
FE je DAX "CPU". Vyhodnocuje DAX výrazy, volá SE, kombinuje výsledky, provádí kontextové výpočty.
- Vyhodnocování DAX (CALCULATE, IF, ...)
- Row context (iterace přes řádky)
- Kontext-aware transformace
- Potenciální bottleneck pro pomalé dotazy
Performance Implication
Ideální: SE dělá těžkou práci (agregace, filtrace). FE je "tenký" vrstvou pro kontext.
Špatné: FE musí iterovat přes miliony řádků, volat SE stovky/tisíce krát.
-- ŠPATNÉ: FE iteruje po ProductKey, volá SE 1000x MEASURE Sales[Bad Total] = SUMX( VALUES(Product[ProductKey]), CALCULATE(SUM(Sales[Amount])) ) -- DOBRÉ: SE agreguje jednou, FE jen aplikuje filtr MEASURE Sales[Good Total] = SUM(Sales[Amount])
Troubleshooting pomalých reportů
Performance Analyzer v Power BI Desktop
Performance Analyzer měří čas vyhodnocení každého vizuálu (query time, rendering time).
View → Performance Analyzer → Start Recording
Klikej na vizuály, slicery, atd.
Poznamenej si vizuály s vysokým Query time. To jsou kandidáti pro optimalizaci.
DAX Studio: Server Timings
DAX Studio (free tool z daxstudio.org) je nejsilnější diagnostický nástroj pro Power BI.
| Funkce | Co měří | Interpretace |
|---|---|---|
| Server Timings | Storage Engine čas vs Formula Engine čas | Vysoký SE → data problem; Vysoký FE → DAX problem |
| Query Plan | Jaké operace SE provádí (scany, agregace) | Mnoho scans → špatné filtrování; Mnoho agregací → CALCULATE overuse |
| Trace | Detailní log všech SE požadavků | Lze vidět duplicitní/neefektivní requesty |
Běžné Performance Anti-Patterns
| Anti-Pattern | Problém | Řešení |
|---|---|---|
| CALCULATE bez optimalizace | Mění filter context, pomalé pro velké sady | Omezte scope (ALL → konkrétní sloupce), přesuňte do Power Query |
| SUMX / PRODUCTX v Measure | Iteruje řádky, volá SE stovkekrát | Nahraďte agregační funkcí (SUM, AVERAGE) |
| Bidirectional filtry | Filtry se šíří na obě strany, zvýší komplexnost | Preferuj jednosměrné; Bidirectional jen pokud nutné |
| M:N bez bridge tabulky (fact-to-fact) | Fact-to-fact M:N může způsobit double-counting; dim↔dim M:N nativní od 2018 | Implementuj bridge tabulku pro fact-to-fact M:N |
| Počítané sloupce s RELATED | Velké computed columns zvětšují model | Přesuň do Power Query (merge) |
Nástroje optimalizace (Community)
DAX Studio (daxstudio.org)
Zdarma, open-source. Nejlepší pro diagnostiku a testování DAX výrazů.
- Spuštění DAX queries přímo na modelu
- Server Timings pro SE vs FE analýzu
- Query Plan pro detailní insighty
- Formatování DAX kódu
VertiPaq Analyzer (sql.bi community tool)
Exportuj model metadata a analizuj:
- Velikost každého sloupce a tabulky
- Cardinality v každém sloupci
- Potenciální optimalizace (sloupce s nízkou cardinality, redundance)
DAX Formatter (online nástroj)
Formátuj DAX kód pro čitelnost. Důležité pro maintenance.
-- Před (nečitelné) MEASURE Sales[Total]=CALCULATE(SUM(Sales[Amt]),Date[Y]=2023) -- Po (čitelné) MEASURE Sales[Total] = CALCULATE( SUM(Sales[Amount]), Date[Year] = 2023 )
ALM Toolkit (Application Lifecycle Management)
Version control a merge pro Tabular modely. Podporuje Team Development workflow (Git).
Tabular Editor
Externí editor pro Tabular modely s pokročilými možnostmi (bulk operations, scripting).
Přehled nejčastějších chyb v modelování
1. Vysoká Cardinality v Fact tabulce
Chyba: Fact tabulka má sloupec s 10 miliardami unique hodnot (GUID transakce).
Dopad: Model se zvětší 100x, dotazy se zpomalí.
Řešení: Použi surrogate key (Integer identity), ponech GUID jen pro referenční účely mimo fact tabulku.
2. Všechny relace jsou bidirectional
Chyba: Navrhneš všechny relace jako obousměrné bez uvažování dopadů.
Dopad: Kontaminace filtru, pomalejší dotazy, těžší debugging.
Řešení: Defaultně jednosměrná (Both = výjimka). Bi-directional jen pokud velmi nutné.
3. CALCULATE v SUMX iterátoru
-- ANTI-PATTERN: Volá CALCULATE 1000x (jednou za produkt) SUMX( VALUES(Product[ProductKey]), CALCULATE(SUM(Sales[Amount]), ...) ) -- OPRAVA: Přesuň logiku mimo SUMX SUM(Sales[Amount])
4. Fact-to-fact Many-to-Many bez bridge tabulky
Chyba: Přímá M:N relace mezi dvěma fact tabulkami (Sales ↔ Budget).
Dopad: Riziko double-countingu — agregace mohou vracet nesprávné výsledky.
Poznámka: Power BI podporuje nativní M:N (od 2018) — pro dim↔dim scénáře (produkt ↔ kategorie) funguje správně. Bridge tabulka je nezbytná při fact-to-fact M:N.
5. Nerelevantní Computed Columns
Chyba: Vytvoříš 50 počítaných sloupců "na potenciální použití".
Dopad: Model se zvětší, refresh pomalý, údržba složitá.
Řešení: Odstraňte nepoužívané. Pracuj s Power Query pro transformace, počítané sloupce jen pro essentials.