PL-300 · Domain 2: Model the data · 27% of the exam
Task 2.3: Optimize model performance
Removing unnecessary rows and columns, finding slow measures, relationships and visuals with Performance Analyzer and DAX query view, and reducing granularity.
Study it
Performance Analyzer and DAX query view
Lesson coming
Reducing rows, columns and granularity
Lesson coming
Sample questions
Try each one before opening the answer. Every option is explained, with the Microsoft documentation page that proves it.
Question 1 · choose 1
A report page has 30 visuals, mostly cards. In Performance Analyzer, each visual's DAX query takes under 100 ms and Visual display under 50 ms, but Other is several seconds for most of them. Which change addresses what the timings show?
AUse fewer visuals, for example one multi-row card instead of many cards
BRewrite the measures behind the cards to make their DAX queries run faster
CSwitch the fact table to DirectQuery for the visuals on the page
DTurn on Assume referential integrity for the relationships
Show the answer and why
AUse fewer visuals, for example one multi-row card instead of many cards
Correct
Other is mostly time spent waiting for other visuals, so fewer visuals means less waiting.
BRewrite the measures behind the cards to make their DAX queries run faster
Incorrect
The DAX queries already return quickly, so faster measures won't remove the wait.
CSwitch the fact table to DirectQuery for the visuals on the page
Incorrect
DirectQuery adds source query time; it doesn't reduce the queueing between visuals.
DTurn on Assume referential integrity for the relationships
Incorrect
A relationship setting doesn't change how long visuals wait for each other.
Performance Analyzer breaks each visual's load into DAX query, Direct query, Visual display and Other. Other covers preparing queries, waiting for other visuals to complete and other background processing, and operations share a single UI thread, so durations include time queued. A page with many visuals spends much of its time waiting.
A 200-million-row Sales table has an OrderNumber column such as SO123456, nearly unique per row, that readers use to look up orders. It's one of the largest columns in the model. The SO prefix is the same on every order. What reduces the column's size while keeping the lookup?
AKeep the SO prefix but hide the OrderNumber column in Model view
BRecreate OrderNumber as a DAX calculated column
CMove OrderNumber into a separate dimension table related on OrderNumber
DStrip the SO prefix and store the number as Whole Number
Show the answer and why
AKeep the SO prefix but hide the OrderNumber column in Model view
Incorrect
Hidden columns are still loaded, so the size doesn't change.
BRecreate OrderNumber as a DAX calculated column
Incorrect
Calculated columns typically compress less well than columns from Power Query.
CMove OrderNumber into a separate dimension table related on OrderNumber
Incorrect
A dimension with one row per order is just as large, and it adds a relationship.
DStrip the SO prefix and store the number as Whole Number
Correct
Numeric columns use value encoding, which stores high-cardinality values far more compactly than hashed text.
VertiPaq uses value encoding for numeric data and hash encoding for text, which needs a dictionary of every unique value. Converting a consistently prefixed text key such as SO123456 into a whole number can greatly reduce size for high-cardinality columns. Setting the column to Do Not Summarize avoids meaningless sums of order numbers.
A model has a dozen DAX calculated columns on its fact table, each computed only from values in the same row, such as Quantity times Unit Price. Refresh has become slow, and the columns compress poorly. What does data reduction guidance suggest?
ATurn them into measures that use SUMX over the fact table
BMark the calculated columns as hidden so the engine skips storing them
CMove the calculated columns into a calculated table
DCreate them as custom columns in Power Query or at the source instead
Show the answer and why
ATurn them into measures that use SUMX over the fact table
Incorrect
The guidance's advice for row-level columns like these is to compute them in Power Query or at the source, not to replace them with measures.
BMark the calculated columns as hidden so the engine skips storing them
Incorrect
Hidden columns are still computed and stored.
CMove the calculated columns into a calculated table
Incorrect
A calculated table is also built after load and stored the same way.
DCreate them as custom columns in Power Query or at the source instead
Correct
Columns from Power Query compress better and are built during load rather than after all tables are loaded.
Calculated columns defined in DAX are stored like other columns, but they typically compress less efficiently and are built after all Power Query tables load, which extends refresh time. Guidance recommends creating such columns as Power Query custom columns, or in the source, unless the formula needs measures or DAX-only functionality.
An import model is near its size limit. Reports need daily sales by product for the last three years, but the fact table holds ten years and 40 columns, of which reports use 12. Which two changes reduce the model's size without losing anything reports need? (Choose TWO.)
AFilter the fact table to the last three years in Power Query
BGroup the fact table by month and product in Power Query
CHide the 28 unused columns in Model view
DConfigure incremental refresh that refreshes the last three years and archives ten
ERemove the 28 unused columns in Power Query
Show the answer and why
AFilter the fact table to the last three years in Power Query
Correct
Loading only the needed history removes rows the reports never use.
BGroup the fact table by month and product in Power Query
Incorrect
Monthly grain loses the daily detail that reports need.
CHide the 28 unused columns in Model view
Incorrect
Hidden columns are still loaded and stored in the model.
DConfigure incremental refresh that refreshes the last three years and archives ten
Incorrect
Incremental refresh changes what's refreshed, but the model still stores ten years.
ERemove the 28 unused columns in Power Query
Correct
Columns that serve no reporting or model purpose can be removed, which saves their storage.
Data reduction techniques for import models include removing unnecessary columns (vertical filtering), removing unnecessary rows, such as limiting history (horizontal filtering). Hiding a column doesn't remove its data.
In Performance Analyzer, which duration shows the time between a visual sending its request and the semantic model returning the results?
AVisual display
BDAX query
COther
DEvaluated parameters
Show the answer and why
AVisual display
Incorrect
Visual display is the time to draw the visual on screen.
BDAX query
Correct
It measures the time from sending the query to getting results from the model.
COther
Incorrect
Other covers background work by the visual, not the query itself.
DEvaluated parameters
Incorrect
This covers evaluating field parameters within a visual.
Performance Analyzer splits each visual's duration into categories: DAX query, Direct query for DirectQuery sources, Visual display, Other, and evaluated parameters for field parameters.
You recorded a slow report page in Performance Analyzer and want to send every recorded timing to a colleague as a file she can open later. What should you do?
ASelect Clear when you finish recording
BSave the report as a Power BI template
CSelect Stop, which saves the results in the .pbix file
DSelect Export in the Performance Analyzer pane
Show the answer and why
ASelect Clear when you finish recording
Incorrect
Clear erases the information in the pane, and it isn't saved.
BSave the report as a Power BI template
Incorrect
A template holds the report pages, model and queries, not the recorded timings.
CSelect Stop, which saves the results in the .pbix file
Incorrect
After Stop, the results stay in the pane for you to analyze; Stop doesn't write them to a file.
DSelect Export in the Performance Analyzer pane
Correct
Export creates a .json file with the information from the Performance Analyzer pane.
Performance Analyzer keeps its results in the pane after you select Stop, and Clear erases them without saving. To save them, select Export, which creates a .json file with the information from the pane that you can share or keep.
While editing a DirectQuery report in Power BI Desktop, every field you drag into a visual sends a query to the source. You want to make several changes first and send queries only once. Which feature does that?
APause visuals on the Optimize ribbon
BThe Interactivity optimization preset
CAuto date/time in the file options for query speed
DAssume referential integrity on relationships
Show the answer and why
APause visuals on the Optimize ribbon
Correct
Paused visuals enter a pending state and don't query until you refresh or resume.
BThe Interactivity optimization preset
Incorrect
This keeps real-time updates, which send queries as you go.
CAuto date/time in the file options for query speed
Incorrect
This creates hidden date tables; it doesn't pause queries.
DAssume referential integrity on relationships
Incorrect
This is a relationship setting; it doesn't hold queries while you edit.
Pause visuals on the Optimize ribbon stops visuals from sending queries while you author; visuals that need a query show as pending until you refresh or resume. This avoids many intermediate DirectQuery queries.