Day: June 23, 2020

Enabling different roles and giving users access to different levels of data is called row based security.

For example; Managers of different departments should only see data for their own department. Higher level users should be able to see everything

To show how Row level security works, we are going to use the Azure Database AdventureWorksDW

There are two types of Role Based / row level security. Static and Dynamic. The first example will show static row level security.

Row Level Security and Role based security are the same thing.

Power BI Desktop. Get Data

Open Power BI Desktop and go straight into Power BI Desk top. We are going to create the data within Power BI

If you haven’t used this before you may need to log in. Username welcome1 PW trngPeak1

We are going to import two tables, DimSalesTerritory and FactResellerSales

The Report will be very simple as we just want to look at row level security for each Territory

After importing the tables, load the data

In relationships ensure DimSalesTerritory is connected to FactResellerSales by the SalesTerritoryKey

Power BI Desktop. Create Visuals

For this simple report we want the following visualisations:

Stacked Column Chart

Axis: DimSalesTerritory, SalesTerritoryCountry

Value: FactResellerSales, SalesAmount

Card

Field FactResellerSales, SalesAmount

Slicer1

Field: DimSalesTerritory, SalesTerritoryGroup

Slicer2

Field: DimSalesTerritory, SalesTerritoryRegion

Creating Roles

Role 1 We have a sales Manager that oversees USA

Role 2 We have a sales Manager that oversees Europe

Role 3 is for Pacific

In Power BI Desktop there is a Security section within the modelling tab

Click on Manage roles and create a new roll

1. Create -New Roll. Click … to rename as USA Sales Manager
2. Click … Next to DimSalesTerritory > Add Filter and Choose SalesTerritoryCountry and set up for “United States”

• Same again (Create New Role) for the Europe Sales Manager

1. Same again (Create New Role) for the Pacific Sales Manager

We now have three roles set up.

Testing roles in the Desktop

To test roles. Go back to Modelling > Security and View as

Stop viewing to go back to viewing all data without Row Level Security

Row Based Security in Service

Now we have three roles, these roles need to be assigned in the service

Publish your reports into Power BI Service. I am publishing to my App workspace Adventureworks Dev

To create an App workspace

https://docs.microsoft.com/en-us/power-bi/service-create-the-new-workspaces

Go to Power BI https://powerbi.microsoft.com/en-us/landing/signin/

Go to the workspace and check your report is available

The new Report is called RoleBasedSecurityTestAdventureworks

Next go to Security. Click on … Settings on the data set

Testing roles in the Service

Before assigning roles to a User or Group (As above), you can click on … against each role and Test as role

Once set up, it doesn’t matter how many times you republish a model. It wont cause issues with the security. This kind of security is called Static security because the values have been determined in the DAX expression in the desktop file.

Once you have tested your roles you can add users against each role.The Report is now ready use

This is fine when you are implementing a simple solution above but becomes a pain if you have, for example thousands of roles. employees who want access to their own payroll information.

The next how to guide will go through a basic dynamic security example