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.

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.

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.

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.

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.

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.

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