BUSINESS INSIGHTS

Aug 29, 2017

Understanding Power BI Security and Data Access

Nathaniel Scharer Posted by Nathaniel Scharer

One of Power BI’s most valuable features is the ease of sharing the reports and dashboards you create. Understanding the best way to do that can be more complicated – especially if you only want to provide access to a specific user group, or you only want to share a portion of report data.

Understanding Power BI Security and Data Access.jpg

For example, a report creator may want to share a report containing sensitive financial data with the finance team and executive leadership only. An HR specialist might want to share an HR report that includes detailed salary information with the HR team only. In these cases, access to reports and dashboards should only be made available to specific groups or individuals. On the other hand, a report that shows company sales data could be of great use to sales managers and individual salespeople, but perhaps the data should be secured to show only the sales data for the regions or accounts that pertain to the individual viewing the report. Finally, it might be useful to share analysis with partners or suppliers that are not part of the organization. Each of these cases requires a different approach to ensure the right access is being provided to consumers of the Power BI artifacts.

Before we look at the different options available to address these scenarios, it’s important to understand the concepts of access, sharing, and security, as they are often confused. This is understandable, as these concepts are often used in conjunction with the development and deployment of Power BI solutions, and some terminology in use in the product (row-level security, for example) can contribute to misunderstandings.

As we dive further, we will cover how to provide access to Power BI artifacts to specific people in your organization, and how to restrict views of data within reports and dashboards to specified users. The implementation will require the use of some security features, but the complete security model for Power BI is out of scope. For more information on how Microsoft secures Power BI instances, see this documentation.

One simple way to think about access to Power BI dashboards and reports is to compare it to taking a flight on an airplane. To get to a destination, a traveler will need to have a ticket for that flight. The ticket (along with identification such as a driver’s license or passport) has the necessary information for the traveler to pass through various checkpoints to get on the flight, and will also have information about what types of services the passenger has access to once on board the flight.

This is similar to the process for a user accessing an organization’s Power BI service via authentication of user name and password. Once an authorized user has provided the necessary account information and password, they will be logged on to the Power BI service that hosts the workspaces, apps, dashboards, and reports for that organization.

Just because a traveler has passed through airport security doesn’t mean they have the ability to get on any flight in the airport… the airline ticket is for a specific flight! Similarly, an organization can use permissions to ensure that users who have appropriate permission to the organization’s Power BI service only have access to the dashboards and reports that are relevant to their role. This is access control… if the user doesn’t have the right permissions, they can’t access (or even see in the list of available reports/dashboards) the content. Power BI report and dashboard authors can define which organizational user groups should have content access, and whether to grant permissions for report creation collaboration, or solely for report consumption.

FirstClassBuisnessClass.jpg

Now that the traveler has boarded the plane they have a ticket for, another aspect of ticketing comes into play. Depending on the ticket, they might be sitting in first class, business class, or economy. So while the traveler on the first-class ticket might have access to a broader menu of meals, entertainment options, and conveniences, the economy traveler might only have access to a subset of those services. Thus, while all travelers are on the same flight, their experiences might be quite different. This is similar to the use of role-based security in Power BI dashboards and reports. If role-based security is being used, a report or dashboard creator can ensure that while a group of consumers of a report or dashboard are all interacting with the same artifact, members of different roles will only see the data that is relevant or permitted for members of that role. Managers or executives might be assigned to a role that allows them to view all of the data in the dataset, while individual salespeople might only have access to their region or customers.

The process for creating role-based security is managed by creating roles within Power BI desktop. A role uses data within the model to define access. For example, to restrict sales data to only sales representatives in a specific region, the workspace owner would create a role for each region, and then use a DAX expression to restrict the members of that role to the data filtered by that expression. As an example, the image below shows creating a role that filters the data by North America:

NorthAmerica.png

Once the dataset is published to the Power BI service, the owner assigns users to the role or roles that were created, as shown below:

RowLevelSecurity.png

While this method can reduce the number of reports and dashboards that need to be created for an organization, it does require careful management to both create the necessary roles in the report, and to add the users and/or groups to the role in the service. Using a SQL Server Analysis Services Tabular model to standardize the datasets behind your reports and dashboards is one way to minimize the work of creating and maintaining multiple security roles. It’s as easy as building and enabling roles and role filters with the SSAS tabular data source for use across multiple Power BI reports and dashboards.

Lastly, you can use dynamic security to restrict data access. By enabling row-level security in Power BI reports, based on a user’s log-in credentials, you set the parameters of what data they see. Continuing with our air travel analogy, the use of dynamic row-level security is similar to the information provided to the flight attendants regarding things like special meals. Travelers might be on the same flight, and in the same class and row of seats, but one might receive a vegan meal and the other a gluten-free meal, based on the information they provided to the airline. Dynamic row-level security is accomplished in Power BI by using user tables in the model and the USERNAME() or USERPRINCIPLENAME() functions in the DAX expression when creating security roles. The Power BI service passes the credentials of the user logged into the service and viewing the report or dashboard to the function and uses that value to filter the data, as show below:

ManageRoles.png

Creating the correct level of access and security for your Power BI reports, dashboards, and datasets can be complicated – but it doesn’t have to be! If you have a question or want to know more about how BlueGranite can help your organization with Power BI security, contact us!

PoweRBIeBook
Nathaniel Scharer

About The Author

Nathaniel Scharer

Nathaniel has over 10 years of experience with Microsoft Business Intelligence solutions and data platforms. He received his Master of Science degree from Norwich State University and enjoys working with industries surrounding sales, web analytics, and manufacturing.