Connecting QuerySurge to Power BI (Versions: 9.0+)

Versions: 8.0+

Versions:9.0+

QuerySurge's end-to-end data testing solution provides testing of all legs in the ETL process, and the QuerySurge BI Tester data source feature extends the solution to the Business Intelligence layer. BI Tester for Power BI allows QA team members to automate the process of retrieving data from Power BI reports and validating it against any supported data source. Users can use powerful SQL syntax to query the data query layer of reports, compare report data to data from upstream or downstream systems, create suites of multiple BI report tests, and schedule tests for execution according to BI system availability.

For a Power BI connection, you will need the following information (check with a Power BI administrator or other knowledgeable resource in your organization):

• Authentication type (QuerySurge supports Service Principal and Master User authentication)
• Client ID
• User Name or Tenant ID
• Password or App Secret

Note: QuerySurge's BI Tester feature is licensed separately. Contact QuerySurge Sales for information.

Account Level Considerations:

When testing Power BI, it's important to understand the capabilities of the Power BI account utilized. QuerySurge requires Power BI Embed Tokens for authentication and validation of data sets. This token generation is automatic and tokens can be generated on Free and Pro Power BI accounts. However, there is a hard limit on the number of tokens that can be generated for the lifetime of the Service Principal and Master User account types. We recommend utilizing this integration with accounts that have purchased Capacity, which removes this limitation.

Authentication Types:

There are currently two supported methods for authenticating to Power BI. These include Service Principal and Master User. QuerySurge supports both of these methods for authenticating with Power BI. We highly recommend the use of Service Principals over Master User accounts as Master User accounts are tied to account holders, In addition Master User accounts can only be utilized on accounts with Power BI Pro. The following two articles can be followed to setup a Service Principal or Master User.

• Setup a Power BI Service Principal
• Setup a Power BI Master User

Connect to Power BI

1. Log into QuerySurge as an Admin user. Navigate to the QuerySurge Admin view.
2. To configure a Power BI Connection, select Configuration > Connection in the Admin view tree (at the left).
3. Click on the Add button at the bottom left of the main panel to launch the Connection Wizard.
    
4. Provide a name for your Connection. Then, select "BI Tester: Power BI" as the Data Source.
   
   Note: The BI Tester JDBC driver is bundled with QuerySurge; install this driver on your Agent(s) from your QuerySurge Installer.
   
   
5. Once you have selected your BI Tester Data Source, the Wizard will tell you what information you are likely to need in order to create your Connection. Once you have collected this information, you are ready to click Next.|
   
6. Provide the connection information to your Power BI Instance. There are two methods for authenticating with Power BI. As such, the below documentation is split for both methods, please only follow the entry pertinent to your authentication method.
   
   Service Principal (Recommended): Required fields included Client Id (Application Id), Tenant Id (Directory Id) and Secret (Application Secret). A Test Query is optional, however it is required to validate the connection via the wizard. Click Next.
   
   Master User: Required fields included Client Id, Username (Often a FQN such as <name>@<domain>.com), and Password. A Test Query is optional, however it is required to validate the connection via the wizard. Click Next.
   
7. Click on the Test Connection button if a test query was provided in step 6.
   
   Note: You must have an Agent running with the driver deployed for this Connection deployed in order to test the Connection.
8. Click the Save button to save the Connection.
   
   
9. Congratulations! You’ve created a QuerySurge Connection. Again, make sure that you have deployed the driver for this Connection to all your QuerySurge Agents. (See Adding a JDBC Driver to your QuerySurge Agent for instructions.