First Impressions of DAX Optimizer

A few weeks ago, I was invited to try out DAX Optimizer, the new product from Marco Russo and Alberto Ferrari of SQLBI fame, in its BETA trial. If you’re unfamiliar with these names then I wholeheartedly encourage you to invest some time reading their content, signing up to their weekly newsletter, and adding them to the list of resources you use when trying to resolve or structure a DAX calculation. If you are familiar with their work, then you’ll know exactly why I was excited to get my hands on a licence.

Now available in “Public Preview”, meaning you can sign up and test it without a wait, and with a sample data model uploaded, allowing you to try before you buy. DAX Optimizer performs a fixed analysis of all DAX inside a data model and identifies problems that may lead to inefficient queries, slowing the performance of your reports. The knock on effect being frustrated users losing faith in your data. But flagging issues is not all it will do, with Marco and Alberto’s vast understanding of the way DAX works they have gone several steps further by pointing towards solutions and ranking each issue by relevance, allowing you to focus on the code that will have the greatest impact on your model's performance first. If I haven’t already whetted your appetite, all of this and more is explained in their introductory blog “Introducing DAX Optimizer”.

As someone who has been working with DAX reasonably intensively for the last 6 years, I class myself as fairly aware of the Do’s and the Don’ts, the best practices to abide by and the anti-patterns that you should avoid like the drunk colleague at a Christmas party. I also take pride in testing and optimising code written by our customers and walking them through how I’ve improved it, furthering their knowledge of DAX in tandem. But, there’s always something you don’t know, something you’ve forgotten and (with the complex, enterprise-scale Power BI solutions I most often find myself dealing with) something basic you’ve done in a hurry and dropped the ball on when it comes to best practice.

So, with all this in mind I was intrigued to give it a whirl and put my skills to the test, whilst also documenting my impressions for this post. I’ll dive into these over the course of the findings below.

Finding 1: You absolutely can teach an old dog new tricks.

This wasn’t necessarily the first thing I found, but it definitely had the most impact on both my own impressions of the tool and the model I used it on. Yes, that’s right, DAX Optimizer introduced me to a rule I was not aware of and gave me pointers towards making a whole tranche of measures more efficient.

It’s important to note here, DAX Optimizer will not give you the code you need to implement to achieve this, but the examples it provides can be used in conjunction with DAX Studio to test some new structures for your measures and assess the impact of the change.

In my case, after several iterations, I was able to bring a query taking an average of 40 seconds down to 11, halving the CPU time in the process. As the images below also show, the main driver for this was reducing the weighting towards the Formula Engine (FE) by nearly 20%. Achieved by slashing the number of rows the measure calculates over from 13 million to around 1.7 million as evidenced in line 12 of both images.

This saving alone was enough for me to realise DAX Optimizer is a tool I would recommend to anyone writing advanced DAX.

Finding 2: It will find those simple slip ups.

We all know we should use a variable (VAR) to store an item we’re going to use more than once. It helps with readability and there may be a performance benefit too, achieved by storing the value so the measure doesn't have to run the same calculation twice. However, there’s always instances where the calculation is so simple (think % change) that we’ve written them in our sleep and referenced the same measure multiple times, rather than using the aforementioned variable. Guilty as charged here, and thanks to DAX Optimizer, all 39 instances of this in my model have been updated to comply with best practice.

Finding 3: There wont always be an impact.

You shouldn’t just take everything as gospel and set about re-writing your measures to match the suggestions. DAX is model specific, and code that is optimal in one scenario will not always make a difference in yours, testing before implementing is key.

I was advised to consider my use of an “&&” operator when filtering with multiple columns over a single table, with the potential change involving REMOVEFILTERS and the use of commas.

However, after assessing several different use cases for these measures, I found there was no benefit to changing the code with both the original and proposed solutions being comparable across total, FE and SE query times. SE CPU usage was also near enough identical, and when running a benchmark across both Cold and Warm caches, again there was no significant difference found. Sometimes you're just right in the first place!

Finding 4: The Expert View gives you an incredible amount of detail.

Aside from helping you resolve possible inefficiencies in your DAX code, DAX Optimizer will also give you an incredible amount of detail on the impact of each measure (not just those with potential issues) in your model. This comes when you switch over to the expert view on the measures tab.

The most important column to pay attention to here is ‘Relevance’ which estimates the impact of each measure on the performance of the model, essentially this column is your prioritisation, tackle those issues with the highest relevance value first. The remaining columns seem to provide context to the relevance score, with the last two columns being of particular interest, especially where measures have no issues. They show you how many times your measures are directly or indirectly referenced by others in the model, giving an indication of the their importance to the model as a whole. This can be a great tool when you need to understand the impact of a change, i.e. go careful when editing something that's directly referenced 76 times, and indirectly 256! The 'CPU' and 'RAM' columns come into play where issues are present and provide estimations for the cost and optimisation % of the issues detected. It’s absolutely no surprise a bunch of measures making use of RANKX and ALLSELECTED score high for these metrics, although optimising these is unlikely.

Wrapping Up

My first impressions of DAX Optimizer are all positive. It's already a great resource for understanding where you may be able to make improvements that will benefit the users of your data, and help you spend less time diagnosing slow calculations. If you're learning best practice with DAX, then it can also help give you a leg up on this journey. It's also very early days, so I can only see it getting better as more feedback is given by users during the preview.

I can see it becoming part of my arsenal for a long time.

My only gripe?

Being British, it's the 'z' in its name, but that's hardly the fault of its creators, so I'll let them off.