How does the VertiPaq engine store data?
By column, compressed, in memory. Each column is encoded (as values or through a dictionary of its distinct values) and then compressed further where the same value repeats in a run. So a column's size depends on how many distinct values it has, its cardinality, much more than on the row count: a yes/no column of a hundred million rows is tiny, a unique transaction ID or a datetime to the second is large.
How do you make a Power BI model smaller?
Remove columns and rows nobody uses, and lower cardinality: split a datetime into a date and a time column, round decimals that do not need full precision, drop unique IDs that are not keys. Prefer integer keys, a star schema, and columns added in Power Query or the source over DAX calculated columns on large tables. Turn off Auto date/time. Then check what is left with VertiPaq Analyzer in DAX Studio.
How do you find which visual makes a page slow?
Open View, Performance Analyzer, start recording and refresh the visuals. Each visual's time is split into the DAX query, the visual display and other (waiting for other visuals). A slow DAX query points at the measure or the model, a slow display at the visual itself (too many points or a heavy custom visual). Copy the query from there to run and tune it in DAX Studio.
What do the Server Timings in DAX Studio tell you?
They split a query's time between the storage engine, which scans the compressed columns in parallel, and the formula engine, which works single threaded on what the storage engine returns. Many storage engine queries, or a large formula engine share, point at the measure: iterators over big tables, callbacks where the storage engine has to ask the formula engine row by row, or large intermediate tables. Good measures push work into a few storage engine scans.
Which DAX patterns make measures slow?
Filtering a whole fact table where a column filter would do, nested iterators over large tables, context transition over millions of rows, and repeating the same measure instead of storing it in a variable.
-- slower: iterates every Sales row Red Sales Slow = CALCULATE ( [Total Sales], FILTER ( Sales, RELATED ( Product[Color] ) = "Red" ) ) -- faster: a filter on one column Red Sales = CALCULATE ( [Total Sales], Product[Color] = "Red" )Why does the number of visuals on a page matter?
Every visual sends its own query when the page loads and again on every slicer click, so twenty visuals means twenty queries per interaction. Keep the main page to the visuals that answer its question, move details to drillthrough pages or tooltips, avoid table visuals with thousands of rows, and consider the query reduction options, which add an Apply button to slicers and filters.
How do you speed up a DirectQuery report?
Most of the work happens at the source, so tune it there: indexes on the join and filter columns, views that do the heavy logic, and enough capacity. In Power BI, keep Power Query steps that fold, use fewer visuals, set relationships to assume referential integrity (inner joins) where the data allows, store dimensions in Dual mode, and add Import aggregation tables for the common summaries.
What are aggregations in Power BI?
An aggregation table is a smaller, summarized copy of a large fact table, for example sales by day and product instead of by transaction, usually in Import mode over a DirectQuery fact. You map its columns to the detail table with Manage aggregations. Queries at that grain or above are answered from the small table in memory; queries that need more detail go to the source. Readers see one table and never choose.
Why do bidirectional and many to many relationships hurt performance?
A filter that can flow both ways has to be applied in more directions and on more tables for every query, and the engine cannot use the cheaper one directional join. Many to many relationships join at a coarse grain and often expand large intermediate tables. Use them where the model needs them, keep the rest single direction, and use
CROSSFILTERin the few measures that need the other way.Where should a new column be computed: the source, Power Query or DAX?
As far upstream as possible: in the source or a view, then in Power Query, and in DAX only when it needs the model, such as a column built with
RELATEDacross several tables or a ranking. Columns added upstream compress better, refresh faster and can be reused by other tools. A DAX calculated column is computed after the data loads, on every refresh.
Power BI Performance Interview Questions
Finding and fixing slow reports: the VertiPaq engine, model size, Performance Analyzer, DAX Studio, DirectQuery and aggregations.