DAX Studio 2.1 helps you optimizing #dax queries for #powerpivot #powerbi #ssas #tabular

After the initial release of DAX Studio 2.0, in December 2014, I started working on a few features to analyze performance of DAX queries. Darren Gosbell implemented many other features, and Daniele Perilli helped us with the graphics of the UI. We certainly can do more, but I think that the current feature set of the new release is good enough to use DAX Studio only during your daily job with DAX, without opening SQL Server Management Studio (SSMS) and SQL Profiler anymore.

For this reason, I’m so proud to announce the release of DAX Studio 2.1, just after Darren Gosbell also officially announcement on his blog.

I created a very short video (less than 3 minutes) to show the new features for analyzing performance of DAX queries with DAX Studio 2.1.

Here is the list of the new features:

  • Improved Server Timings Tab
    • Show storage engine query events
    • Sort events by CPU and Duration
    • Filter events by type (Cache, Internal)
    • Show formula engine and storage engine timings
    • Different layouts for complete display of storage engine query text
    • Cleanup of storage engine query text
  • Improved Query Plan Tab
    • Display of physical query plan in a list that highlights number of records processed
    • Separation of physical query plan and logical query plan in two different lists
  • Save query plan and server timings with DAX query
    • When you save a DAX file, if you enabled Server Timings and Query Plan panes, two other files are created with the same name and a different extensions (.dax.queryPlans and .da.serverTimings).
    • When you load a DAX file, if the other two files exist in the same directory, they are loaded and the Server Timings and Query Plan panes display these information
    • This feature is useful to get useful information from a remote user asking help without having to connect remotely or to download the entire database.
  • Metadata Search: find measure, column, and table names in the entire metadata tree
  • Query text search & replace
  • Integration with DAX Formatter for automatic query layout (plus static syntax check without metadata)
  • Accept MDX queries
    • You can copy a query from a pivot table in Excel using OLAP PivotTable Extensions and run it in DAX Studio
    • By using DEFINE MEASURE at the beginning of the query, you can define new DAX measures and see the effects in the same MDX query
    • Most important, you can see the profiler events for executing DAX measures in a MDX query, which is very important for performance tuning
  • Connect to Power BI Designer
    • Just open Power BI Designer and then open DAX Studio
    • You can choose to connect to Power BI Designer
    • At the moment, we don’t support multiple instances of Power BI Designer and if you close Power BI Designer, you lose the connection without any warning.

There are also many other bug fixes and small improvements. In this release, we still didn’t solved an issue we have in getting trace events when connected to Power Pivot, but debugging work nicely when using Power BI Designer. Yes, you read it correctly!

In the last weeks of beta testing, I never used SQL Server Management Studio anymore to debug DAX measures and queries. In particular, I no longer open the SQL Profiler, unless I want to capture a query generated by other programs (this is a feature we might integrate in DAX Studio in the future, sniffing all profiler events and debug queries run by other processes).

Remember: DAX Studio is free and open source. If you want to contribute, post and vote bugs and feature requests on CodePlex. If you are also a .NET Developer, join us writing other features. There have been more than 2500 downloads in 3 months since 2.0 release. It’s a good number, but I expect to reach such a number much sooner with this release. Spread the word!