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):

SELECT
{ [Measures].[Sales Amount], [Measures].[Sales Rows] } DIMENSION PROPERTIES PARENT_UNIQUE_NAME
, MEMBER_VALUE
, HIERARCHY_UNIQUE_NAME ON COLUMNS
, NON EMPTY Hierarchize (
{
DrilldownLevel (
{ [Date].[Calendar].[All] }
,
,
, INCLUDE_CALC_MEMBERS
)
}
) DIMENSION PROPERTIES PARENT_UNIQUE_NAME
, MEMBER_VALUE
, HIERARCHY_UNIQUE_NAME ON ROWS
FROM [Model] CELL PROPERTIES VALUE
, FORMAT_STRING
, LANGUAGE
, BACK_COLOR
, FORE_COLOR
, FONT_FLAGS

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!