Better performance with ADDCOLUMNS instead SUMMARIZE

There is three tables: City,Calendar,Pdp. Pdp is the fact tables with 20.000.000 rows.

ER

execute the  script below  and analyze the query plan with profiler

    evaluate
	summarize( 'Pdp',
			    'Calendar'[year],
				'City'[City],
				"Sales",
				SUM('Pdp'[Price])
		     )

the profiler shows that VertiPaq engine get years and cities with a scans the fact table

SummarizeScan

if i take the year and the city is better to scan the tables of the calendar and the cities than in the fact table because they are smaller. City table contain 1168 row and calendar table contain 1400 row .

rewrite the query using ADDcolumns:

evaluate
	addcolumns( 
				crossjoin(VALUES('Calendar'[year]),
						  VALUES('City'[City])
			             ),
				"Sales",
				CALCULATE(SUM('Pdp'[Price]))
		     )
the profiler shows that VertiPaq engine get years and cities with scans from both tables (Calendar,City) but don't use fact table.

AddColumn1

 

AddColumn2

 The Duration is reduced. our fact table has a few lines. But  if he had more row the difference between the two techniques would be better.

 

Comments are closed.

Switch to our mobile site