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:

• SSAS/KIMBALL: modeling a N:M relation between dimensions (part I)
• SSAS/Kimball : building a multivalue dimension construction in SSAS (part II)
• SSAS: Joining issue with MDXing of M2M dimension (part III)
• SSAS/SSRS/MDX : Cascading parameters and M2M dimensions
• SSAS/SSRS : Building a graph for analyzing a subset related to the whole set 

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