THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

SQLBI - Marco Russo

Please visit the same blog at https://www.sqlbi.com/blog/marco/ - comments are now disabled here but you can comment on the new blog (all blog posts available here are available there, too).
You can follow me on Twitter: @marcorus

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!

Published Tuesday, March 17, 2015 2:09 PM by Marco Russo (SQLBI)

Comments

 

Davide Mauri said:

Great job mate!!!!!

March 18, 2015 5:50 PM
 

Marco Russo (SQLBI) said:

Thanks!

March 19, 2015 5:37 AM
 

Juan V. said:

Hi,

I have a question about the sql server business intelligence solution (2012 version will be OK)

I want to asses this solution, SSIS (for ETL processes), SSAS (for cube designing)

and SSRS (for resporting), I want to know:

- Is there any trial version of the suite?

- Is there any free version with less funcionality?

- Is there any cloud solution?, paying for used time?

Thanks, any advice, will be greatly apreciated.

For me is OK 2012 version.

March 20, 2015 6:26 PM
 

Marco Russo (SQLBI) said:

You need SQL Standard to use SSAS Multidimensional, or SQL Business Intelligence edition to use SSAS Tabular. You can use the VM provided by Microsoft on SQL Azure to pay per time (create a VM with SQL Server 2012). You can download trial edition of SQL Server and use SQL Server Developer edition for internal Development (it has all the Enterprise feature, but it cannot be used in production).

You can download trial here:

http://www.microsoft.com/en-us/evalcenter/evaluate-sql-server-2014

March 21, 2015 3:52 AM
 

Darren said:

Sorry for such a basic question but as a follow up to the above can you use DAX to query SSAS Multidimensional on SQL Server Standard?

March 25, 2015 6:19 AM
 

Marco Russo (SQLBI) said:

Hi Darren, it is not possible, you need Business Intelligence or Enterprise edition in order to use DAX over SSAS Multidimensional. It is not supported over a standard edition.

April 1, 2015 5:52 AM
New Comments to this post are disabled

About Marco Russo (SQLBI)

Marco Russo is a consultant, writer and trainer specialized in Business Intelligence with Microsoft technologies. He runs the SQLBI.COM website, which is dedicated to distribute resources useful for BI developers, like Integration Services components, Analysis Services models, tools, technical information and so on. Marco is certified as MCT, MCDBA, MCSD.NET, MCSA, MCSE+I.

This Blog

Syndication

Archives

Privacy Statement