The blog has been a bit stagnant… even though I have quite a backlog of things to share. In my defense, I did create over 900 measures in a single Power Pivot model for a customer since we last talked. Though… that idea sounds so crazy I probably shouldn’t bring it up.
There has been a ton of changes in the Power BI landscape… I’m skipping that discussion. I have two different “Part 1” posts… without completing the corresponding Part 2. I’m skipping those, too. I was able to write an addin for Excel 2013 that lets me import measures to my model from a text file… and, I’m skipping that discussion, too. For today, we talk about my new love of DAX Studio.
Why New Love?
I have had DAX Studio installed a long time. It’s just… I haven’t really used it a ton. I would use it occasionally to dump all my measures out of my model (eg, so I could easily search for certain column references) or maybe to help debug a complicated measure. But that was really about it. However, as of version 2.2.0, the author of DAX Studio, Darren Gosbell unleashed (for me) its most awesome feature… Tracing (of query plans and server timings) against Power Pivot models! While this existed for tabular models… it wasn’t available for Power Pivot and there was just no convenient way to get that data. Data that I really really wanted sometimes.
Let’s say we have the awesome table at left with 3 columns of numbers, and we need a measure to add them all up.. but only if all values are > 10000. We have a ton of ways we could write this measure… some examples:
SUM1:=CALCULATE(SUM(Table1[Value1]) + SUM(Table1[Value2]) + SUM(Table1[Value3]), Table1[Value1] > 10000, Table1[Value2] > 10000, Table1[Value3] > 10000)
SUM2:=CALCULATE(SUMX(Table1, Table1[Value1] + Table1[Value2] + Table1[Value3]), Table1[Value1] > 10000, Table1[Value2] > 10000, Table1[Value3] > 10000)
SUM3:=CALCULATE(SUMX(Table1, Table1[Value1] + Table1[Value2] + Table1[Value3]), FILTER(Table1, Table1[Value1] > 10000 && Table1[Value2] > 10000 && Table1[Value3] > 10000))
Of course, we are pretty darn curious which method (if either) is faster. Cuz we love performance. Who doesn’t!?
This is really easy to test out with the new DAX Studio features!
A bit of “How To”
I just launch DAX Studio, and write the following code: evaluate row(“SUM1”, [SUM1])
Which… is a bit weird if you have never played in DAX Studio or SSMS, but isn’t complex. “evaluate” just dumps the contents of the table passed as an argument. ROW( ) is a function that returns a table from a caption and expression – in this case our [SUM1] measure.
Before you run it, simply click the options for Query Plan and Server Timings. That way, once you run the measure (using F5), you will get some new tabs. The Query Plan is probably only useful for the true ninjas, but I like to keep it there to feel smart
However, the Server Timing are awesome.
Total shows you the total time in milliseconds, so… that’s obviously darn useful, and how we will see that [SUM3] is the fastest (which may or may not have surprised me). On the left, you are really hoping for your time to be spent in SE (Storage Engine) rather than FE (Formula Engine) since the former is wicked stupid super fast… so consider re-writing queries to encourage less FE usage.
On the right you can see the steps taken, and how long each took. You can click on the steps to see the SQL that got executed. This will really help you understand how your measure is being evaluated, and hopefully give you clues on how to re-write it.
Thanks and Warnings
First, thanks to Darren. For a performance geek like me… this functionality is just huge. It’s roughly a million times easier than trying to capture a profiler trace! Secondly, please try not to infer too much off my stupid tests above. My data was purely random… real data isn’t like that! There is only 1 way to know the best way to write a measure for your data, and that is to measure it. With DAX Studio!