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.
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:
groupId(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): Optional field. Defaults to utilize summarized entity data. Pass 0 to get underlying data.
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',
'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:
'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. Metadata can also be used to compare multiple reports. To obtain metadata for a report, use the
powerbiMetadata function as follows:
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):
- 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:
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 generate embed token for report - Response code 400
When executing against some reports an error is returned when generating embed tokens with the HTTP status code of 400. 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.