Filter Cube Data by User
A fairly common request in regards to cubes is the filtering of cube data by and for specific users.
For instance, lets say that you have a Sales cube that contains a Sales Person dimension, and the company wants each Sales Person to be able to see his/her own data.
In addition, each Sales Person should only see his/her own data, i.e. should not be able to see the data of others. And lastly, non-Sales People with access to the cube, such as business executives, should be able to see all sales data.
One very effective way to restrict data and accomplish the task can be done through custom dimension security.
This article describes how to use custom dimension security in Microsoft Analysis Server to filter dimension data for the current user accessing the cube.
To use this option the user's Windows login username must be accessible in our Sales Person dimension. The sum of the entire process is simple: we identify whom the user is and then restrict their access to specific members within the dimension.
If your data environment does not contain the Windows usernames that you need, then you will need to bring this information into your environment. If you are using Active Directory and are familiar with coding against the .Net framework, you can write a simple program utilizing System.DirectoryServices to extract this information and then add some code to place it into your database. Once this information is available in your environment, you can add it to your dimension as a Member Property.
To add the username property to your dimension, expand the level that wish to add the member property. Right-click the "Member Properties" folder and select "New Member Property". In the "Insert Member Property" screen, select the field that holds your usernames and then click OK.
Save your dimension. Now your "username" field will be available to be accessed through customer MDX.
Create Restrictive Role
Next, we will create a new Role that restricts our dimension’s members for a sales person to that of the currently logged in user.
Also, we want to make sure that we only filter the right people, if a high level executive accesses this dimension they should be able to see all members.
In other words, if the current username is not equal to one of our Sales Person’s usernames then they can see all Members.
In Analysis Server, right-click "Database Roles" and select "Manage Roles".
Click the "New" button to create a new Role
From the "Create a Cube Role" screen
Set Role Name = FilteredBySalesPerson
Enforce On = Client
From within the Membership Tab, click the "Add" button
On the Add Users and Groups screen, add your users or groups as required
Click the Dimensions tab
Locate the dimension that you want to filter, and change the the "Rule" from "Un-restricted" to "Custom"
Click the ellipsis ("...") button in the Custom Rule column
Enter a description, "FilteredBySalesPerson" was descriptive enough for me.
Click the Advanced tab, under Allowed Members enter the following MDX:
Filter([Salesperson].[Name].members, Salesperson.CurrentMember.Properties("Username") = ucase(Trim(Mid(UserName, Instr(1, UserName, "\")+1))))
Your screen should look similar to:
Select the "Common" tab
Check the box for "Define default member and specify using MDX"
In the textbox under the "Define default member…" checkbox, enter the following MDX:
Filter([SalesPerson].[Name].members,[SalesPerson].CurrentMember.Properties("Username") = ucase(Trim(Mid(UserName, Instr(1, UserName, "\")+1)))).Item(0)
Click OK. On the Create a Database Role screen click OK again to return to the Manage Roles screen. Click Close to close the Manage Roles screen.
You should be all set now. Now when users attempt to access the Sales cube our new role will filter the cube based on their login username.
Summary
The intention of this article was to offer an effective way to solve a fairly common analysis request.
give an introductory look into the Cluster Service Automation Classes. The code presented in this article is very basic and should be built upon with your own functionality such as logging and error catching as well as other general programming practices. Those general practices are outside the scope of this article. In our example we only failed over 1 group, if you want to failover several groups the failover code above will fit nicely into a function that can be called as needed. For more information on Windows clustering visit http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnanchor/ht ml/anch_winclustering.asp.
Anthony Bressi is owner of Agilist Technologies Inc. which specializes in software for DBA's and SQL Server developers. Mr. Bressi has over 8 years of experience in the Microsoft SQL Server development environment.
