Modeling for Power BI: optimize the semantic model with VertiPaq

The VertiPaq engine in Power BI (and Analysis Services) has storage and execution characteristics different from a traditional RDBMS. Modeling specifically for VertiPaq can improve query performance by 10x or more.

How VertiPaq stores data

VertiPaq = columnar + dictionary compression
  - Each column stored separately
  - Dictionary: unique values → compact integer IDs
  - Run-length encoding for consecutive repeated values
  - Impact: columns with FEW unique values compress better
  - 1 TEXT column "Country" with 50 unique values ≈ 3-4 bytes/row
  - 1 TEXT column "Description" with 1M unique values ≈ 50+ bytes/row

Golden rules for VertiPaq

1. REMOVE columns not used in visuals or relationships
   → Each column takes memory EVEN without relationships

2. AVOID unnecessary high-cardinality columns
   → Fields like "free comment" or "sequential ID with prefix"
   → Prefer integers (ID INT) to strings ('PROD-001234')

3. NEVER use RELATED() in calculated columns on fact tables
   → Materializes the column on every row — blows up the model

4. USE measures instead of calculated columns for aggregations
   → Measures compute at query time; calculated columns take memory

5. DATES as Date type (not DateTime) when you don't need time
   → DateTime has much higher cardinality

Diagnose with DAX Studio

-- See the size of each model column
EVALUATE
SELECTCOLUMNS(
    FILTER(
        INFO.STORAGETABLECOLUMNS(),
        [DICTIONARY_SIZE_KB] > 100
    ),
    "Table",       [DIMENSION_NAME],
    "Column",      [ATTRIBUTE_NAME],
    "Dict KB",     [DICTIONARY_SIZE_KB],
    "Data KB",     [USED_SIZE_KB],
    "Cardinality", [SEGMENT_COUNT]
)
ORDER BY [DICTIONARY_SIZE_KB] DESC

Relationships and filtering

-- Rules for Power BI relationships:
✓ Whenever possible: 1:N relationship (dimension → facts)
✗ Avoid: direct many-to-many
✗ Avoid: bidirectional filtering (Both)
   → Causes ambiguous queries and filter loops
   → Use the DAX CROSSFILTER() function only when absolutely necessary

Conclusion

Optimizing for VertiPaq is not about SQL — it is about cardinality, number of columns and measure design. A well-optimized model fits in 10x less memory, loads in half the time and answers queries 5-10x faster. Invest 30 minutes in DAX Studio after each model refresh.