Use Case - Publishing the Power BI Report with Row-level Security

Enable row-level security for different roles working with reporting data and assign these roles to Nimbus Service users.

The default Power BI template provides an account based security level. This means, that a user only sees data of the service(s) she/he is a member of. Within viewing that data there is no other restriction. The user can see the performance of the whole team.

In this use case, we want to add row-level based security into the report. We want to achieve the following:

  1. Establish one central account to share the report with all Nimbus Service users.
  2. Enable row-level security for different roles working with reporting data and assign these roles to Nimbus Service users.

PRECONDITIONS

  • One Power BI Pro account is needed to manage the row-level security
  • You need Tenant Administrator rights to create O365 users and assign them to Nimbus teams.
 

Show Icon Legend

💡 = A hint to signal learnings, improvements or useful information in context. 🔍 = Info points out essential notes or related page in context.
☝ = Notifies you about fallacies and tricky parts that help avoid problems. 🤔 = Asks and answers common questions and troubleshooting points.
❌ = Warns you of actions with irreversible / data-destructive consequence. ✅ = Intructs you to perform a certain (prerequired) action to complete a related step.
 
 

Example User Structure

In our example we work with the following services and users:

User
Nimbus Service(s)
Power BI licence
Role description
Agent1@qnnb.onmicrosoft.com A - Helpdesk (Enterprise Routing) none Agent
Supervisor@qnnb.onmicrosoft.com A - Helpdesk (Enterprise Routing) none Supervisor of the Enterprise Routing service
Agent2@qnnb.onm B - Customer Service (Contact Center) none Contact Center Agent
reporting@qnnb.onmicrosoft.com

A - Helpdesk (Member)

B - Customer Service (Service Supervisor, User Supervisor)

Pro Central Reporting Resource Account

How-To Steps

Create a central reporting resource account

  1. In the O365 Admin Portal, create a reporting user account.
    ✅ Note that if you are using Contact Center, this account needs to be licensed for Contact Center.
  2. In the Nimbus Admin Portal, add the account and assign the Nimbus Service Supervisor and Nimbus User Supervisor roles for all OU's to it.

Open the report with the central reporting resource account

  1. Open the report template Nimbus.pbit and connect to the data with the central reporting account.
    🤔 First time connecting? Read our Power BI chapter to learn how to connect users to your Nimbus reporting data.
  2. 💡 Tip: If the report had already been owith opened with another account, you may switch the account:
    1. Go to Files -> Options and Settings -> Data sources
    2. Select "Edit Permissions" and Edit the Organizational account.
  3. Refresh your data source.

✅ Make sure you see the data of all services before you go to the next step.

Create the row-level roles

1. Within Power Bi Client, go to Modeling > Manage Roles

2. Set the roles according to your needs. Here are some useful role filters: 

Role name
Description
DAX Filters
Agent Should only see statistics for own services and calls where personally involved. Filter on Table Users: [Id] = USEROBJECTID()
Agent 30 days View Should only see statistics for own services and calls where personally involved AND only data for the last 30 days

Filter on Table Users: [Id] = USEROBJECTID()

Filter on Table SessionDates: [Date] in DATESINPERIOD('SessionDates'[Date],MAX([Date]),-1,MONTH)

OU Supervisor  Should only see data for USers and Services of a specific OU

 Filter on Table Services: [OrganizationUnitId] = "x000xy0z-xxxx-00d0-yyy0-c0f0000001z1" (use your own OU Id)

 Filter on Table Services: [OrganizationUnitId] = "x000xy0z-xxxx-00d0-yyy0-c0f0000001z1"

The DAX expression USEROBJECTID() returns the Azure AD ObjectId of the current user. If you test these roles with the global resource account, you won't see any difference.

You do not need to filter on Services, because the service is already filtered on account level.

 

Once done configuring all roles save the report.

Optional: hide the central reporting resource account in the report

If you want to hide the Global Reporting Account from the users, you can use a filter "Transform Data" on the Users Table as described here: Use Case - Filtering the BI Report data model quickly

Share the Power BI report and assign the roles to the users

1. Go to https://login.microsoftonline.com/ and sign it with the global reporting account. In our example with reporting@qnnb.onmicrosoft.com

2. Share the report as described in this use case Publish to Power BI service

Show me an excerpt of steps

First - Create a workspace

Second - upload the report file with the added row-level security

Third - Add all Nimbus Service Groups as Viewers to the workspace

 
 

3. Now manage the roles inside the Data Source.

  1. Go to > ... > Security > Row-Level-Security
  2. Assign the users to the roles:
  3. In MS Teams, share the report.
    💡 Any user with Power BI Pro licence can share it.

Table of Contents