maandag 12 september 2011

SSAS : Setup dynamic dimension security in Analysis Services

Introduction
In SSAS implementations there is always a demand for implementing security. There are multiple possible security options and this blogpost focuses on data level security or row level security : security that depends on the useraccount and connects users to certain rows in a table. Users needs to be managed as new users appear, some will disappear and from some users may change their role.


In this post i'll descibe the dynamic dimension security in SSAS i've built at a customer. It's also called 'attribute hierarchy security'. Dynamic dimension security is about allowing restrictive number of users to a certain set of information in your cube. So you need to create an allowed set of members that users in a role are permitted to access. In this blogpost i'll be defining a set by building a MDX query for showing departments for certain users.

In this post we will build a security based on a department dimension. The cube is secured by the department dimension, a bridge tabel and a user table. In addition, i have leveraged two local user accounts: SSASUser and SSASUser2.

Here are the steps that needs to be taken for a successful implementation of dynamic dimension security:
  • Create a table users with username field that should store the Windows accounts.
  • Create a bridge table that models the M2M relationship between users and department.
  • Fill the tables with appropriate values.
  • Add the tables to the datasourcce view of the SSAS project.
  • Create a user dimension.
  • Create a measure group for bridging the users and department.
  • Create regular relationships between users and the bridge userdepartment.
  • Create a new role in BIDS/SSMS.
  • Add MDX statement to the Allowed member set and set the Enable visual totals checkbox.
  • Deploy and test. 

Setup
Below are the steps described that i've taken to build a dynamic dimension security.

1) Create two SSAS users. I'll be using these users for testing the dynamic dimension security. I've added these to the SSASusers and the SSASUsers2 group.

2) Create some tables in SQL and build a cube on the tables with the following structure:


There a couple of sections involved in this diagram: a fact called, FactTest, a M2M dimension and a bridge for the security. The FactTest is a small example with two dimenions Date and Location. The M2M dimension setup is a bridge (BridgeLocationDepartment) and a Department dimension. The requirements of my customer tells me that there is a n:m relation between location and department. For this blogpost i've added two tables : BridgeUserDepartment and user. Most of these tables are described in more detail in other blogposts about SSAS M2M dimensions. You can find them here:
3) So there is n:m relation between location and department, a fact table, a date dimension and especially for this blogpost i've added two extra tables and these controls the security on the cube: BridgeUserDepartment and Users. The BridgeUserDepartment table handles the n:m relation between department and users. The first thing to check is the relation between User and Department:

 

User_Dim_Key 1 is the SSASUser and 2 is the SSASUser2.

Querying with MDX the Bridge User Department, SSASUser and SSAUser2 shows the following results:


4) In this step w'll setup the dynamic security for this cube. We will create a new role, Dynamic Security, with read access to the ResearchAverage cube.


Add the SSASUser and SSASUser2 users to the membership tab.This step is a pitty one, in my opinion. Now i've to set something at two places: in the database and in the membership tab in Analysis.


Give read access to the cube:


The next steps are done in BIDS (denali) but it makes no difference where you setup the security (in SSMS or BIDS). Only when you setup the security in SSMS, it will be overwritten when the cube is deployed from BIDS. The next step took me a while to understand when i was working at the customer. In this customer situation there are about 50 dimensions and a lot of role playing dimensions. One error i keep on getting was

"An error occurred in the MDX script for the dimension attribute permission's allowed member set: The dimension '[User]' was not found in the cube when the string, [User].[UserName].[UserName].&[<user>], was parsed."

Because i had so many dimensions i didn't see the cube dimensions, at first. So i implemented the security at databasedimension level and not at cube dimensionlevel. The combobox "dimension" stores both database dimensions and cube dimensions. This is very confusing. I'm not completely sure that this also needed in this every situation. Anyway i've implemented the security at cubedimension level.


The following window does the trick. It limits the allowed set because of the usage of the UserName() function. This function returns a windows useraccount like Laptop\Hennie or Laptop\SSASUser.


I've left open the "enable visual totals" (lower left) intentionally and pressed on check (not shown in the window) and we have a....successful check!



The next step is running SSMS with the SSASUser account


And we can only see the departments that are authorized for the SSASUser:



The next step is running SSMS with the SSASUser2 account


And we can only see the departments that are granted by SSASUser2:



Okay, let's try another dimension like date and ...Now we can see all the information. That was not supposed to happen.



You can solve this with "enable visual totals" option.



And now only the facts are shown that belongs to a department:




Other things to do are making things invisible. The Bridge User Department measure and the User dimension provides a base for the security of the dynamic dimension security. These tables are only needed for the security and not for end user querying. So these two should be set to invisible. Important note: these are only hidden and when the end user is familiar with the presence of them, still they can query them.

Conclusions
There some things that are a bit annoying in my opinion:
  • It's not fully dynamic because i've to add the user to the membership tab also. To grant a user i've to add the user to the database and to the memership tab. 
  • The distinction between regular and cube dimension did cost me a lot of time to find out, because i didn't see the cube dimensions at first sight (because the customer had about 50 dimensions).
  • Another problem i've encountered now is that i've a benchmark graph with all departments and the user can compare his department with all of the other departments. Now with this security only the departments which are granted to the user are visible. I'm still working on a solution for this. When i've a proper solution i'll blog about this.
So for securing the cube, dynamic security is a great solution for letting the users play with the cubes with Excel.

Greetz,
Hennie

16 opmerkingen:

  1. When adding the allowed set to the dimension, do we need to add it to all attributes or just one (maybe the key attribute)?

    Thanks in advance.

    BeantwoordenVerwijderen
  2. Hi, great blog. Helped me a lot to understand dynamic security.
    I have a question. Recently I built a cube and delivered to another team.
    That team has some process(using AMO) that adds dynamic security. When they had added dynamic security, they used the "enable visual total".
    Now I have no control over that team's process. But my client want to see
    all total NOT visual total. Is there a way I can do so that client always see original total(irrespective of the users allowed region).
    Like scopes or any script? If I use scope is that going to overwrite by role?

    BeantwoordenVerwijderen
  3. Hi,
    I have followed the same method in implementing dynamic security. Cube is processed and deployed well. But I cannot see the filtered data based on the role. I can see all the data of all users. Is it dependent on admin rights on server or something? I have admin rights on the server.

    BeantwoordenVerwijderen
  4. Hi,

    I'm having the same issue as Mai H. We followed all the instructions but data is not being filtered.


    Thanks,
    Jameson

    BeantwoordenVerwijderen
  5. When browsing the cube, do it as the role you created and NOT as admin, then your filtered data will appear.

    Try browse the cube from client (ex Excel) and see what happens.

    Create a user on your server that is not db_owner/admin on the DB. Then add the user as member to the role. Log in as the new user on server and then browse the cube.

    BeantwoordenVerwijderen
  6. Deze reactie is verwijderd door de auteur.

    BeantwoordenVerwijderen
  7. It shows all the members in the hieararchy when Enable Visual Totals is disabled.

    BeantwoordenVerwijderen
  8. Thank you so much! I've been struggling with the same exact scenario you mentioned above - trying to set security on the databaselevel dimension instead of the cubelevel dimension. I do have a question, though...does this mean you have to go in and set the security up this way for each cube? I created an "Active Directory" cube that lists my users and the locations they have permissions to see, but setting the security in this cube...only affects this cube, correct?

    BeantwoordenVerwijderen
  9. van de Leensel Jérémie8 november 2013 om 16:43

    Hi, thanks for this post. Will it work too with AD security groups? Or do I have to enter one by one each user?

    BeantwoordenVerwijderen
  10. Hi,Joint Partnership Company is similar to a simple partnership company with Registered Agents in Qatar however, a joint partnership company will have two classes of partners.

    BeantwoordenVerwijderen
  11. Hello, could anyone send me the solution folder for this? I crate one but it is not working.

    BeantwoordenVerwijderen
  12. Hi,

    Could you please send me the solution file ,it will be great help.

    My Email ID : Yadavakaran@gmail.com

    BeantwoordenVerwijderen
  13. Thank you so much! This was very helpful - especially the screenshots with the three different options which helped me understand how "All" shows up when I select "Members" instead of the specific attribute.

    BeantwoordenVerwijderen
  14. Another problem i've encountered now is that i've a benchmark graph with all departments and the user can compare his department with all of the other departments. Now with this security only the departments which are granted to the user are visible. I'm still working on a solution for this. When i've a proper solution i'll blog about this.

    Any news about this?

    BeantwoordenVerwijderen