AVEVA Data Hub Sample Graph QL Power BI Connector

Version: 1.0.0

The Sample AVEVA Data Hub Graph QL Connector for Power BI Desktop is used to get event data from the ADH API into Power BI Desktop. The connector uses the OAuth Authorization Code with PKCE flow to connect to the API and get an access token.

Power BI Deployment Requirement

1. Power BI Desktop
2. Optionally register a Client-Credentials Client in your AVEVA Data Hub tenant and create a client secret to use in Power BI. (Video Walkthrough)

• NOTE: This sample only requires read access to resources (Streams, Assets, etc.) to run successfully
• It is strongly advised to not elevate the permissions of a client beyond what is necessary.

Build Requirements

1. Visual Studio Code
2. Power Query SDK
3. Register an Authorization Code Client in ADH and ensure that the registered client:
   • Contains https://oauth.powerbi.com/views/oauthredirect.html in the list of Allowed Redirect URLs
   • Contains https://login.microsoftonline.com/logout.srf in the list of Allowed Logout Redirect URLs
   • Contains https://oauth.powerbi.com in the list of Allowed CORS Origins
   • Use this Client ID when configuring the project in the Building the Connector section of this guide.

Building the Sample Connector

1. Open the sample in Visual Studio Code

2. Rename appsettings.placeholder.json to appsettings.json

3. Update the ClientId parameter in appsettings.json with the client generated in the Build Requirements section

4. Optionally update the Resource parameter in appsettings.json if you are using a different region or non-production enivornment

5. Build the project by following below steps

   • Assuming "Power Query SDK" Extension is installed, As mentioned in Build Requirements
   • In Visual Studio Code, Navigate to Main Menu (Top Bar) --> Terminal.
   • Select option Run Build Task -> build: Build connector project using MakePQX
   • Once Completed successfully, *.mez files are generated and available in /bin/AnyCPU/Debug or /bin/AnyCPU/Release.

6. Build the project following Microsoft's build and deploy documentaion

Deploying the Sample Connector

1. Open Power BI Desktop, and navigate to File > Options and Settings > Options
2. Navigate to Security, and under Data Extensions select the option "(Not Recommended) Allow any extension to load without validation or warning"
3. Click OK, acknowledge any warnings
4. In your user's Documents folder, create a folder Power BI Desktop with a subfolder Custom Connectors
5. Copy the .mez file from either /bin/AnyCPU/Debug or /bin/AnyCPU/Release (depending on settings) into the new Custom Connectors folder
6. Restart Power BI Desktop, and the connector should be available

Note: For more information refer to Microsoft's distribution documentation

Using the Sample Connector

1. Generate a graph QL query within AVEVA Data Hub using the GraphQL console to be used later.
2. From Power BI Desktop, open Home > Get Data > More
3. The connector should be available as "Sample AVEVA™ Data Hub QraphQL Events (Beta)" in the category "Online Services"
4. Either select "Organizational account" and click "Sign In" or select "Client Credentials" and provide the client credenitals you created ahead of time
5. Click "Connect"
6. If using the connector for the first time, you may get another warning regarding untrusted connectors
7. When prompted for the Namespace Id, enter your Namespace Id
8. Click OK, and you will be prompted to login if you have not already, using an organizational account
9. Once logged in, the Power Query Editor should open and you will have the option to supply the previously generated GraphQL query
10. Click Invoke

When using the Power Query Advanced Editor, the function DataHubGraphQLConnector.Contents can be used where the input parameter is your Namespace Id.

Using the Results

After you have made a query you should be left with a result that looks something like this:

To get the result in a format that is useable by Power BI you will need to expand the results. This can be done by clicking the expand icon then clicking Done or Expand to New Rows. This may need to be repeated a few times to fully expand the results.

Once the data is expanded, if necessary, right click on column headers and use the "Change Type" options to assign the proper types, as all fields are treated as strings by default.

At this point, the data should be consumable in a Power BI Dashboard!

Tests

1. Open Visual Studio Code whith the Power Query SDK installed
2. Open the sample folder
3. Set a credential. See Microsoft's documentation for more information.
4. Evaluate DataHubGraphQLConnector.query.pq. See Microsoft's documentation for more information.

---

For the main ADH samples page ReadMe
For the main AVEVA samples page ReadMe