THE SQL Server Blog Spot on the Web

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

SQLBI - Marco Russo

Please visit the same blog at - 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.2 released - tracing support for #powerpivot and much else! #dax

Thanks to Darren Gosbell we have DAX Studio 2.2. In this new release:

  • Tracing (query plans and server timings) available for Power Pivot
  • Basic implementation of Intellisense
  • Connect to Multidimensional SSAS servers
  • Support for multiple Power BI Desginer instances
  • Highlight unnatural hierarchies (read here why this is so important for performance)

 A more complete description of the new features is available in the Darren's post.

I think that this release is a revolution for Power Pivot users. Until now, you had to use Analysis Services to restore a Power Pivot model and then run your query using DAX Studio to analyze performance. Now you don't need anything else other than Excel. This is amazing.

If you are wondering about how to use this feature, simply follow these steps:

  1. Create a pivot table that generates a performance issue
  2. Capture the MDX query using OLAP PivotTable Extensions using its "View PivotTable MDX" feature
  3. Open DAX Studio from the Excel AddIn ribbon
  4. Copy the MDX query in DAX Studio
  5. Enable Query Plans and Server Timings buttons
  6. Run the query

That's it. At this point, you can improve your productivity by copying the code of your DAX measure at the beginning of the MDX query.

For example, if you have this MDX query from your PivotTable (look at Sales Amount measure):

{ [Measures].[Sales Amount], [Measures].[Sales Rows] } DIMENSION PROPERTIES PARENT_UNIQUE_NAME
, NON EMPTY Hierarchize (
  DrilldownLevel (
   { [Date].[Calendar].[All] }

 You just have to add these lines *before* your MDX statement

WITH MEASURE 'Sales'[Sales Amount] = SUMX ( Sales, Sales[Quantity] * Sales[Unit Price] )

The table name (Sales in this case) should correspond to a table of your model, use the table where you defined the measure originally. Now your definition of Sales Amount overrides the one of the data model in this query and you can easily change the following DAX code of the measure definition and test the entire query again (maybe clearing the cache before) until you obtain a better version. Then, simply copy the code & past it into your Power Pivot model, replacing the previous definition of the same measure.

You will see that this is way more productive than changing the code in Power Pivot and refreshing the pivot table every time! 


Published Wednesday, June 24, 2015 2:51 PM by Marco Russo (SQLBI)
Filed under: , , , ,


No Comments
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



Privacy Statement