Applies to SSAS 2008 and Excel 2007.
Also see this blog entry: OLAP Reporting with Excel 2007: Use Member Properties!! at http://blog.davyknuysen.be/2009/08/03/olap-reporting-with-excel-2007-use-member-properties/
As you add columns from a SSAS cube to pivot table in Excel it will progressively get slower to the point that it is virtually unresponsive. In order to understand exactly why this occurs we need to look at how Excel builds its MDX. Let’s take a look at a simple pivot table against AdventureWorks.
So what is the underlying MDX that is executed to get this result?
SELECT NON EMPTY
CrossJoin(
CrossJoin(
Hierarchize({DrilldownLevel({[Product].[Product].[All Products]})})
, Hierarchize({DrilldownLevel({[Product].[Color].[All Products]})}))
, Hierarchize({DrilldownLevel({[Product].[Size].[All Products]})}))
DIMENSION PROPERTIES PARENT_UNIQUE_NAME
,[Product].[Product].[Product].[Class]
,[Product].[Product].[Product].[Color]
,[Product].[Product].[Product].[Days to Manufacture]
,[Product].[Product].[Product].[Dealer Price]
,[Product].[Product].[Product].[End Date]
,[Product].[Product].[Product].[Large Photo]
,[Product].[Product].[Product].[List Price]
,[Product].[Product].[Product].[Model Name]
,[Product].[Product].[Product].[Reorder Point]
,[Product].[Product].[Product].[Safety Stock Level]
,[Product].[Product].[Product].[Size]
,[Product].[Product].[Product].[Size Range]
,[Product].[Product].[Product].[Standard Cost]
,[Product].[Product].[Product].[Start Date]
,[Product].[Product].[Product].[Status]
,[Product].[Product].[Product].[Style]
,[Product].[Product].[Product].[Subcategory]
,[Product].[Product].[Product].[Weight] ON COLUMNS
FROM [Adventure Works] WHERE ([Measures].[Order Count])
CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS
Notice that there are two CrossJoin function calls in the MDX which build a set of 3 Hierarchize function call for each of the selected dimension attributes. The CrossJoin function performs a cross product of two set and the sets are the results of Hierarchize function which organizes the members of the set in hierarchical order. As you can see the number of member of the set returned can grow exponentially as more and more columns are added to the pivot table. As you add a column to the excel spreadsheet Excel will add another CrossJoin and Hierarchize function the MDX. For example the adding the Size Range attribute leads to the following query:
SELECT NON EMPTY
CrossJoin(
CrossJoin(
CrossJoin(
Hierarchize({DrilldownLevel({[Product].[Product].[All Products]})})
, Hierarchize({DrilldownLevel({[Product].[Color].[All Products]})}))
, Hierarchize({DrilldownLevel({[Product].[Size].[All Products]})}))
, Hierarchize({DrilldownLevel({[Product].[Size Range].[All Products]})}))
DIMENSION PROPERTIES PARENT_UNIQUE_NAME
,[Product].[Product].[Product].[Class]
,[Product].[Product].[Product].[Color]
,[Product].[Product].[Product].[Days to Manufacture]
,[Product].[Product].[Product].[Dealer Price]
,[Product].[Product].[Product].[End Date]
,[Product].[Product].[Product].[Large Photo]
,[Product].[Product].[Product].[List Price]
,[Product].[Product].[Product].[Model Name]
,[Product].[Product].[Product].[Reorder Point]
,[Product].[Product].[Product].[Safety Stock Level]
,[Product].[Product].[Product].[Size],[Product].[Product].[Product].[Size Range]
,[Product].[Product].[Product].[Standard Cost]
,[Product].[Product].[Product].[Start Date]
,[Product].[Product].[Product].[Status]
,[Product].[Product].[Product].[Style]
,[Product].[Product].[Product].[Subcategory]
,[Product].[Product].[Product].[Weight] ON COLUMNS
FROM [Adventure Works] WHERE ([Measures].[Order Count])
CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS
So you can see all these cross products quickly get out of hand and performance suffers. Since we cannot control how Excel builds the MDX how can we help this.
There are two options that I am going to bring up here. First we could just add a unnatural hierarchy to the dimension which consist of Product, Color, Size and Size Range and this will materialize the cross join in the cube so that performance will be much better but this is really impractical since there is virtually unlimited number of combinations that would have to be handled. Another approach is to use another less well know feature of Excel and that is Show Properties in Report.
If you notice in our example [Product].[Product] is the primary key of the dimension so the measure is unaffected by the additional columns in pivot table. A product can only have 1 color, size and size range. We are at the grain of the dimension once we have put Product in the pivot table.
In order to have Excel not CrossJoin the additional attributes we do the following:
1. Remove the Color and Size attribute from the pivot table.
2. Select them to show in the report using the Show Properties in Report field menu item.
Now we have the same data in Excel and at virtual no extra cost we can add any number of additional attributes to the pivot table.
The MDX generated looks like this:
SELECT NON EMPTY
Hierarchize({DrilldownLevel({[Product].[Product].[All Products]})})
DIMENSION PROPERTIES PARENT_UNIQUE_NAME
,[Product].[Product].[Product].[Class]
,[Product].[Product].[Product].[Color]
,[Product].[Product].[Product].[Days to Manufacture]
,[Product].[Product].[Product].[Dealer Price]
,[Product].[Product].[Product].[End Date]
,[Product].[Product].[Product].[Large Photo]
,[Product].[Product].[Product].[List Price]
,[Product].[Product].[Product].[Model Name]
,[Product].[Product].[Product].[Reorder Point]
,[Product].[Product].[Product].[Safety Stock Level]
,[Product].[Product].[Product].[Size],[Product].[Product].[Product].[Size Range]
,[Product].[Product].[Product].[Standard Cost]
,[Product].[Product].[Product].[Start Date]
,[Product].[Product].[Product].[Status]
,[Product].[Product].[Product].[Style]
,[Product].[Product].[Product].[Subcategory]
,[Product].[Product].[Product].[Weight] ON COLUMNS
FROM [Adventure Works] WHERE ([Measures].[Order Count])
CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS
Notice there is no longer any CrossJoin at all and we have virtual the same data in Excel.
If you want to control the order of the properties that appear in Excel you can use the Field Properties tool in the pivot table ribbon.
You can really see the difference in performance if you just put all the dimension attributes as columns in the pivot table and compare its performance with the performance when you Show all the Properties in the Report.
Conclusion
Using Show Properties in Report can provide much better performance but it is not always possible because the attributes you are looking for are not properties of a column you are showing but it is just one more way to get better performance out of Excel.
In general once you pull in the key attribute of the dimension you should use Show Properties in Report for all other attributes from that dimension.