It’s really hard for me when Excel works very slowly when interacting with a pivot table. Adding / removing a field, changing a filter or slicer completes Excel work in a few minutes.
It seems that the generated MDX is extremely inefficient. I can understand that they should generate MDX dynamically and should support many pivot table functions, but 100 times slower is funny.
When they generate MDX for a field in a row or column, they use DrilldownLevel (... [Property Dimension]. [County])
I'm not sure if Excel's goal is a more complicated approach, but I hope there are some options that I can remove so that Excel doesn'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.
Querying for one result set takes 5 minutes with Excel MDX and takes less than 5 seconds with my MDX.
I checked that slowness is not a problem when rendering / formatting Excel results, as I took the MDX used by Excel and ran it directly in SSMS to check the timings. I can view the task manager on the server and see how the processor is pumping out while it processes the results.
Note that I am not blaming the server, as I can create MDX queries that run very fast and give the same results.
How can I get Excel to create a more efficient MDX? I am using Excel 2010.
I heard that powerpivot generates a more efficient MDX, however Powerpivot is not used on top of SSAS, since it does not use the SSAS cube. So a short rant about why Powerpivot on top of SSAS doesn't work. If you are importing data from SSAS into powerpivot, then all you do is a giant crossover to transfer data from SSAS to a Powerpivot table. If you tried this, you will find that it generates field names / labels such as "Property DimensionCountyCounty Name" ... really? Then you simply work with the data using the local PowerPivot OLAP mechanism and, therefore, depends on the client machine with a 64-bit OS to work with a reasonable size data set. It’s as if you are simply cutting out SSAS, doing away with all your hard work of creating a complex OLAP database and all metadata, calculations, aggregates, etc. Half of the reasons for using SSAS is that it can summarize the details before it is returned to the client, so that the client does not need a 64-bit OS and does not need a huge amount of resources on the client. I tried very hard to make powerpivot versus SSAS usable, but having tried several approaches back and forth with users, it really wasn't that close to being usable. Do not get Powerpivot down because I see its usefulness in many other scenarios, but if your SSAS cube is an important part of your system (e.g. computing, aggregating large volumes of records on the server side, etc.), then powerpivot seems like 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 an example of Excel. In fact, I have already deleted several other aspects of the Excel query, trying to simplify it to determine who was the culprit. It was like a request when it was still running slowly, and then the next step I took was that I removed DrilldownLevel and replaced. [All] p. [Attribute Name] that it started to work much faster.
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 is DrilldownLevel (... [All]) needed? Is there an option where I can flip so that Excel does not generate this part of the query, so that it works faster?