Microsoft didn't plan to support member properties in SQL Server 7.0 OLAP Services. Member properties contain additional dimension-member data that comes from columns in your dimension tables. For example, a product dimension might have member properties such as color, size, and manufacturer that you can use to provide a more specific description of the product. Before the commercial release of SQL Server 7.0, beta users insisted that member properties were necessary, so Microsoft added support for them just before releasing the product.
Now, most of you are using SQL Server 2000 Analysis Services, and you probably can't imagine what the product would be like without member properties. I can't think of one Analysis Services application that doesn't use some aspect of member properties. But even so, many people don't know how to use member properties to their full potential. Are you getting the most from member properties? You can increase the value of your analytic applications by tapping into these top 10 ideas for maximizing the value of member properties.
10. Use Member Properties to Store Associated Information About Dimension Members
Storing additional dimension-member information is the most basic use of member properties. If you've developed your own front-end application, you can display this associated information in fly-over hints (e.g., a tool tip) or in a property panel that appears whenever a user selects a dimension member. For example, if you have an OLAP cube that contains your product information, you could use member properties to store and display such product information as the product manager's name, the original product-release date, the current version number, the associated product family, and even a longer product description.
9. Use DIMENSION PROPERTIES to Show Member-Property Values
Because member properties contain information about dimension members, displaying member properties along with query results is helpful. When you create an MDX query, you can use the DIMENSION PROPERTIES keyword to specify that the query returns certain member properties with the dimension members on the rows or columns. Few third-party OLAP front-end products use this keyword to display member properties along with the query result, probably because the functionality is Microsoft-specific and can be tricky to implement if you don't plan it during application design. However, if you're developing a custom application that displays query results, using DIMENSION PROPERTIES is an efficient way to retrieve and display the values of the member properties. The following code example shows how to retrieve the type of member card that each of the top 10 FoodMart 2000 customers holds, based on unit sales:
SELECT { [Unit Sales] } ON COLUMNS,
TOPCOUNT( Customers.[Name].Members, 10,
[Unit Sales] )
DIMENSION PROPERTIES Customers.[Member
Card]
ON ROWS
FROM Sales
To see how the DIMENSION PROPERTIES keyword works, first run the preceding query in the MDX Sample Application. Then, double-click a customer name in the grid that contains the query result. Along with the query result, the MDX Sample Application returns all the available information about that dimension member, including the member-card type for each of the top 10 FoodMart 2000 customers.