Filtering a Pivot Table by a Large Dimension

Question

I have a very large dimension in my OLAP cube - a customer dimension. Each customer has her own unique ID. I require a pivot table to be filtered for a single specific customer (ID). Unfortunately Excel Pivot table takes very long to display the appropriate customer ID (checkbox) in the report filter. It also does not help to pre-select customer from a specific town or birth year - it seems that all customers are scanned anyway.

How can I speed up this process? Is there maybe a specific setting/property that I can apply in the SSAS solution? Would attribute relationships help? Can I maybe pre-calculate something in the cube? Maybe I should add an additional attribute to the dimension?

Answer 1

Try Olap Pivot Table extensions for Excel. You can insert a filter list without having to wait for the costly excel filter.

Answer 2

You could add an attribute hierarchy to the customer dimension ItIsJoeBloggs. In the customer dimension table this would be True for the JoeBloggs rows and False for all others.

Then in Excel this hierarchy could be an additional filter with True selected.