Slow Excel pivot table MDX?

Question

I am having an extremely hard time with Excel being very slow whenever interacting with a pivot table. Adding/removing a field, changing a filter or slicer, all take several minutes of Excel being frozen before responding.

It appears that the MDX being generated is extremely inefficient. I can appreciate that they have to generate the MDX dynamically and must support many features of the pivot tables, but being 100 times slower is ridiculous.

When they generate MDX for a field on a row or column, they use DrilldownLevel(... [Property Dimension].[County])

I'm not sure what the purpose of Excel's more complicated approach is, but I am hoping there are some options somerwhere that I can uncheck so that Excel doens't need to use the DrilldownLevel function.

Instead, I usually omit the Drilldownlevel function and just do [Property Dimension].[County].[County] to access the attribute.

A query for the same result set takes 5 minutes with Excel's MDX and takes less than 5 seconds with my MDX.

I have verified that the slowness is not a problem with Excel rendering/formatting the results, as I took the MDX used by Excel and ran it directly in SSMS to verify the timings. I can view the task manager on the server and watch the CPU churning away while it processes the results.

Note, I am not blaming the server since I can create MDX queries that run extremely fast and provide the same results.

How can I get Excel to generate more efficient MDX? I am using Excel 2010.

I have heard that powerpivot generates more efficient MDX, however Powerpivot is not usable on top of SSAS, as it doesn't leverage the SSAS cube. So a short rant on why Powerpivot on top of SSAS doesn't work. If you import data from SSAS into powerpivot all you are essentially doing is performing a giant crossjoin to migrate the data from SSAS into a Powerpivot table. If you have tried this, you find it generates field names/labels such as "Property DimensionCountyCounty Name"... wow really? You are then just working with the data using the local Powerpivot's OLAP engine, and thus depends on the client machine having 64bit OS in order to work with a reasonably sized data set. It's as if you are just cutting out SSAS, throwing out all your hard work on building a sophisticated OLAP database and all of the meta data, calculations, aggregations, etc. Half the reason for using SSAS is so that it can summarize the granular data before it is returned to the client, so that the client doesn't need a 64 bit OS, and doesn't need a huge amount of resources on the client. I tried really hard to make powerpivot against SSAS useable, but after trying several approaches and back and forth with users, it really was no where close to being usable. Not to knock Powerpivot, as I see it's usefulness in many other scenarios, but if your SSAS cube is an important part of your system(i.e. calculations, aggregating large amounts of records on the server side, etc.) then powerpivot seems the wrong option.

Here is an example of my query:

SELECT 
NON EMPTY CrossJoin(
{[Department Dimension].[Name].[Name]},
 {[Finance Month].[Report Year].[Report Year]}
 )
  ON COLUMNS , 
 CrossJoin(
   {[Department Finance Line Type Dimension].[Display Order].[Display Order] }, 
   {[Department Finance Line Type Dimension].[Line Number].[Line Number]},
   {[Department Finance Line Type Dimension].[Display Name].[Display Name]}
   ) 
   ON ROWS  
   FROM 
   (
   SELECT ({[Department Dimension].[County].&[Seminole],[Department Dimension].[County].&[Sarasota]}) ON COLUMNS  FROM [HYP Data View]
   ) 
   WHERE ([Department Finance Line Type Dimension].[Section Name].&[Part 1 - Balance Sheet],
   [Measures].[Amount]
   ) CELL PROPERTIES VALUE

And below is what Excel generated. I actually had already removed several other aspects of the Excel query as I attempted to simplify it to determine what was the culprit. This was what the query looked like when it still ran slow, and then the next step I took was when I removed the DrilldownLevel and replaced .[All] with .[Attribute Name] that it began running much much faster.

Very very slow query:

SELECT 
NON EMPTY CrossJoin(
{DrilldownLevel({[Department Dimension].[Name].[All]})},
 {DrilldownLevel({[Finance Month].[Report Year].[All]})}
 )
 DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS , 
 CrossJoin(
   {DrilldownLevel({[Department Finance Line Type Dimension].[Display Order].[All] })}, 
   {DrilldownLevel({[Department Finance Line Type Dimension].[Line Number].[All]})},
   {DrilldownLevel({[Department Finance Line Type Dimension].[Display Name].[All]})}
   ) 
   DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON ROWS  
   FROM (
   SELECT ({[Department Dimension].[County].&[Seminole],[Department Dimension].[County].&[Sarasota]}) ON COLUMNS  FROM [Afr Data View]
   ) 
   WHERE ([Department Finance Line Type Dimension].[Section Name].&[Part 1 - Balance Sheet],
   [Measures].[Amount]
   ) CELL PROPERTIES VALUE

Why does it need the DrilldownLevel(...[All])? Is there an option somewhere I can flip to get Excel to not generate that part of the query so that it will run faster?

Answer 1

Unfortunately though I have worked with Excel and Pivottables on SSAS MOLAP Cubes quite extensively I have not yet found a mechanism in pure Excel to alter the queries it generates.

I have however used a 3rd party tool called XLCubed which is quite powerful when working with Excel and MDX queries from SSAS OLAP Cubes, may I suggest you give it a try for your purpose? You can even replace the MDX it will use for a "Grid" with totally customized MDX as you have above. Though I find this is not often necessary their replacement for pivot tables "Grid" seems to generate far more efficient MDX in general.

May the SSAS Gods be with you.