Monday, September 19, 2016

Power BI – A Secure Way to Share Data Dynamically and Visually - Part 1

Data

One of the major advantages of using Power BI over other business intelligence solutions (including Excel) to share data is the ability to leverage row-level security to filter data for specific users. This allows us to establish one dashboard with data dynamically displayed based on the user’s assigned role in the organization.

In this blog post we cover how to set up row-level security within Power BI. If your data set is larger than 1gb you will need to leverage an external data source such SQL Server Analysis Services (SSAS). Our next blog post will provide a guide for setting up dynamic security within SSAS.

Establishing Row-Level Security

The dataset to be used for this example is the sample data set that we use for conducting our Dashboard in a Day training sessions.

clip_image002

We are interested in securing the data contained in Sales table by geographic location. We would like users to see data from only from the countries assigned to them. Our sales data includes data from France, USA, Canada, and Mexico. A sample output report showing data from all countries is shown below.

clip_image004

Our first step in setting row-level security is to establish roles for which we will filter the sales data. In Power BI Desktop, click on the Modeling menu and the Manage Roles option.

clip_image007

Then, create the USA role as follows (filtering the country column by value “USA”:

clip_image009

Create similar roles for Mexico, Canada, and France using the same logic but replacing the country filter value with each country.

Once you have created the roles you can test the roles by clicking on the View As Roles option:

clip_image012

Clicking on the Canada role causes the report to change showing only sales data where the country is Canada.

clip_image014

Once you have completed establishing roles, publish the report to the Power BI Service where you can assign users to the roles you have established.

Log on to your Power BI Service and click on the ellipse next to the data set. Click on the security option and you should see the following:

clip_image016

Next you can assign specific users to each role or assign Office 365 groups or Active Directory security groups to each role.

clip_image018

So that’s it, hopefully you can see how powerful row-level security can be in sharing dashboards with a broad set of users and ensuring that they see only the data for which they are authorized. Remember that you can also establish security at the dashboard level to control which dashboards can be viewed by your audience.

Our next blog post will cover sharing data dynamically using SQL Server Analysis Services.