I suggest you use Windows Groups to keep things simple. Add a role called “RestrictedUsers” and assign the users or usergroup. Just like in the beginning of the article, you need a role to secure. Step 4 – Create role, assign MDX to dimension On the “Dimension usage” tab, link the Branch dimension to the new measure group and also the “Werknemer” dimension. Because it only keeps one member, the whole measure group will be hidden in Excel. Call it “Security” and name the measure “Security Count”. Create a measure group on the Branch_Werknemer table. You can hide the dimension in the cube by going to the “Dimension usage” tab, right click on the dimension, click properties. This is a line which come from the fact table you need to secure.Ĭreate a dimension from the user table (werknemer in my example). ** Please note that I added a NTUserName in the bridge table too, this is not needed for getting this tot work.ĭrag a relation between from the “bridge-table” to the werknemer and branch.
The next step is add the tables to the SSAS Data Source View: I use “Werknemer”, “Branch” and “Branch_Werknemer”. The User table looks something like this:īecause my BI Solution is Dutch, I am using different names in this example. Organisation_User: UserID, OrganisationID Organisation table: Unique ID, all other attributes User table: UserID, LoginName Loginname = Domain + \ + Active directory name.
Your tables should look something like this: You should extract that information and store in the Organisation_User table. I mean, it keeps information about which user works at which department. Maybe your organisation has a HR-system which keeps hidden security. Let’s say you have an Organisation table, an User table, then we need an Organisation_User table with the right combinations. In the SQL Server database, you are gonna need to create and populate a table or view that holds the security data. Step 1 – Create a Organisation_User table You want to protect the organisation table so users can only see their own organisation data. Let’s take “Organisation” in this example. You need some database tables with the security settings for each user. 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. It only doesn’t work for my client because the measures MUST be hidden. Check the blog from SqlJason for more information. You can use an alternative method by showing them 0. If you are using calculated measures, you cannot hide them. You can now deselect all measures you want to protect against users in this role. You will not find calculated measures in this tab. This will only work fine if your cube is based on actual physical database tables. In this dimension, you will find all measures. Here, you will find a dimension called “Measures Dimension”. Go to the “Dimension Data” tab and scroll down to the Cube you want to protect. The first step is create a role and put the users/usersgroup in that role which need security. Implementing SSAS Security on a measure is simple. As you know I like to keep things simple. I was searching on the internet and I found many articles with complicated MDX stuff. I will describe both options, cell/row-security and measure security. Another possibility is that you want to protect your measure values like margin or profit for a big group of users. Maybe you want to restrict permission on organisational units so users can only see their own organisation unit. When no security is needed, please skip this article too:-) When you build a cube for 5 users, settings can be done by hand manually. When new users start at the company or change job positions, security on the olap cube should be changed automatically. You can assign security settings to a role and add members to that role. The basic security mechanism in SSAS is by using roles. Facts are all things you can measure and dimensions are views of the data. Please read all steps before doing it yourself to prevent making mistakes.
MDX CODE IN OLAP CUBE EXCEL ADD IN HOW TO
In this article, I will describe how to implement dynamic dimension security in SSAS. 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. 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. I know it was not difficult to implement dimension security in Analysis Services.
0 kommentar(er)
