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.
8. Use Calculated Members to Show Member-Property Values
Many applications don't support the DIMENSION PROPERTIES keyword, but Analysis Services and OLAP Services let you use calculated members to return member-property values along with query results. Just create a calculated member that has the same name as the member property, and use a formula like the following to return the value of the member property:
WITH MEMBER \[Measures\].\[Member
Card\] AS 'Customers
.CURRENTMEMBER.PROPERTIES
("Member Card")'
SELECT \{ \[Member Card\], \[Unit
Sales\] \} ON COLUMNS,
TOPCOUNT(
Customers.\[Name\].Members, 10,
\[Unit Sales\] ) ON ROWS
FROM Sales
This query returns the top 10 customers based on unit sales. The query displays each customer's total unit sales value and the member-card type.
7. Use Member Properties as an Alternative Dimension Hierarchy
Member properties aren't very different from dimension levels. For example, in the FoodMart 2000 Sales cube, the Customers dimension is organized geographically. Couldn't you just as easily organize the customers by member-card type? Sure. The query in Listing 1 returns the top five customers based on unit sales for each member-card type. Figure 1 shows the query's result.
Listing 1's query creates four calculated members in the Product dimension—one for each member-card type. I chose the Product dimension because I needed a dimension other than the Measures and Customers dimensions. Each of the four calculated members returns the sum of the selected measure (in this case, Unit Sales) for each of the customers who have that card type. For example, the Bronze calculated member returns the sum of all customers holding a Bronze member card. I used the GENERATE() function to determine the top five customers for each of the four member-card types.
6. Use Member Properties to Store Starting and Ending Dates for Each Time Period
If you develop your own analytic applications and you want to create an application that understands what calendar dates each time member corresponds to, you can include in the Time dimension member properties that return the starting and ending date of each time period. Client applications can tell the difference between members at different levels, such as months and years, but they can't tell for sure what dates map to each time period. For example, a time period might be called Week 5, but that name doesn't tell the client application how to map the time period to the calendar. Plus, you can create the Time dimension members in any language, which further complicates the problem. For example, a client application might not be able to recognize the word January in multiple languages. Having member properties that contain the starting and ending dates of the time period can help you, as a client-application developer, determine how to correlate the members of the Time dimension to an actual calendar. For example, the member property for Holiday Season might contain the specific dates November 27-January 6.
5. Use Member Properties to Store Alternative Language Names
These days, even small companies often have a presence in more than one country, and you might want or need to provide analytic applications that can display data in the appropriate language for the region where users are viewing it. When you create a member property in Analysis Manager, you can change the properties of the member property to reflect local data. For example, say your company is based in France and some of your product names appear slightly differently in English than in French. You can create a member property to contain all the English versions of your product names. You configure this property by setting the language property of the member property to English and the type to Caption. Setting the member property this way is a hint that will help the analytic application display the correct product caption for the locale where the application is running.
4. Use Member Properties to Implement Dimension Security
To protect confidential information, you might want to control which OLAP users can access certain dimension members. For example, in a retail-sales application, you might want a store manager to have access to the sales information for only her store. Most DBAs control data access by using dimension-level security. If you have a close mapping between your users and your dimension members (meaning that a one-to-one relationship exists between each user and the dimension members he can access), you can store the usernames in a member property for that dimension. When a user logs in to your application, you can check which dimension members she's allowed to access based on whether that username appears in a dimension member's property. Better yet, you can have Analysis Services match the username to the member property for you. Just edit a Cube Role, click the Dimensions tab, and change one of the dimensions to Custom; then type the appropriate MDX into the Allowed Members dialog box. (For more information about how to restrict user access to selected information, see "Security and Parameterization," December 2002, InstantDoc ID 27040.)
3. Use Member Properties for Member-Name Aliases
Have you ever made a bar chart on a cube that has long dimension-member names? All the names run over each other, and the chart usually displays them as truncated. To create short and long alternative captions for member names, you can use a technique similar to the one I described for creating multilingual cubes. Just use Analysis Manager to configure the property type as a short caption or a long caption. If you don't need a member name's full detail, you can configure your application to use the short caption or even an abbreviated caption.
2. Use Member Properties for Member-Selection Criteria
Possibly the most obvious and valuable use of member properties is for filtering (selecting) dimension members. The code in Listing 2 shows how to filter the Customer dimension to find only male customers who have Silver member cards. Then, the query finds the top 10 of these customers based on unit sales. I always get a kick out of running a query like this one in the FoodMart 2000 Sales cube because some of the men have the most unlikely names. You can see what I mean by looking at Figure 2, page 44, which shows the query's results.
1. Use Member Properties with Virtual Dimensions
Analysis Services' virtual dimensions expose member properties as a separate dimension. The FoodMart 2000 Sales cube contains several virtual dimensions: Education Level, Gender, Marital Status, Promotion Media, Promotions, Store Size in SQFT, Store Type, and Yearly Income. Each virtual dimension is based on a member property from a nonvirtual dimension. Virtual dimensions are efficient because they don't require extra disk storage as a typical dimension does and they still perform well. If you used virtual dimensions to filter dimension members, the query would look like the following example, which finds the top 10 single females based on unit sales:
SELECT \{ \[Unit Sales\] \} ON
COLUMNS,
TOPCOUNT( \[Customers\].\[Name\]
.MEMBERS, 10, (\[Unit Sales\],
\[Marital Status\].\[All Marital
Status\].\[S\], \[Gender\].
\[All Gender\].\[F\]) ) ON ROWS
FROM Sales
I'm sure you're using member properties today in your analysis solutions, but are you using them to the fullest extent? When you use member properties creatively, you can increase the level of detail you get from Analysis Services. I hope these ideas help you create more valuable solutions. If you have other innovative ideas for using member properties, please send them my way, and I'll share them with other readers.
