Creating a Dynamic Row Level Security for an OLAP Cube Multidimensional – Project Server 2013

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?

  1. I need resource tables with ID, Account, and Name.
  2. 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 Name

Project Name

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.

Enjoy it!!!

#ProActive  #JoinUs

Diego Pereira

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Blog do Douglas Romão

MVP Office Apps and Services | Modern Workplace and Business Applications Expert

Blog do Project

Tudo que você queria saber sobre Microsoft Project


by Allan Rocha

Modern Work Management - Project and the Power Platform blog

Microsoft Project, Project Online, Project for the Web and the Power Platform

%d bloggers like this: