Rules / Performance

Consider using aggregations if using Direct Query in Power BI

info MODEL_USING_DIRECT_QUERY_AND_NO_AGGREGATIONS · ported · model layer · scope: Model

What it checks

Models that have at least one DirectQuery table, no aggregation table (no column has an alternateOf mapping), and the PowerBI_V3 data source version, which is every project Desktop writes today.

Each finding names the model, as Model, and there is one per model however many DirectQuery tables it holds.

Example

Fires the rule

model Model
	culture: en-US
	defaultPowerBIDataSourceVersion: powerBI_V3

table Sales
	column 'Order Date'
		dataType: dateTime
		formatString: mm/dd/yyyy
		sourceColumn: OrderDate

	column Amount
		dataType: decimal
		summarizeBy: sum
		sourceColumn: Amount

	partition Sales = m
		mode: directQuery
		source = let Source = Sql.Database("finance", "Warehouse"), Sales = Source{[Item = "Sales"]}[Data] in Sales

After the fix

model Model
	culture: en-US
	defaultPowerBIDataSourceVersion: powerBI_V3

table Sales
	column 'Order Date'
		dataType: dateTime
		formatString: mm/dd/yyyy
		sourceColumn: OrderDate

	column Amount
		dataType: decimal
		summarizeBy: sum
		sourceColumn: Amount

	partition Sales = m
		mode: directQuery
		source = let Source = Sql.Database("finance", "Warehouse"), Sales = Source{[Item = "Sales"]}[Data] in Sales

table 'Sales by Day'
	isHidden

	column 'Order Date'
		dataType: dateTime
		formatString: mm/dd/yyyy
		sourceColumn: OrderDate

		alternateOf
			baseColumn: Sales.'Order Date'

	column Amount
		dataType: decimal
		summarizeBy: sum
		sourceColumn: Amount

		alternateOf
			summarization: sum
			baseColumn: Sales.Amount

	partition 'Sales by Day' = m
		mode: import
		source = let Source = Sql.Database("finance", "Warehouse"), Agg = Source{[Item = "vwSalesByDay"]}[Data] in Agg

Why it matters

In DirectQuery every visual sends a query to the source. Aggregation tables let the engine answer the common high-level questions, totals by month or by region, from a small imported table and send only the detail queries through. Without them, the summary page of a dashboard pays the full round trip to the source on every interaction. This is an info-level prompt to consider the feature, not a defect.

How to fix it

Build a summary table at the grain the reports use most, one row per day and region rather than one per transaction, and load it from a view in the source so the two tables stay consistent. Import it, keep the detail table in DirectQuery, and in Power BI Desktop right-click the summary table in the model view, choose Manage aggregations, and map each of its columns to the detail column it summarizes and the function that summarizes it. In the TMDL file that mapping is an alternateOf block under each aggregation column, which is the only thing the rule looks for. The block names the detail column in baseColumn, qualified by its table as in Sales.'Order Date', or, for Count table rows, names the detail table alone in baseTable. It sets a summarization, such as sum or count, for every function except group by, which it leaves out. The summary table should be hidden, because report authors write their measures against the detail table and the engine redirects the query when it can. The Manage aggregations dialog hides it for you when you select Apply all, if it is not hidden already; in the TMDL file that is isHidden on the table, as in the example. Any one aggregation mapping anywhere in the model clears this finding, so treat the rule as a prompt to start rather than a measure of how far you got.

When to ignore it

The question is whether there is a summary worth precomputing. A DirectQuery table kept for a handful of live status tiles has none: the whole point is that the numbers are current to the second, and a cached summary would be wrong. A report that only ever reads detail rows, such as a lookup of one order by number, has none either, because there is no total for an aggregation to answer. A source that already serves a summarized view at the grain the report asks for is the third case, and there the aggregation exists, it is just on the other side of the connector. Where the report opens on a page of totals by month over a large DirectQuery fact table, the finding is worth acting on, and at info severity it will not fail a build while you decide.

To ignore this rule on one object, add annotation pbiplint.ignore = MODEL_USING_DIRECT_QUERY_AND_NO_AGGREGATIONS under the object in its TMDL file. Power BI Desktop keeps the annotation. To turn the rule off for a whole project, set "MODEL_USING_DIRECT_QUERY_AND_NO_AGGREGATIONS": "off" under rules in pbiplint.config.json.

Quirks

The data source version has to be present and read as powerBI_V3, compared without regard to letter case. A model file that does not carry defaultPowerBIDataSourceVersion at all is never reported, whatever its tables say.
One column with an alternateOf block anywhere in the model clears the finding, whatever it maps to. A single half-finished aggregation table counts as much as a complete set.
A table counts as DirectQuery when its first partition says mode: directQuery, letter case aside. No other mode counts, so a table set to Dual storage mode is not a DirectQuery table here.
While a model file has a parse issue that can take a declaration out of the model, such as a line indented with spaces, or pbiplint could not open a model file or folder at all, the rule reports nothing, because an aggregation table could be in what pbiplint missed, and pbiplint does not guess what a file it could not read says. The skipped line gives the reason, a model file could not be fully read, and the file's own PARSE_ISSUE finding names it, or a notice does for a file or folder pbiplint could not open.

MEASURES_USING_TIME_INTELLIGENCE_AND_MODEL_IS_USING_DIRECT_QUERY makes the same test for a DirectQuery table and reports the measures that call a time intelligence function. Importing every table clears both.