I know it was not difficult to implement dimension security in Analysis Services. While it’s not difficult, why did it cost me a lot of days to get this to work? Well, its because you have to do a lot of little settings and it’s easy to make a small mistake implementing dimension security. On the other hand, SSAS has a lot of settings and tabs! When doing things in the wrong order, it simply doesn’t work like you want it to do. In this article, I will describe how to implement dynamic dimension security in SSAS. Please read all steps before doing it yourself to prevent making mistakes.
SSAS consists of dimension and facts. Facts are all things you can measure and dimensions are views of the data. The basic security mechanism in SSAS is by using roles. You can assign security settings to a role and add members to that role. The security settings should be dynamic. When new users start at the company or change job positions, security on the olap cube should be changed automatically. When you build a cube for 5 users, settings can be done by hand manually. When no security is needed, please skip this article too:-)
Security can be set in many ways. Maybe you want to restrict permission on organisational units so users can only see their own organisation unit. Another possibility is that you want to protect your measure values like margin or profit for a big group of users. I will describe both options, cell/row-security and measure security.
I want to protect measures
This one is pretty easy to implement. I was searching on the internet and I found many articles with complicated MDX stuff. As you know I like to keep things simple. Implementing SSAS Security on a measure is simple. The first step is create a role and put the users/usersgroup in that role which need security. I call this role “RestrictedUsers.role”. Go to the “Dimension Data” tab and scroll down to the Cube you want to protect.
Here, you will find a dimension called “Measures Dimension”. In this dimension, you will find all measures. You will not find calculated measures in this tab. This will only work fine if your cube is based on actual physical database tables.
You can now deselect all measures you want to protect against users in this role.
If you are using calculated measures, you cannot hide them. You can use an alternative method by showing them 0. Check the blog from SqlJason for more information. I have tested this method and it works. It only doesn’t work for my client because the measures MUST be hidden.[adinserter block=”3″]
I want to protect dimension members
This one is a little bit more complicated because you need to do some preparation for this to get it to work and you also need an MDX (arghhh) statement. You need some database tables with the security settings for each user. Let’s take “Organisation” in this example. You want to protect the organisation table so users can only see their own organisation data.
Step 1 – Create a Organisation_User table
In the SQL Server database, you are gonna need to create and populate a table or view that holds the security data. Let’s say you have an Organisation table, an User table, then we need an Organisation_User table with the right combinations. Maybe your organisation has a HR-system which keeps hidden security. I mean, it keeps information about which user works at which department. That’s the information you need. You should extract that information and store in the Organisation_User table.
Your tables should look something like this:
User table: UserID, LoginName; Loginname = Domain + \ + Active directory name. For example: NL\Your.Name. This one is important.
Organisation table: Unique ID, all other attributes
Organisation_User: UserID, OrganisationID
The User table looks something like this:
Because my BI Solution is Dutch, I am using different names in this example. I use “Werknemer”, “Branch” and “Branch_Werknemer”.
The next step is add the tables to the SSAS Data Source View:
** Please note that I added a NTUserName in the bridge table too, this is not needed for getting this tot work.
Drag a relation between from the “bridge-table” to the werknemer and branch. Also notice another line to the branch. This is a line which come from the fact table you need to secure.
Step 2 – Create dimension
Create a dimension from the user table (werknemer in my example). You can hide this dimension later. You can hide the dimension in the cube by going to the “Dimension usage” tab, right click on the dimension, click properties. Then set “Visible” to false.
Step 3 -Create a measure group
Go to you cube, Cube structure tab. Create a measure group on the Branch_Werknemer table. Call it “Security” and name the measure “Security Count”. Hide the member. Because it only keeps one member, the whole measure group will be hidden in Excel.
On the “Dimension usage” tab, link the Branch dimension to the new measure group and also the “Werknemer” dimension.
Step 4 – Create role, assign MDX to dimension
Just like in the beginning of the article, you need a role to secure. Add a role called “RestrictedUsers” and assign the users or usergroup. I suggest you use Windows Groups to keep things simple. Let your IT department take care of that.
Browse to the “Dimension Data” Tab. You will see a Combobox. Browse down till you see the cube. Don’t click on the top, these are the database dimension tables and not the cube.
Double check that you are selecting the CUBE-DIMENSION and not the database dimension.
Select the dimension you want to protect. I my case, I used the “Branch” table to create a dimension called “Organisation” so I want to protect my organisation table. 10 cm down, click “Advanced”. Select the attribute you want to protect. Now comes the big trick that created a lot of pain in my head 🙂
STRTOSET(“[DimWerknemer].[LoginNaam].&[” + username() + “]”),
What does this mean? First, you need to give the name of the dimension and the hierarchy you want to secure. The second argument is the dimension with the user and loginname. The username which returns “NL\Your.Name” from the first step must exist. It will check that through the secured table we created. The [LoginNaam] hierarchy is a field in my table which keeps the Domain\UserName values.
Now if you only want to show totals from the secured members, check the checkbox “Enable Visual Totals” on the downside of the screen.
You need to create an MDX for every attribute you want to protect
Now you can test if it works. You can do this by asking a used who’s assigned to the role to browse the cube through Excel. You can also check the security by browsing the cube as different user.
Here are some thing to keep in mind:
- Process cube when something changes. You need to process all dimensions and facts first before you can make changes to the role
- Implement the MDX formula on all attributes which need security
- Work with windows group and add them to the role
- Do not use hard-coded dimension security on attributes
- Keep it simple
I hope this article helps you creating dynamic dimension security in Analysis Service. In the newer version of SSAS, you can use the Tabular model.