The Power BI Wizard is a utility which assists users in the creation of QueryPairs against Power BI visualizations. This Wizard provides a no code solution for testing data within Power BI visualizations and for viewing details on your Power BI Reports.
Accessing the Power BI Wizard
To open the Power BI Wizard create or edit an existing a QueryPair which utilizes a Power BI connection (For information on setting up a connection to Power BI please see Connecting QuerySurge to Power BI). QuerySurge will automatically detect any query which uses Power BI as its connection and modify the Query Editor toolbar as seen below:
Standard Query Editor Toolbar - No Power BI connection Used
Power BI Query Editor Toolbar
Clicking on this new button will open the Power BI Wizard modal as seen below. The Wizard is tied to the respective editor which it was opened from, this allows for different Power BI connections to be utilized on your Source & Target queries.
Power BI Wizard Welcome Page
Note: The title indicates the respective query (Source or Target) used to open the Power BI Wizard.Using the Power BI Wizard
Choosing a Report
The Power BI Wizard needs some details on the report to load. On the second screen of the Wizard you will be prompted to enter a few core pieces of information.
Report Selection Page
Workspace (Required): Are a folder like object in Power BI. Users can have access to one or more Workspaces in their Power BI instance. QuerySurge will automatically fetch all Workspaces which are available to user configured with your Power BI connection.
Report (Required): After selecting a Workspace, QuerySurge will fetch all Reports associated with the Workspace. As with fetching Workspaces, the reports returned are those which are available to user configured with your Power BI connection. Note: Report listings are populated based on selected Workspace. Changing Workspaces will trigger the Report list to reload and clear any currently selected Reports.
Passing Row Level Security
Row Level Security may need to be provided when utilizing a Service Principal. Attempting to load a report which requires Row Level Security will trigger an error if the report requires such details. To determine if Row Level Security is needed, please speak with your internal Power BI Administrator. For more information on Row Level Security please see our article Power BI Row Level Security
Working with a Report
Once all information is provided, QuerySurge will render your report directly in the Wizard as seen below:Loaded report with highlighting enabled
All visualizations that can be used as part of your QueryPair in will be highlighted in red. Right-clicking on any of the valid visualizations within the report will bring up a context menu with the following options:
- Copy: Generates SQL to query a visualization or slicer in QuerySurge and adds it to clipboard. Users have the option to generate SQL with or without any filtering. When generating SQL with filtering, the query will contain information on the current state of all slicers configured.
-
Replace in Editor: This functionality is similar to copy, however instead of utilizing the clipboard SQL generated will automatically be inserted into your Source/Target query.Note: This function is a replacement only. Any existing logic contained in your Source/Target will be overwritten.
- View SQL: Will open a modal that contains the SQL for querying the selected visualization or slicer.
- View Details: Will open a modal that contains metadata on the visualization or slicer selected. Metadata returned include internal identifiers on the workspace, report and visualization as well as the visualization type. When viewing a slicer an additional section on filter details will be included.
Power BI reports can contain multiple pages, the bottom toolbar of the Power BI Wizard contains a drop down which will list all of the available pages for a given report. In addition, a checkbox is available to enable/disable the visualization and slicer highlighting.
Bottom Toolbar
Slicers(filters)
Slicers are one of a few ways to have filtering done within a Report in a Power BI. This filtering is done at the page level of a report and will affect all visualizations that are directly linked with the dataset being filtered by the Slicer. Slicers are visible inside of the Power BI report and often contain a list of options for users to filter their dataset. In the below screenshot the report Regional Sales Analysis has two Slicers one for Year and the other for Category.
Report with Slicers
Altering the currently selected Category's has a direct impact on the data displayed in this report. As seen below, only selecting the Glider Category decreases the value in the Quantity visualization from 135,993 to 6,140 as seen below.
Altered Slicer Output
When using the QuerySurge Power BI Wizard, select any Slicer which you wish to include in your test. Using the Copy or Replace in Editor context options you will be able to select if your query will contain the Slicer (filter) information. The below example shows a query generated with Slicers.
SELECT * FROM powerbiReport(
'78ec2b8d-33d8-4eac-bb11-640caafca914',
'5267364f-86e3-4aed-8427-8362a9985d4b',
'ReportSection',
'VisualContainer5',
1, --useSummarizedData, either 0 or 1
'', --userId
'', --roles
'', --customData
'{"slicers": [{"slicerName":"VisualContainer2","slicerState":{"filters":[{"$schema":"http://powerbi.com/product/schema#basic","target":{"table":"Date","column":"Year"},"filterType":1,"operator":"In","values":["CY2016"],"requireSingleSelection":false}],"targets":[{"table":"Date","column":"Year"}]}},{"slicerName":"VisualContainer3","slicerState":{"filters":[{"$schema":"http://powerbi.com/product/schema#basic","target":{"table":"Product","column":"Category"},"filterType":1,"operator":"In","values":["Glider"],"requireSingleSelection":false}],"targets":[{"table":"Product","column":"Category"}]}}]}'
); Common Issues
Authentication Timeout Error
The below is an example of performing a request within the Power BI Wizard when the authentication token is expired; in this case, using the Refresh button for the Report drop-down. This will only occur with long periods of idle time having the Wizard opened after the initial setup was performed.
When this type of error occurs, a dialog will appear to attempt to re-authenticate and reset all initial parameters. Selecting ‘No’ or closing the dialog will not update any parameters, but most operations will not function in this state. A restart of the Wizard or clicking on ‘Yes’ for the error dialog will resolve this issue.
Report Selection Timeout
Row Level Security Errors
The below error will occur when passing Row Level Security options when none are present for a select Report. This error, and any type of input error, can be resolved by clicking on the ‘Back’ button, changing inputs to expected values, and re-submitting to generate the report on the last screen.
Example RLS Error
Authentication Issues
The below error occurs if QuerySurge is unable to connect or log into Power BI. Additional information will be logged into the Tomcat log for your QuerySurge server. Ensure all credential information for your Power BI connection is correct.
Authentication Error