Reports in Power BI can be created in a way to restrict user access to data within dashboards, tiles, reports, and datasets. This restriction is done using developer assigned Roles which uses an end users Power BI account. Role based security is only implemented on accounts which have Viewer only level permissions to a Workspace.
Passing Effective Identities
Testing of reports with Row Level Security and Service Principals requires passing an Effective Identity which indicates to the report which roles and user to emulate. Depending upon the design of a report or type of test executed, passing of Effective Identities maybe required. The below steps highlight how to gather the required information for passing Effective Identities in your queries. The example below is using Power BI web, however Power BI desktop could be utilized to gather this information and elevated privilege's maybe required for some tasks listed.
Gathering Role Information
1. Follow the steps outlined in https://querysurge.zendesk.com/hc/en-us/articles/360057624212-Writing-SQL-Queries-Against-Power-BI-Reports-Versions-9-0- extract core information on your Power BI report such as the Report Id, Workspace Id, Page Name, Visualization Title. These items will be required to create your Power BI query.
2. Navigate to the Workspace which contains the report you would like to test.
3. Hover over the report which you would like to test. Click the 3 vertical dots to open the reports context menu.
4. Click the View lineage menu option.
5. Using the Lineage diagram, determine what Power BI Dataset is populating your Power BI report. In the below example the Dataset "pbiroles" is used by our report.
Note: Reports in Power BI can only have a single Dataset.
6. Close (Navigate back) the Linage screen and return to the Workspace.
7. Locate the Dataset in the Workspace. Hover over the Dataset which you would like to test. Click the 3 vertical dots to open the Datasets context menu.
Note: Datasets are only visible in the All or Datasets + dataflows views
8. Click the Security menu item.
10. The Security menu open will open the Row-Level Security window for the Dataset. This will list the different Roles on the left hand side, and any corresponding members. Make note of all Role names as they will be required for your query. Member counts located next to Role names should be omitted. For example, the below Dataset has two Roles named "Admin" and "Tester".
Note: User can be assigned to one or more Roles.
Passing Effective Identities in Queries
The query syntax for BI Tester queries is based on H2 SQL and supports most of the H2 SQL grammar and its function calls, so SELECT-type queries against Power BI follow a familiar form. The main element is the powerbiReport table function, which instructs the BI Tester driver to execute the report and pull back the query data as a table for you to query. The function's syntax is as follows:
powerbiReport('workspaceId','reportId','pageName',
'visualizationName',useSummarizedData, 'userName', 'roles')
-
workspaceId(string): ID of the workspace which contains the report -
reportId(string): ID of the report which contains the data to extract -
pageName(string): Name of the page containing the visualization -
visualizationName(string): Title of the visualization to extract data from -
useSummarizedData(Integer): Defaults to 1. Indicates the type of data extraction used.-
- 1 = Summarized Data
- 0 = Underlying Data
-
-
userName(String): Defaults to ''. The user to emulate viewing the report. Only a single username can be passed. -
roles(String): Defaults to ''. Roles to select when applying. Multiple roles should be passed as a comma seperated list. For example 'Admin,Tester' -
customData(String): Defaults to ''. Free form text field to pass back to Azure Analysis Services for additional filtering. NOTE: Only available in versions 10.3+ -
Slicers(String): Defaults to ''. Filters which should be applied to your Power BI visualization. NOTE: Only available in versions 10.3+
The following is a sample SQL statement against the our "pbiroles" report:
SELECT * FROM powerbiReport('73ec2b8d-33d8-4eac-bd11-640caafca914',
'8ace3d8e-8aca-4f6c-939f-e2da15c7321f',
'Sales Metrics',
'Sales By Country',
1,
'pbiTester@mydomain.com',
'Admin,Tester');Passing Effective Identities in Metadata Queries
When developing data tests for Power BI reports, it can be useful to see information about the datasets returned by the report for learning about the columns available. Metadata can also be used to compare multiple reports. To obtain metadata for a report, use the powerbiMetadata function as follows:
Syntax:
powerbiMetadata('workspaceId','reportId','pageName','userName','roles')
Note: The page name field is optional. Pass an empty string to retrieve all pages.
Example:
SELECT *
FROM powerbiMetadata('73ec2b8d-33d8-4eac-bd11-640caafca914',
'8ace3d8e-8aca-4f6c-939f-e2da15c7321f','pageName'
'pbiTester@mydomain.com','Admin,Tester')Note that powerbiMetadata can only retrieve one report per metadata query, and it cannot be combined with a report data query (i.e. joins are not supported).
Azure Analysis Service
When utilizing a service principal, the service principal account needs to be configured with instance permissions. Once setup, the user name provided in the Power BI query must be the principal Object ID. Object ID is a common term used throughout Azure so it is important to extract the correct ID. To locate the principal Object ID please follow the below.
1. Log into the Azure Portal
2. Navigate to App Registrations
3. Locate the service principal created for QuerySurge and open its entry
4. In the overview section click the hyper link for "Manage application in local directory"
5. On the new window which has opened copy the field Object ID.
Note: Per Microsoft, the service principal must be a server admin or role member of the Azure Analysis Service in order to successfully access the report data.
Limitations
- Passing Effective Identities to reports without Row Level Security is not supported.
- Roles must always be passed when using Effective Identities with Service Principals.