SQL SERVER – Becoming a More Effective DAX Developer with DAX Studio – Notes from the Field #134

[Note from Pinal]: This is a new episode of Notes from the Field series. I personally have no problem accepting that I do not know many topics. One of the such topics is DAX Studio. I always wanted to learn more and I finally got the opportunity to learn more.

In this episode of the Notes from the Field series I asked SQL Expert Bill Anton a very crucial question – How to become a more effective DAX developer with DAX Studio? Bill was very kind to answer the questions and provides plenty of information about how a novice developer can work with DAX Studio.

If you’ve been doing any sort of development involving DAX, whether it’s with Power BI, Power Pivot, or Analysis Services Tabular, and you’re NOT using DAX Studio, then you’re doing it wrong!

But don’t worry. In this post, I’m going to walk you through this incredible tool and show you how it can improve your development experience and enable you to write better, more efficient DAX.

Disclaimer: DAX Studio is an incredible (and completely FREE) tool that significantly improves the developer experience when writing and performance tuning DAX code. This is not some blatant marketing tactic. Nor have I been asked to write this blog about it. I’m just a typical SSAS developer who has found this tool very helpful in my client engagements, so much so in fact, that I feel the need to tell others about it.

The Basics: Writing DAX

DAX is a functional programming language and whether you’re writing a query or a calculation, the amount of function nesting can get intimidating. Take the snippet below which calculates the total sales amount for the prior year:

Internet Total Sales PY :=
CALCULATE (
    SUMX (
        'Internet Sales',
        'Internet Sales'[Order Quantity] * 'Internet Sales'[Unit Price]
    ),
    SAMEPERIODLASTYEAR ( 'Date'[Date] )
)

You’re probably thinking to yourself, “that doesn’t look so bad. What is this guy whining about?”

To show you what I mean, here’s the same DAX measure as originally written in SQL Server Data Tools (SSDT) – which is the default tool for developing Analysis Services Tabular models.

Hold on, let me get you a magnifying glass…

Is that better? Not really?

Exactly! So imagine having to write the following DAX code in that silly little formula bar…

Open Orders :=
SUMX (
    FILTER (
        GENERATE (
            SUMMARIZE (
                'Internet Sales',
                'Internet Sales'[Order Date],
                'Internet Sales'[Ship Date],
                "Rows", COUNTROWS ( 'Internet Sales' )
            ),
            DATESBETWEEN (
                'Date'[Date],
                'Internet Sales'[Order Date],
                'Internet Sales'[Ship Date] - 1
            )
        ),
        'Date'[Date] = EARLIER ( 'Date'[Date] )
    ),
    [Rows]
)

Right? I don’t want to do it either. But I have…

…and I see others in the field having to do it too.

Where’s the font colors indicating reserved words, functions, etc? And don’t even get me started on how annoying it can be when you’ve just spent 5-10-15+ minutes writing a new complex DAX calculation only to be greeted with an error message indicating you accidentally missed a closing parenthesis or closing column bracket. Hopefully you’re prepared to start over from scratch because after closing the error message you’re going to be left with a blank cell.

This type of scenario has quickly led to many developers writing DAX in a general text editor (e.g. Notepad++) before copying it over to the DAX formula bar in SSDT. And while that saves you from losing the 5-10-15+ minutes of rewriting the DAX code, you’re giving up intellisense, which, in a complicated model, is very VERY helpful.

Note: to be fair, most of my complaints are directed at the SSAS Tabular developer interface (i.e. SSDT for SQL Server 2014 and 2012). Power BI (and SSDT 2015 for SSAS 2016) is much better in these regards. For example, Power BI (and SSDT 2015 for SSAS 2016) includes syntax highlighting (i.e. color coding function/reserved words).

After spending a few weeks/months in the pits working with the SSDT built in DAX editor in SSDT, I eventually found my way over to using DAX studio which includes syntax highlighting, intellisense, formatting, and a ton of other helpful features!

So, for example, even if I write my code out in a single line like a robot…

…I can hit this handy little button…

…and get some super readable code…

The features you see above, again, super basic features that should be included in any development environment (syntax highlighting, automatic formatting, intellisense, etc) are enough of a reason for me to use DAX Studio for over 95% of the DAX code I write. And I haven’t even shown you the good stuff!

Advanced: Optimizing DAX

As mentioned in the previous section, DAX is a functional language and you, as a developer, have to be very explicit in telling the engine what you want it to do. This is quite different from SQL, a declarative language, where we “declare” to the optimizer what we want the output to be and it is up to the optimizer to determine the most efficient way to get there.

What this means is that the same SQL query can be written a number of different ways and still result in the same (or very similar) query execution plan… whereas the same DAX query or calculation can be written a number of different ways and performance can differ by orders of magnitude. Bottom line: as DAX developers you spend a good bit of time writing the same DAX calculation or query several different ways in order to achieve optimal performance. And DAX Studio includes several key features that help with this task.

Clear Cache

One of the initial and most basic optimization checks when writing DAX (or MDX for that matter) is measuring cold-cache performance… how fast does the query or calculation perform when it has to get all of the data from raw storage.

First of all, there’s no way to test query or calculation performance from within SSDT (the tool used develop tabular models)… which means you’re going to have to build the model SSDT and test calculation and/or query performance using another tool like SSMS (which, btw, doesn’t even have a DAX editor!). So in order to test cold-cache query performance, you’ll need two windows: one for the query and the other for the clear cache command. Did I mention already that SSMS doesn’t have a DAX editor?

With DAX Studio, there’s a built in button, right on the main ribbon tab, that allows you to clear the cache of the tabular database you’re connected to:

…and an output tab which will tell you when the cache was cleared…

And if that wasn’t easy enough, you can even set the default “Run” option to clear the cache before running the query everytime!

Timings

Another common DAX optimization check is the Formula Engine (FE) vs Storage Engine (SE) ratio. Knowing whether a slow query is spending most of its time in the FE (or the SE) is your first clue on where to focus your optimization efforts. And DAX Studio makes this information quickly and easily available!

All we have to do is click/enable the timings button and we get a new tab in the bottom half of the screen that not only gives us the FE vs SE ratio, but also tells us how many storage engine queries were generated by the DAX statement along with the duration of each as well as the total query duration.

This is by far one of the most helpful views in the tool. In order to get this same information without DAX Studio, you would have to setup a profiler/extended events trace against the SSAS tabular instance, run the query in SSMS, and analyze the trace results after loading them into a SQL table…every single time!

Wrapping Up

As you can see, DAX Studio is an incredible tool that can save DAX developers a ton of time… and, in terms of features, we’ve only just scratched the surface. There’s query plans, query history, function references, dynamic management view references and much much more.

If you want to get started on Tabular Models with the help of experts, read more over at Fix Your SQL Server.

Reference: Pinal Dave (https://blog.sqlauthority.com)