With QuerySurge BI Tester for Power BI, data testers can use familiar SQL syntax to retrieve query data from Power BI Reports and validate it against any QuerySurge-supported Source or Target. Power BI connections can be set up in the QuerySurge Admin view, and utilized in the same manner as any other Connection types (i.e. in QueryPairs and Staging Queries, and Test Suite Connection overrides).
For details on setting up a connection to Power BI, see the article Connecting QuerySurge to Power BI.
Determining a Reports Workspace ID
To retrieve data from a Power BI Report, a Workspace must be identified using its Id. To find the Workspace ID for a Report see the below steps:
1. Login to Power BI Web
2. On the left navigation section, select "Workspaces". A list of Workspaces will appear which the current user has access too. Select the Workspace which contains the report you would like to test
3. Using the URL extract the ID for your workspace. This ID can be found using the following pattern: https://app.powerbi.com/groups/<Workspace ID>/list/dashboards?noSignUpCheck=1
Note: Users private workspace "My Workspace" is not supported. You must use a created workspace as a users private workspace does not have an ID.
Determining a Report ID
The next piece of information needed is the Report ID for the Report which contains the data you wish to extract. To find the Report ID for a Report see the below steps:
1. Navigate to the Workspace containing the report you wish to test
2. Open the report by clicking on the report name
3. Using the URL extract the ID for your report. This ID can be found using the following pattern:
https://app.powerbi.com/groups/<Workspace ID>/reports/<Report ID>/ReportSection?noSignUpCheck=1
Note: Each report will have its own ID. Copying reports will generate a new ID for the report.
Determining a Page Name
Once a Report ID has been extracted we will need to identify what Page the object which we would like to extract data from exists on. To find a Page Name see the below steps:
1. Open the Power BI report which is to be tested
2. On the left hand side of the report, a list of pages will exist. We recommend selecting the edit button, as the page names can be difficult to copy from this view
3. Right click the page which contains the object which you would like to extract data from and click "Rename Page". Copy the page name
Note: QuerySurge expects that each page will have its own unique name. If duplicate names exist, QuerySurge will append a numeric value for the duplicates in the following format "_<Occurrence>". Numbering of duplicate page names are based on Power BI's internal id's. Please use the Power BI metadata request to determine renamed pages for your queries.
Determining a Visualization Title
The last piece of information required is the Visualization object which you would like extract information from. To find a Visualization Title see the below steps:
1. Open the Power BI report which is to be tested
2. Click "Edit" on the top of your Power BI report
3. Click on the Visualization which you would like to extract data and select "Format" on the right hand side.
4. Expand the Title section and copy the value in fiend "Title txt"
Note: As with page name QuerySurge expects that each visual will have its own unique title. If duplicate titles exist, QuerySurge will append a numeric value for the duplicates in the following format "_<Occurrence>". Numbering of duplicate visuals names are based on Power BI's internal id's. Please use the Power BI metadata request to determine renamed visuals for your queries.
Note: In some cases visuals may not have a title or the title will be dynamic. In these cases Power BI's internal names can be utilized to identify the visual to extract. Power BI internal names are not readily visible, however they can be extracted using the QuerySurge's Power BI metadata extraction query seen below.
Querying Report Data
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)
-
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. Note, the internal Power BI name can also be used if needed. -
useSummarizedData(Integer): Optional field. Defaults to utilize summarized entity data. Pass 0 to get underlying data. -
userName(String): Defaults to ''. The user to emulate viewing the report. Only a single username can be passed. NOTE: Only available in versions 10.0+ -
roles(String): Defaults to ''. Roles to select when applying. Multiple roles should be passed as a comma seperated list. For example 'Admin,Tester' NOTE: Only available in versions 10.0+ -
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+
All arguments excluding useSummarizedData must be surrounded by single quotes, and single quotes within the argument value (i.e. apostrophes within a report name) must be escaped with an additional single quote. Following is a sample SQL statement against the our Regional Sales Analysis report:
SELECT * FROM powerbiReport('73ec2b8d-33d8-4eac-bd11-640caafca914','5266364f-86e3-4aed-8437-8363a9985d4b','Regional Sales Analysis','Sales and Avg Price by Month');
When executed in QuerySurge, the following results are returned:
Queries can include standard syntax like joins, group by, single-valued functions, aggregate functions, aliases, and more. Note that references to column names with spaces or special characters must be surrounded by double quotes:
SELECT isa."Sales",isa."Month"FROM powerbiReport('73ec2b8d-33d8-4eac-bd11-640caafca914','5266364f-86e3-4aed-8437-8363a9985d4b','Regional Sales Analysis','Sales and Avg Price by Month') AS isa
Querying Report Metadata
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 and underlying vitalization names. 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')
-
workspaceId(string): ID of the workspace which contains the report -
reportId(string): ID of the report which contains the data to extract -
pageName(string): Optional field. Name of the page containing the visualization. Pass an empty string for all pages -
userName(String): Defaults to ''. The user to emulate viewing the report. Only a single username can be passed. NOTE: Only available in versions 10.0+ -
roles(String): Defaults to ''. Roles to select when applying. Multiple roles should be passed as a comma seperated list. For example 'Admin,Tester' NOTE: Only available in versions 10.0+ -
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+
Example:
SELECT *FROM powerbiMetadata('73ec2b8d-33d8-4eac-bd11-640caafca914','5266364f-86e3-4aed-8437-8363a9985d4b')
Note: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). This query returns the query names (as table names) and column names for all queries in the report (the example here is truncated for brevity):
Example output 9.0 - 9.2:
Example output 10.0+:
Limitations
- Only Power BI Reports are supported for data extraction. Other entity types such as Dashboards and Paginated Reports are not supported at this time
- Custom Visualizations are not supported
- All visualizations have an export limit of 30,000 records. Any records past this set will not be included in the QuerySurge comparison.
- Extraction of data from Decomposition trees will only contain data currently being displayed.
- Exports using Underlying do not work if the data source uses Analysis Services live connection on versions older than 2016, when the tables in the model do not have a unique key.
- If using DirectQuery, the maximum amount of data that can be exported is 16 MB.
- Exports can be disabled by Power BI admins
- Power BI has limits on the number of embed tokens which can be generated for Free and Pro accounts. These limits will trigger 403 errors when generating embed tokens. Limits are removed in Power BI when capacity is purchased. For more information on this limit please see:
https://docs.microsoft.com/en-us/power-bi/developer/embedded/embedded-faq#how-many-embed-tokens-can-i-create- - Composite or Proxy Models are currently not supported
Common Issues
Table not found [42102-196]
On occasion when exporting data from Power BI a query may return a table not found error as seen below. These errors occur when "Continue On Viz Extract Error" is set to true and QuerySurge was unable to extract data from a visualization used in your QueryPair.
Common causes for visualizations to not extract are:
1. Extracting underlying data on a visual without an aggregate. Example:
A visualization which does not contain an aggregate
2. Adding data from two unrelated tables to a visualization. Example:
Two tables without a relationship
A single visualization which contains both tables
Unable to Connect/Login to Power BI using Service Principal or Master User
The above error indicates that an issue exists with the credentials provided for the Service Principal or Master User used in this connection. Reconfirm all details provided and ensure that the Service Principals secret is still valid.
Unable to generate embed token for report - Response code 400 (Bad Request)
When executing against some reports an error is returned when generating embed tokens with the HTTP status code of 400 (Bad Request). Often this code is triggered by reports which utilize Row Level Security. Row Level Security allows reports to be filtered/altered based on a individuals security groups. When Row Level Security is utilized on a dataset you will need to pass effective identities described in https://querysurge.zendesk.com/hc/en-us/articles/4416482653581-Power-BI-Row-Level-Security-Versions-10-0- or utilized a Master User account for authentication.
Another cause for 400 response code are invalid Workspace or Report IDs. Workspace and Report IDs have a specific format for their GUIDs, missing or additional characters will trigger a 400.
Unable to get report info - Response code 401 (Unauthorized)
HTTP 401 errors occur due to permissions issues. Power BI has multiple levels of permissions which can make tracking down the root cause of this error quite difficult. Below is one of the most common causes for this error.
- The report is utilizing a dataset which the user/service principal does not have access to.
- Service Principals are unable to to utilized Power BI APIs or are limited to a specific security group which does not include the Service Principal being utilized.
Unable to generate embed token for report - Response code 403 (Forbidden)
The 403 error indicates that the current user is forbidden to run one of the required commands for the QuerySurge integration. 403 permission related errors can occur for a few reasons, below are some of their common causes.
- The user utilized in the connection does not have access to a given workspace or the users permissions is set to viewer Viewer.
- Embed content in apps is disabled or limited to specific security groups which does not include the user utilized in the QuerySurge connection.
- The report being tested is utilized data stored in Azure Analysis Service or SSAS (with data gateway). When a report utilizes these types of data sources additional configuration is required which are listed below.
- Using Azure Analysis Service and a service principal, the service principal account needs to be configured with instance permissions. Once configured, you will need to pass effective identities as described in https://querysurge.zendesk.com/hc/en-us/articles/4416482653581-Power-BI-Row-Level-Security-Versions-10-0-
- Using SSAS with a data gateway with a service principal , the service principal account needs to have ReadOverrideEffectiveIdentity permissions for the SSAS data source. Once configured, you will need to pass effective identities as described in https://querysurge.zendesk.com/hc/en-us/articles/4416482653581-Power-BI-Row-Level-Security-Versions-10-0-
Unable to get report info - Response code 404 (Not Found)
A 404 error can occur for the following reasons.
- Passing a correctly formatted Workspace or Report IDs but with incorrect values. For example using Workspace ID "5247364f-16e3-4add-9427-8352a9985d5b" in a QueryPair when it should be "8247364f-16e3-4add-9427-8352a9985d5b" (Notice the first digit is supposed to be an 8 not a 5).
Cannot load model - Response code 503 (Service Unavailable)
A 503, error can commonly occur when a Power BI report is utilizing a Proxy or Composite model. Proxy models are when a Power BI semantic model is linked to another Power BI semantic model. A composite model is when multiple semantic models feed a single Power BI report. QuerySurge does not support these scenarios.
Other/Generic Errors
In some cases, when attempting to query a Power BI visualization, a timeout error may occur. This can happen when querying very large reports (This timeout can be increased, please contact support for assistance on modifying this setting), however one additional common cause is network access between the QuerySurge agent and Power BI. In order for QuerySurge to connect to Power BI multiple domains will need to be connectable. For a list of domains which Power BI utilizes which need to be accesible please see https://learn.microsoft.com/en-us/power-bi/admin/power-bi-allow-list-urls