DAX Studio — a faster way to DAX

Visual Studio has long been one of those IDE’s I’ve particularly loved to hate. I’ve consistently considered it as basking comfortably in the “way too much” side of the spectrum of feature-rich IDE’s. I think it’s way too powerful, boasts too many features, and in many cases, way too slow.

I’ve had to use it lately for some Business Intelligence modelling work I was doing with Analysis Server. We have a project that uses ETL to bring data into the data warehouse, then into the Tabular Model. The Tabular Model is mostly an in-memory version of your standard database, but its optimized to run analysis type queries. Queries written against the Tabular Model are written in Microsoft’s Data Analysis Expression (DAX) language. My role on the project is to write queries in DAX to support the modeling we want to perform. Our end users interact with these queries using Excel’s PowerPivot or Dashboards other teammates build in PowerBI.

Microsoft built a Visual Studio Extension called SQL Server Data Tools — Business Intelligence (SSDT-BI) to allow us to write DAX queries and perform other management operations for the Tabular Model. Unfortunately, I’ve found the extension to be quite painful to use.

The DAX Editor is really wonky. The cursor never moves smoothly, when trying to scroll with the keyboard. Selecting text often results in some unpredictable surprises, bracket matching / highlighting is down right weird, autocomplete gets in the way. I got to my breaking point when I realized each save of the DAX query meant having to wait 10–30 seconds each time (despite telling it not to recalculate the model each time). During this time, the UI would block completely 😦 … threads anyone?

End rant… Enter DAX Studio.

DAX Studio is a beautifully built piece of software. It provides a stellar interface for writing DAX queries and provides some much needed profiling tools for understanding slow queries. I’ve only been using it for one night and I’ve gained so much momentum using it. I started out reading this great Getting Started guide. It tells you how to install it, the main feature set, and how to write queries in DAX.

Once you’ve got it installed, the first thing you want to do is connect to your Tabular Model.

Once you’ve connected, you will have a DAX window where you can write your queries.

There’s one main principle that’s required to start writing DAX expressions in DAX Studio.

The simple rule is that every DAX expression you write must evaluate to a table. Normally, in SSDT-BI, we would author a measure that provides some scalar value. We’ll need to do a little extra in DAX Studio to examine that value.

Let’s start with a basic example.

If you just wanted to list all the data in a table called Product. You could simply do this:

EVALUATE ALL(Product)

Then you can execute the query using F5, or hitting Run. So similarly, if you wanted to do a filter…

EVALUATE FILTER(Product, Product[Price] > 2000)

Now suppose you wanted to view the output of a scalar… something that doesn’t produce a table like the examples above. Well, DAX has a ROW function that creates a table with a single row. Let’s say you wanted to show the current date using NOW.

EVALUATE ROW("The Date", NOW())

This would produce a table with just one row titled “The Date” and the value of NOW.

We can extend on this example to show how we would transfer measure code we’re writing in Visual Studio to DAX.

Let’s say we had the following in Visual Studio.

Number of Products := SUM(VALUES(Product[ProductId])

We would just need to copy the actual expression to DAX Studio to evaluate it. We can again use ROW to show us the value.

EVALUATE ROW("Number of Products", SUM(VALUES(Product[ProductId])))

It’s a great tool to use to build up expressions slowly and check the output of them. I also found it handy to create my own “pivot-table”-esque views. I used a SUMMARIZE query for instance to test out my measure. The example looks something like this…

EVALUATE SUMMARIZE(<Table>, <Group By Column>, "Testing", <Test Measure>)

Now DAX Studio of course isn’t perfect and I’ve already found a few things that weren’t deal breakers for me.

• It doesn’t know much about your *.bim file. The bim file stores the definition of your models, partitions, calculated tables, etc… This means that you have to copy and paste new measures that you are working on. This also means no KPI creation or adding formatting to your measures.
• It connects quite easily to an instance of Analysis Server, but I haven’t tried it with Visual Studio’s workspace Server. I vaguely recall using Visual Studio where there was a temporary Tabular model database created. You will need to verify that you can connect to that temp workspace. If you can’t connect to your temporary workspace that means any edits you make there won’t be visible in DAX Studio.
• The EVALUATE workflow took a little while to get used but once you get the hang of it, it gives you much greater momentum that working in Visual Studio.

For now, I’m using DAX Studio as my authoring environment going forward. Given I’m pretty new to DAX, SSDT-BI really slowed my pace and discouraged experimentation. DAX Studio is well built and doesn’t freeze up when a long query is being run. IMO, it’s much more robust than SSDT-BI and it’s gotten more love and updates too.

Let me know if this works for you or what your workflow looks like with SSDT-BI.