Hello folks, this month I’m working building a Multidimensional OLAP Cube and my customer asks me to implement the Row Level Security, then instead create a lot of roles for each profile is better to create just one Dynamic role.
Let’s assume you already create all tables and views and has created the correct connections between those tables, and you have the diagram.
OK, now we have the tables and connections and what else?
- I need resource tables with ID, Account, and Name.
- A table that tells me which resource has access to which project. You will use only the resource ID and Project ID
For example, just to illustrate the table #2.
|Resource 1||Project A|
|Resource 1||Project B|
|Resource 1||Project C|
|Resource 2||Project A|
|Resource 2||Project E|
|Resource 3||Project F|
So, add those tables to the diagram.
Create a new Measure using the table with permissions, and create a dimension using the table resources. Dimensions are blue and Measures are Yellow.
The Idea here is, you get the resource account when the user connects to the report, then you check the permission and show the data based on their permission.
Now, let’s create the Dynamic role.
Open the Solution Explorer and right click on Roles and New Role. You can rename the role.
Open the role, and click on:
- Data Sources – select the option Read under Access
- Cubes – select the name of the cube and also set the access to Read.
- Dimensions – Set the access to Read for all dimensions that the users are able to see
And now the most important, here is where the magic occurs.
If you check my diagram, the table Users is connected to Project Permissions and the Project Permissions is connected to Project. So, my MDX should be inside the Project table.
- Click on Dimension Data – Select the Dimension Project and click ok
Now on the Attribute Hierarchy select Project Name for example and click Advanced
Now click on the first option Edit MDX and insert the following code:
[Project].[Project Name].[Project Name].MEMBERS,
( [Measures].[Project ID],
StrToMember(“[Users].[Resource NT Account].&[” + username() + “]”)
To do tests using the Visual Studio you can change the user account line to the line using the NT Account for a specific user
([Users].[Resource NT Account].&[ad\diegopereira])
And that’s all, of course, you have to adjust the MDX for your cube and select the correct items that you want to filter.
To test, open the Cube in Visual Studio go to Browser and set the Security Context to Current User.
If you are an administrator you will see all projects.
Now, let’s change the Security Control to a specific Project Manager when you do that the Dynamic Role will filter only the Projects that the Project Manager can see.
PS.: I changed the MDX line and inserted a specific AD Account
And the Cube is ready, now you can connect Excel and the Excel will show the information based on the user account that is using the excel and show only what that user has permission to see. You can also create a report using Reporting Services and do the same.