I have been playing around with the new DAX INFO functions since they went into public preview. My use case was mostly getting the metadata of a semantic model. The old fashioned way to get this is via XMLA queryin the DMVs. The DAX.INFO() functions have a few advantages outlined here.
There was a discussion on Reddit recently where I was thinking that they also may cost less (in terms of CU seconds). This is incorrect though.
I have now run a test on about 600 semantic models in an F2 capacity comparing the cost of running DMVs via XMLA vs. DAX INFO functions (via the execute DAX queries API call).
My findings are quite surprising, especially when you look at the costs.
What exactly did I do?
-Pull metadata like CALCDEPENDENCIES for ~600 semantic models -> via external tool "Measure Killer"
-Check the CU seconds cost in my F2 capacity before and after.
-Repeat a day later and compare averages
Here are the results:
In terms of speed I guess it is no surprise that XMLA is much faster - 20 min. vs more than 1 hour for DAX.INFO()
In terms of cost (measured in CUs - Compute Unit seconds) I got the following:
The results for pulling metadata for ~600 semantic models (F2 capacity)
The number shown are in CUs (Compute Unit seconds)
XMLA | DAX.INFO() | Cost multiplier (DAX.INFO costs x times more) | |
Run 1 | 3.4 (avg per model) | 8.8 (avg per model) | x2.58 |
Run 2 | 2.8 (avg per model) | 7.9 (avg per model) | x2.82 |
Average | 3.1 (avg per model) | 8.4 (avg per model) | x2.71 |
Out of the 595 models, only 15 had a lower cost via the DAX.INFO functions.
580 models (97,5%) had a lower CU cost via XMLA.
Interestingly, both approaches consume very little compute overall.
If you run this metadata scan of these ~600 semantic models,
XMLA cost 1,836 CUs => 1% of a daily F2 allowance.
DAX.INFO() cost 4,988 CUs => 2,9% of a daily F2 allowance.
Cheapest (MIN) and most expensive (MAX) models per run (in CU seconds)
XMLA MIN | XMLA MAX | DAX.INFO() MIN | DAX.INFO() MAX | |
Run 1 | 0.8 | 21.8 | 0.1 | 113.3 |
Run 2 | 0.8 | 34.5 | 2 | 95.8 |
Here you can find the full Excel table including all raw data.