OLAP Reporting with Excel 2007: Use Member Properties!!
Excel 2007 is great tool for browsing OLAP cubes. But 1 thing a lot of people don’t know about or at least don’t use enough, is Member Properties.
When they want to add a property of a specific attribute, they just check this item in the field list. By using it this way, you’re kind of building hierarchies between all the selected attributes, therefore all possible relationships must be checked. This of course has a huge performance impact.
There is an easier and better way that also makes loading the data a lot faster: Member Properties. The relationships between Members and their Properties is defined by specifying Attribute Relationships in Analysis Services.
Ever saw that Tooltip in Excel when you’re hovering your mouse over a field in the Pivot Table? This Tooltip shows all available Member Properties.
But you can also display them on columns:
Click right on the field where you would like to display one or more properties for. Under “Show Properties in Report”, select the properties you would like to show.
By using Member Properties the layout will be better and your report will load a lot faster!