Power BI Row Level Security 1 – Static Security

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

Power BI Lineage and Impact Analysis

Another new feature from the May 2020 Microsoft Business Applications Summit

This became available in April 2020 and its a fantastic feature. Lets have a look at it

There is a lot going on in the Adventureworks Portal. Lets go to View to go into the Lineage View

The Lineage View gives us a lot of information so lets look at everything in more detail

Data Source

We start with the data sources. this gives us information about the type of Data Source

  • Local Database
  • Azure Database
  • xlsx

Click on each source to find out more information

Dataflow

I can immediately see there are issues with the dataflow

Clicking the two arrows allows you to see the lineage of just that one object

Everything else is greyed out

Clicking on the data flow shows you the objects within the flow but you cant look any further at the items within the object or any of the code behind the objects

Data Sets

Note the blue icons. This shows that the data set has been promoted for use. There is also an icon for certified data sets (If admin allows you to certify).

You can Promote datasets, Dataflows are currently in Preview. To do this, go to the Settings and Endorsement

Back to the Lineage

Within the data set we can now see the data items within each table object.

We can also see the measures that have been created with DAX, but we cant see the code that creates the DAX

Reports

The Reports contain all the visuals. If you click on the Up Arrow you can open the report

Dashboards

Finally information about the dashboards.

the great thing about Lineage is the ability to see everything that connects together

This will even work for reports and dashboards that are connected to data sets in other App workspaces.

Alerts

Check the impact of any changes. here we see if impacts very little.

You can also send an email to everyone who needs to be notified that a new data set is being published. this goes out to your Contacts List

Go to Your App workspace and select Workspace Settings

Go into Advanced and change the contact list. this will ensure that everyone receives an email about any impacts and changes.

Create your website with WordPress.com
Get started