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.

tooltip

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.

show properties

By using Member Properties the layout will be better and your report will load a lot faster!

Using Attributes:

with attributes

Using Properties:

with properties

Share
  1. Sep 18th, 2009 at 17:35 | #1

    Is it possible to do the same with report filters? I have a hierachy which is used as a filter – the name that makes sense to the user is in a member property

  2. Sep 19th, 2009 at 00:11 | #2

    No, you can’t. Properties can’t be used as a filter or in a hierarchy.

  3. Hemant
    Apr 7th, 2010 at 19:42 | #3

    How can we get this Member Property displayed by default ?
    What I mean is when we drop and dimension in to the Excel Row, along with dimenison, it should put the member property. Is there any way to do it

  4. Apr 12th, 2010 at 08:12 | #4

    The only way to make sure the user always drops the property, is to disable the attribute: AttributeHierarchyEnabled = false.
    But this also means the attribute is only visible as a property.

Leave a comment

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>