Performance Optimization

Scenario: 500M-row fact, hourly updates, fast source warehouse.

• If most reports are historical trends and do not need hourly latency, choose Import with incremental refresh.
• If users require near real-time at high granularity and the source is very fast, consider DirectQuery.
• For both: Hybrid (Import for history, DirectQuery for hot data) or a Hybrid/Incremental approach.

Rule of thumb: Start with Import + incremental refresh. Move high-latency needs to DirectQuery partitions if justified.

Mini task: Write a one-paragraph storage plan for your largest model, noting why you chose it.

Concept: Partition fact data by date; refresh only recent partitions.

1. Add Date column in the fact (not text), ideally a full datetime.
2. Parameterize RangeStart and RangeEnd.
3. Filter the fact table in Power Query using RangeStart/RangeEnd.
4. Configure policy (e.g., store 5 years; refresh last 7 days).

// M filter example
FilteredRows = Table.SelectRows(FactSales, each [SaleDate] >= RangeStart and [SaleDate] < RangeEnd)

Mini task: Propose a refresh policy that balances size and latency (e.g., store 36 months, refresh last 3 days).

Slow pattern (iterates rows unnecessarily):

-- Slow: row-by-row iteration
[Total Sales Slow] = SUMX(FactSales, FactSales[SalesAmount])

Better:

[Total Sales] = SUM(FactSales[SalesAmount])

With filters and variables:

[Store Sales (This Year)] = 
VAR TY = CALCULATE( SUM(FactSales[SalesAmount]), YEAR('Date'[Date]) = YEAR(TODAY()) )
RETURN TY

Tips:

• Prefer SUM, COUNTROWS, AVERAGE over X-iterators when possible.
• Use variables to avoid repeating expensive filters.
• Limit the number of CALCULATE modifiers and nested filters.

Mini task: Replace at least one SUMX/AVERAGEX with a base aggregator and variables where appropriate.

Good: filter and type changes that fold to SQL.

// Good folding
Filtered = Table.SelectRows(FactSales, each [SaleDate] >= #date(2024,1,1))
Typed = Table.TransformColumnTypes(Filtered, {{"SalesAmount", type number}})

Risky: early Table.Buffer, custom row-by-row functions, adding indexes before filters.

// Breaks folding (example)
Buffered = Table.Buffer(FactSales)
AddedIndex = Table.AddIndexColumn(Buffered, "Index", 1, 1, Int64.Type)

Check folding using your tool’s native query view. If a step breaks folding, try moving it later or replace it with a foldable operation at the source.

Mini task: Reorder steps so all filters occur before non-foldable steps; confirm folding is preserved.

• Limit visuals per page (aim for ~8 or fewer).
• Use simpler visuals (bars/lines) with fewer data points.
• Disable cross-highlighting when unnecessary.
• Use slicers sparingly; consider a single filter pane or parameters.

Mini task: Remove two non-essential visuals from a busy page and retest load time.

• Measure visual, DAX, and query times with performance tools in your BI environment.
• Validate that aggregations are hit; if not, review mappings and relationships.
• Export query plans/logs when supported and analyze the slowest visuals first.

Mini task: Identify your three slowest visuals and note whether the delay is DAX, visual rendering, or data query time.

• Does your slow visual hit the aggregation table? If not, align dimensions and mapping.
• Is query folding on? Check native query; reorder steps to restore folding.
• Which component is slow: DAX compute, data query, or visual render? Optimize the dominant part first.
• Are slicers/filters exploding the result set? Limit multi-select or use fewer slicers.

• Model size reduced by at least 20% from baseline.
• Aggregation table confirmed as query source for trend visuals.
• Incremental refresh refreshes only recent partitions.
• All visuals render within target time; no unnecessary cross-highlighting.