DAX Expression > DAX query > Vertipaq query.
Test 1: Testing the Impact of USERELATIONSHIP() in a DAX Measure
Does using USERELATIONSHIP() with inactive relationships slow down a DAX measure? I’m not sure so I thought I would test it out.
To test this I will create a measure using the existing between my date table and my FactOnlineSales table and then I created a copy of the date table and joined it in the same way to the FactOnlineSales table, but making the relationship inactive.
I use DAX studio to create 2 measures for calculating total sales and then I throw in some filters for year and continent as follows:
DEFINE
MEASURE '_Measures'[test1] =
CALCULATE (
SUMX (
'FactOnlineSales',[Sales Amount]
),
'DimDate'[CalendarYear] = 2009,
'DimGeography'[ContinentName] = "Europe"
)
MEASURE '_Measures'[test2] =
CALCULATE (
SUMX (
'FactOnlineSales',[Sales Amount]
),
'DimDate (2)'[CalendarYear] = 2009,
'DimGeography'[ContinentName] = "Europe",
USERELATIONSHIP('DimDate (2)'[Datekey], FactOnlineSales[DateKey])
)
EVALUATE
ROW (
"test", [test2]
)Below is a screenshot of my DAX studio settings. As you can see I’ve got the ‘Clear cache on Run’ button selected. This will help ensure each test is equal. I also have the use ‘Server Timings’ button selected. Note be careful using ‘Clear cache on run’ on a connection to a production model as it will clear the cache (hence slowing down reports).
For the original sale measure that has an active relationship to the data table, I get a time of 2,197 which is broken down into FE (Formula engine) is 624 ms and 1,573 ms for the SE (Storage engine -that’s the Vertipaq engine). The FE turns the DAX expression into a DAX query and creates a query plan that it sends to the SE to retrieve the results of the query. The FE also caches the results of queries (this is why I have turned on the clear cache when the DAX query is run, otherwise the subsequent queries will return results very quickly).
Comparing the 2 measures
Here we run the query on each measure 5 times to get an average and as you can see there was very little difference in performance. Indeed the first 3 tests on the measure with the active relationship took longer (but this may have been because my laptop was busy doing something). My results are below, I think they are pretty inconclusive, but they are interesting none the less, and the method would be interesting to test with a larger model.
In the server timings you can view the Vertipaq query that is executed by the Analysis services:
Each step of the execution is below:
The first step selects the data from FactOnlineSales with the 2 filters returning 1.4M rows of data of 133 KB.
The second step does the sum of the sales amount and returns one row. The third step returns the start and end time as well as other information (if you mouse over the query).
The Par. field stands for Parrallism, which represents the number of parallel threads used by the storage engine to retrieve the result at each step.