Problem Statement:
A company want to use “Out-of-the-Box Microsoft PPM Dashboards and Reports” in PowerBI. Specifically, they want to share the report “RESOURCE ASSIGNMENTS” with users within the organization, that have been assigned with Team Member licence in Project Online. The Resource Assignment report shows all tasks that a resource has been assigned to work on.
In Project Online, assignment of a Team Member licence limits users to view only Projects/Tasks they were assigned to. Enabling access for Team Member users to PowerBI reports by default gives users access to the full dataset without the controls that were applied in Project Online.
Management require user level access restrictions for PowerBI Reports, so that users can only see their own task assignments data.
Any security role defined in Project Online will not be inherited in a PowerBI data set and therefore an equivalent level of control will have to be enabled in PowerBI.
This problem can be solved by enabling RLS (Row-level security) in PowerBI.
Tools needed:
- Power BI Desktop/Service
- Project Online Environment
What is Row Level Security in Power BI?
Row-level security with PowerBI Desktop can be used to restrict access to data for users, by creating DAX filters at the row level, these filters can be defined within roles.
Implement Row-Level Security in Power BI
As a starting point, in the below image is the Out-of-the-Box report “RESOURCE ASSIGNMENTS” that displays task assignment data for all projects and resources from the connected Project Online site. In the next steps we will create RLS for this report, that will restrict access to the user level.
Step 1 – Create Table to consolidate, Projects & Resource Assignments:
In PowerQuery we will create a new table “ResourceProjectsAssignments” and use the Merge feature to combine 3 tables.
- The table “Assignments” will be merged with the “Projects” table, (using Project ID),
- Then we will merge the table “Resources” (using Resource ID),
- Expand the columns needed from the merged tables “Projects” & “Resources”.
We now have the complete dataset relating to resource assignments defined in our merged table, and we have included the “Resource Email” field that we can now use to apply a filter to our reports.
Step 2 – Replace Out of Box Report fields with fields from Merged Table:
Select each visualisation on the “Resource Assignments” PowerBI report and replace the data with the relevant fields from the merged table, “ResourceProjectsAssignments”.
Step 3-Define roles and rules with PowerBI Desktop
Under Modeling tab we will select Manage Roles.
- Create a new role with the name “TeamMemberView”,
- then select the new table created with PowerQuery “ResourceProjectsAssignments”
- Create a DAX formula [Resources.Resource Email Address] = USERPRINCIPALNAME() and Save. This DAX formula applies a filter to the new table that restricts data to that of the logged in user.
Step 4 – Test the defined role in PowerBI Desktop:
To test our new role, we will navigate again to Modeling Tab, and select View as “TeamMemberView” role.
As in below image, the report should now show only the data relevant for the selected role/user.
After testing in PowerBI desktop is complete, we can then publish the report to a PBI service workspace.
Step 5- Assign New Role in Power BI Service Workspace
- Navigate to the Power BI service, and click on the ellipses to the right of the Dataset name, then click on Security.
- Add all the users or group you want to apply Row-level security to.
Step 6 – Provide users access to the workspace:
Share the Dashboard with any user or team who requires access. The roles will be applied to read-only members and will not apply to roles with edit permissions.
As a result, below is the new “USER_RESOURCE ASSIGNMENTS” report with restricted access to user level as per management requirement, shared in a PBI service workspace.
Conclusion
In the scenario presented, Row level security is a solution to manage security for a PowerBI model and restrict data at the row level to show only the rows appropriate to a user role.
For more information visit our Business Applications services page.
Author: Codruta Guler, Project Consultant, Aspira.