Analysis Services – Data Driven Security Model

Preamble…

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!!)

Scenario

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:

  1. 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:

  1. Include table into Cube Project

This table can then be incorporated into the cube Data Source View, linking to the ProductSubcategory table.

  1. 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]

  1. 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.

  1. 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:

  1. General à Read Definition
  2. Membership à NT AuthorityAuthenticated Users
  3. Cubes à Read Access to the Required Cubes, in our case Sales.
  4. 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)
    1. EXISTS([Product].[Subcategory].Members, STRTOMEMBER(“[User].[User].[” + UserName + “]”), “Fact Security”)
  5. 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.  

6. Test

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.

Administrator  Bob 
   

 

Conclusion

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.

Advertisements

  1. #1 by JG on July 27, 2011 - 2:38 pm

    Can I use this to accomplish this?

    I’m implementing security on a new SSAS cube. I’m trying to setup up Dimension Data Security b/c Cell security does not work with our 3rd party app.

    Here is my setup:

    1)The database has a dimension Branch.

    2) The cube has two dimensions: Owner Branch and CoOwner Branch.

    I need to allow each Owner/CoOwner to query data in their particular branches. i.e. they can select TX from Owner Branch OR TX from CoOwner Branch. If they select TX in Owner Branch, they should see all data regardless of CoOwner Branch. When I set up dimension security for both Owner and CoOwner branch of TX, I only see where it exist in both.

    Is there a way to do this in dimension data security.

    Thanks in advance for any help you can provide.

    • #2 by Gavin Russell-Rockliff on August 13, 2011 - 9:56 am

      hey JG,

      This sounds like you would need to implement a specal case of the security MDX script.
      I have no dev environment right now to actually write some sample mdx for you… happens when I’m mid install of Denali…

      Basically, I would aim to include the full set of [CoOwner Branch] members if the current member of [Owner Branch] is not [All]… bit of an ugly IF or a SCOPE should do the trick.

      Would love to read your blog post in reply of how you soved this one 🙂

  2. #3 by Sub on August 14, 2011 - 9:40 am

    Hello,

    Found it to be a gr8 thread.

    Can you pls advice on below –

    How do I show these maesures dynamically based on users.

    Example –

    User 1 can see only Measure M1,M2,M3

    User 2 can see only Measure M2,M5,M7,M8

    User 3 can see only Measure M3,M8, M20

    and so on..

    Measures M1,M2,M3 … are all physical measures.

    I have a dimension – DimOrgUsers. I want to create only one role, and define access to the measures using MDX dynamically,

    Pls note that, I have already defined security of my other dimesnion (geography,kpi) based on dimuser and bridge table approach. Now I just need to define dynamic security on the different measures M1,M2 etc. based on users. I do not want the measures to be visible at all to the end client.

    Pls advice how this can be done

    Regards,

    Sub

  3. #4 by JG on August 15, 2011 - 2:53 pm

    Thank you Gavin,

    I’m not very good with MDX, but I will do some research on that. I’ll be sure to post IF I come up with a solution.

  4. #5 by Shakir Bohari on April 2, 2012 - 1:12 pm

    Hi Gavin,
    To start of wth a great post!

    A question though how would you do if you want to set that a certain member should have access to other users data. For instance if you have employees and mangers, were the manager should have access to the employees numbers but the employeers shouldn’t be able to see each others data.

    Best regards,
    Shakir

    • #6 by Gavin Russell-Rockliff on May 2, 2012 - 11:34 am

      Hi Shakir,

      Sorry for late reply. Been a while since I’ve been able to get into the blog.
      The scenario you’ve described would be an extension to the SSAS security model. Best case would be to have the user’s login names stored in your dimension, such as [employee].[employee].[domain\name]. you could then use dynamic MDX to build up the allowed set using the UserName() function.
      So your employers role would be something like =DESCENDANTS(STRTOMEMBER(“[employee].[employee].[” + USERNAME() + “]”), , SELF_BEFORE_AFTER)
      And the employee role would only include the employee itself, like STRTOMEMBER(“[employee].[employee].[” + USERNAME() + “]”)

      All the best.

  1. Data Inspirations » SQLU SSAS Week: Cube Deployment 101

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: