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.

DiagramWithProjectAndResources

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.

CreateNewRole

Open the role, and click on:

  • Data Sources – select the option Read under Access

DataSourceAccess

  • 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

DimensionProject

Now on the Attribute Hierarchy select Project Name for example and click Advanced

AttributeProjectName

Now click on the first option Edit MDX and insert the following code:

NONEMPTY(

[Project].[Project Name].[Project Name].MEMBERS,

(   [Measures].[Project ID],

StrToMember(“[Users].[Resource NT Account].&[” + username() + “]”)

)

)

MDX

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.

SecurityContent_CurrentUser

 

If you are an administrator you will see all projects.

BrowserData_Admin

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

SecurityContent_DynamicRole

BrowserData_ProjectManager

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:

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

Blog do Project

Tudo que você queria saber sobre Microsoft Project, Project Server e Project Online

ppm4all

by Allan Rocha

Paul's Project Server and Project Online Blog

Microsoft Project Server and Project Online (PPM)

%d bloggers like this: