Analysis Services cubes provide a granular mechanism to maintain security rights, right from cube level through to individual dimensional elements. This security is maintained through the implementation of cube roles, where individual Windows Accounts or Groups can be assigned various access rights. These access rights are traditionally managed through SQL Management Studio, as and when required. See Technet for a full description on roles and how to maintain them.
There are cases however, where this maintenance can become tedious, especially in cases where a large number of individual users require granular rights against dimensions with many hundreds or thousands of members.
In traditional SQL based systems, these types of scenarios are typically handled through SQL Server mapping tables. These tables would map individual windows logins against the corresponding entities that that user had rights to see. It is possible to re-use these types of mapping tables in the context of Analysis Services to manage security access. (without writing extensive XMLA or C#-type code to manage roles!!)
Let’s take a version of the Adventure Works cube, and assume that we need to manage security rights to the Product Sub Category level in the Product dimension. The process would be as follows:
- Implement and Populate Mapping Table
As described above, let’s create a mapping table, which maps an individual user against the dimension key that he/she has rights to access; something like:
- Include table into Cube Project
This table can then be incorporated into the cube Data Source View, linking to the ProductSubcategory table.
- User Dimension
From this table, we can create a User dimension, with one level, namely User. So, the user represented in the above table would have a key of [User].[User].[bi2010bob]
- Link Dimension to Cube
Firstly, let’s add our mapping table into the cube as an additional measure group, with a basic measure.
We can then add our new User dimension to the cube, and ensure that the relationships have been setup correctly.
- Create cube role
The final step in the process is to create a role in the cube which will use our underlying security mappings to limit user roles.
This role relies on some predefined MDX functionality.
- UserName. This function in MDX returns the NT name of the user that is connected to the cube, in the form domainusername. E.g. bi2010bob
- StrToMember. This function takes string input, which can be a concatenated expression, and returns the underlying cube member with this name or key.
Exists. This lesser-used function determines limits a given set, returning only those members that have actual values in a given Measure Group.
- Again, see Technet for a full description of Exists
We create a role, with the following properties:
- General à Read Definition
- Membership à NT AuthorityAuthenticated Users
- Cubes à Read Access to the Required Cubes, in our case Sales.
Dimensional Data à in the attribute against which we’re creating security, in our case Product Subcategory, add the following MDX (Under the Advanced Tab, Allowed Member Set)
- EXISTS([Product].[Subcategory].Members, STRTOMEMBER(“[User].[User].[” + UserName + “]”), “Fact Security”)
- Enable Visual Totals. This will depend on your security policy. If checked, will only show totals that apply to the restricted set. If unchecked, the All levels will display the totals as if security were not applied.
All done! Now we’re ready to deploy, process and test.
The table below outlines the different result when querying Order Qty by Subcategory, as an Administrator and as Bob.
Extending the Analysis Services security model to exploit SQL managed mappings is a tremendous way to leverage existing security mappings, as well as manage those scenarios which are simply too cumbersome to manage by hand.