DAX Studio Review

One of the enjoyable and privilege components of my work is discovering new things and learning new things, especially in becoming more productive. DAX Studio is a great example of this.

DAX Studio is a result of great hard work and dedication from BI experts; Paul te Braak, Marco Russo, Darren Gosbell and Angry Koala. The product can be downloaded from Codeplex. The version in this review is 1.0.1.

This article is a review of DAX Studio based on my experience at work. It does not encompass all usage scenarios under the sun for DAX, however it should provide some insights on the pros and cons of the product.

DAX Studio is an Add-In

DAX Studio is an add-in for Excel 2010 (I believe the product would also work for Excel 2013 as well, but I am yet to test this). After installing DAX Studio, you will see that the button becomes available on the Add-In tab

Upon clicking on the DAX Studio, a dialog box would appear, requesting for connection to either an existing PowerPivot (default) or a tabular model instance.

Connect To detects no PowerPivot model

It is quite interesting that when we connect to the PowerPivot model linked to the workbook, the connection will say “Microsoft_SQLServer_AnalysisServ…”. Unfortunately the name is cut off from the drop down box and it’s not resizeable. Similarly it is not easy to pick a tabular database from the drop down list when they have long name with the same first few characters as shown below.

Perspective is selectable from the dropdown list on the right near where the Server Name drop down box.

Similar to SSMS, the following information information is shown on the bottom left of DAX Studio window:

• Server Name ($Embeded$ when connecting to a Power Pivot model)
• SPID
• Version

DAX Studio Shines

DAX Studio allows you to browse through the objects that you have defined on the model. The calculated measures are shown under the table that it is created, not grouped separately like in SSMS under “measures”. In the version that I am evaluating, the calculated measures are listed based on the creation time/date. This could be a good thing for some users. Unfortunately, in my case, I am implementing over a hundred of calculated measures (it’s for a specific Accounting report, hence lots of measures) within my model, making it harder to find the measure I want sometimes. But at the same time, when I’m still debugging the newest calculated measure, it’s really good as I can go to the bottom of the list and pick the last calculated measure that I want to test.

I also find that the ability to see the hidden fields or tables in DAX Studio is very powerful. Quite often, my query requires those hidden column or tables as part of calculation. In addition, it makes life so much easier in debugging what the hidden columns that were used as part of the calculation.

Based on my experience when using DAX Studio, there are two major aspects that I find very useful.

1. Performance of the DAX Studio when being used for querying

More often than not, I have a few pivot tables within one workbook. When I only need to inspect / debut a calculated measure, I wouldn’t want to refresh the whole pivot tables. So, with DAX Studio, I am able to perform a smaller set of unit testing independently from the rest of my workbook. I find that it’s a lot quicker for me to refresh the EVALUATE statement in DAX Studio than for the PowerPivot to refresh the data.

2. Analysing performance of queries

As part of unit testing, I am often is concerned with how long my query runs. I can go to the DMV tab and perform drag and drop on useful DMVs like:

select * from $SYSTEM.DISCOVER_PERFORMANCE_COUNTERS

In addition to being able to see the different types of performance counter, DAX Studio also returns a message about errors and how long the query takes.

Let’s take a look at the Output generated by DAX Studio. The actions that can run on DAX Studio are:

1. Output query to Excel as a Table
2. Output query to Excel as a static data
3. Output query to Grid dialog box
4. Evaluate only (no output)
5. Clear Cache

There are also options on creating new sheet or overwriting existing sheet when Output query to Excel is chosen.

There is support for line numbers, saving the query and – most of all – syntax highlighting.

Where to from here?

As a BI Consultant working with Tabular Model and PowerPivot solutions on daily basis, I think DAX Studio is a superb companion. It helps in increasing developers productivity.

I think the product can be improved further. Here are some small areas of improvement which would be helpful (I’m talking based on my experience – if you share my view on areas of improvement, please vote or comment on the codeplex comment link).

1. More support for SSMS/VS short cut keys, e.g. changing case to upper case or lower case, Ctrl+K Ctrl+C to comment things

2. Keeping the column format as specified in the model. Even though the calculated measure is formatted as shown below, the output is not retaining the format.

Internet Total Sales format is Currency

The workaround for this is to use the “FORMAT” function in the DAX query, but this is not ideal.

3. Ability to sort the calculated measures – at the moment it’s in the order they’re created, which might not be the best.

4. Providing a warning dialog box – “You have unsaved query. Are you sure you wish to exit DAX Studio?”. I have been caught a few times accidentally closing DAX Studio instead of the Grid dialog box and lost some changes 🙁

5. Displaying DAX query plan

6. Adding integration to source control

7. Resizeable Database Name Dropdown Box.

Wrap Up

I have been using DAX Studio for over a month now and am very delighted that such product exists. This really helps in the development life cycle of Tabular Model and PowerPivot solutions. Is it overkill, given SSMS is available? No, querying PowerPivot via SSMS is not an option (not that I am aware of). Would there be more support for the product? Yes, I believe so. I would highly recommend downloading, using the product and providing feedback. The project is built and coordinated by BI experts in the world who are passionate and actively involved in implementing Tabular Model and PowerPivot solutions.

DAX Studio is a great tool for developing DAX query, unit testing and querying performance statistics. It is easy to use and can definitely help.

If you have any comments and feedback on DAX Studio, please post them on the Codeplex project so it can be tracked. I’m also happy to hear thoughts around the product or a similar one.

Further Reading

Using DAX studio to create PowerPivot measures by Javier Gullien

Introduction to DAX Studio by Paul Te Braak

DAX Studio 1.0 beta available #dax #tabular #powerpivot by Marco Russo

Gantt Reworked with ADDCOLUMNS, FILTER, GENERATE and SUMMARIZE by David Churchward for PowerPivot Pro