Defining Member Properties for an Attribute in SSAS 2008

I was working with a client the other day designing a couple cubes. These cubes used two pretty large dimensions, each containing many attributes. In order to increase the performance of these large dimensions, I worked with the client to identify attributes that the end users would not necessarily need to slice and dice with, but would still be useful to view the attributes. Once those attributes were identified, I displayed them as what was known as members properties. So lets walk through how we can accomplish this.

Here’s the Customer dimension in the Adventure Works 2008 R2. As you can see, there’s a whole lot of attributes, many of which probably would provide little value by allowing the end users to slice with.

Capture1

Attributes with high cardinality are prime candidates to become a member property. A good example would be AddressLine1.

The first thing we need to do is set the AttributeHierarchyEnabled property to False for the AddressLine1 attribute.

Capture2

This will prevent end users from slicing with the attribute, reducing storage requirements and increasing performance.

To allow the users to view this attribute as a member property, we need to create an attribute relationship between AddressLine1 and the attribute we would like to view AddressLine1 from. By default, all attributes are related to the key column in the dimension. But I would like our end users to be able to view AddressLine1 when the hover over the Name field of our customer.

To accomplish this, go to the Attribute Relationships tab in the dimension designer. In my Customer dimension, I’ve got a calculated column called FullName but you could use FirstName for this example. Right-click the FirstName and click New Attribute Relationship. Then in the Create Attribute Relationship window, find Address Line1 in the Related Attribute drop down list. Click OK.

Capture4

After I processed the cube and connected to the AS database in Excel, I can now view the member properties for the Name attribute. Right-click the Name of a customer in your pivot table, click Show Properties in Tooltips, and click Show All Properties or select the individual attribute you’d like to view as a tooltip.

Capture6

Now when I hover over the name of a customer, I see the properties of that customer. In the Adventure Works cube, you can see how many other attributes have been created as member properties of the Name attribute.

Capture5

Alternatively, you can choose to display the member properties as a column in the pivot table by right-clicking the name of a customer, clicking show properties in report, and then select the field to display in the report.

Capture7

This is a great way to increase performance of large, unwieldy dimensions and still give the end user their wildest dreams Winking smile.

Advertisements

2 thoughts on “Defining Member Properties for an Attribute in SSAS 2008”

  1. Excellent idea mate!! Thanks a lot for sharing it 🙂
    Any idea about How to display info as Tool Tip in Proclarity? I mean your solution works in Excel, but will it work in Proclarity?

    1. Hey Rockstar, I believe in earlier versions of Proclarity it wasn’t possible but I’m not sure about the latest versions. Unfortunately I don’t know off the top of my head. I’d have to test that. If you’re able to test that I’d love to know the answer.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s