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.