/sample-adh-data_retrieval-power_query_m

Apache License 2.0Apache-2.0

ADH Power Query M Data Retrieval Sample

Version: 2.0.0

Built with Power Query SDK in Visual Studio Code

The sample code in this repository demonstrates how to connect to ADH and pull data from Streams, Assets, and Data Views using Power Query M. Power Query works with a variety of Microsoft products such as Analysis Services, Excel, and Power BI workbooks. For more information on Power Query M please refer to Microsoft's documentation.

Requirements

  • Power BI Desktop
  • Register a Client-Credentials Client in your AVEVA Data Hub tenant and create a client secret to use in the configuration of this sample. (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.

Setting up Power BI

  1. Open Power BI Desktop.
  2. Click the Get data button in the Data section of the ribbon.
  3. In the Get Data window search for "Blank Query".
  4. Select Blank Query and click the Connect button.
  5. Click the Advanced Editor button in the Query section of the ribbon in the Power Query Editor.
  6. Paste the query from the desired .pqm file. See the Power Query Functions section below for descriptions of each provided function.
  7. Click the Done button.
  8. Right click on the function and rename it to match the copied function.
  9. Create all functions that will be used.
  10. Optionally create parameters for connection information like your Tenant Id by clicking Manage Parameters in the Parameters section of ribbon.
  11. Use functions in your queries. See the Using Functions section below for more information.
  12. You may encounter the prompt to "Please specify how to connect." If this occurs, click Edit Credentials, select Anonymous, and click Connect.

Note: It is not recommended to hard code the app settings directly in the power query scripts as this could pose a security risk.

Setting up Excel

  1. Open Excel
  2. Under the Data section of the ribbon, click the Get Data button.
  3. In the dropdown drill down to From Other Sources and click Blank Query.
  4. Click the Advanced Editor button in the Query section of the ribbon in the Power Query Editor.
  5. Paste the query from the desired .pqm file. See the Power Query Functions section below for descriptions of each provided function.
  6. Click the Done button.
  7. Right click on the function and rename it to match the copied function.
  8. Create all functions that will be used.
  9. Optionally create parameters for connection information like your Tenant Id by clicking Manage Parameters in the Parameters section of ribbon.
  10. Use functions in your queries. See the Using Functions section below for more information.
  11. You may encounter the prompt to "Please specify how to connect." If this occurs, click Edit Credentials, select Anonymous, and click Connect.

Note: It is not recommended to hard code the app settings directly in the power query scripts as this could pose a security risk.

Using Functions

The provided functions can be chained together in your queries to meet your needs. Every function (besides GetToken) requires a token for authorization to resources so you will usually start by generating one using GetToken. This pattern can be seen in the following example:

let
    token = GetToken(Resource, ClientId, ClientSecret),
    data = GetStreamWindowData(token, Resource, ApiVersion, TenantId, NamespaceId, "SLTC.SensorUnit1.TMP117", #datetime(2023, 5, 28, 0, 0, 0), #datetime(2023, 5, 29, 0, 0, 0)),
    expandedData = Table.ExpandRecordColumn(data, "Column1", {"Timestamp", "Temperature"}, {"Timestamp", "Temperature"})
in
    expandedData

The generated token can also be used for subsequent calls so long as it has not expired (tokens expire after 1 hour by default).

Functions can also be chained together to accomplish more complex tasks like retrieving data from a set of streams returned by a query. An example of this can be seen below:

let
    token = GetToken(Resource, ClientId, ClientSecret),
    streams = GetStreams(token, Resource, ApiVersion, TenantId, NamespaceId, "SLTC.SensorUnit1.TMP117 OR SLTC.SensorUnit1.DPS310"),
    streamIds = Table.ToList(Table.SelectColumns(streams,"Id")),
    data = Table.Combine(
        List.Transform(
            streamIds, 
            (streamId) => let 
                result = Table.AddColumn(
                    GetStreamWindowData(
                        token, Resource, ApiVersion, TenantId, NamespaceId, streamId, #datetime(2023, 5, 28, 0, 0, 0), #datetime(2023, 5, 29, 0, 0, 0)
                    ), 
                    "StreamId", 
                    each streamId
                )
            in
                result
        )
    ),
    expandedData = Table.ExpandRecordColumn(data, "Column1", {"Timestamp", "Temperature", "AtmosphericPressure"}, {"Timestamp", "Temperature", "AtmosphericPressure"})
in
    expandedData

Using the Results

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

Power Query Editor Result

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 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 or Excel Workbook!

Power Query Functions

Function Description
GetToken.pqm Retrieves a token using Client Credentials OAuth flow. Each of the functions below need this function to generate a token.
GetStreams.pqm Retrieves Streams based on query.
GetStreamWindowData.pqm Returns a collection of stored values from a Stream based on request parameters.
GetAssets.pqm Retrieves Assets based on query.
GetAssetWindowData.pqm Returns a collection of stored values from an Asset based on request parameters.
GetCommunityStreamSearch.pqm Retrieves Streams in a Community based on query.
GetCommunityStreamWindowData.pqm Returns a collection of stored values from a Community Stream based on request parameters.
GetDataViewInterpolatedData.pqm Returns interpolated data for the provided Data View and index parameters.
GetDataViewStoredData.pqm Returns stored data for the provided Data View and index parameters.
GetGraphQLQuery.pqm Submit a GraphQL query to AVEVA Data Hub.

Running Tests

  1. Open Visual Studio Code with the Power Query SDK installed.
  2. Open the sample folder.
  3. Rename appsettings.placeholder.json file to appsettings.json.
  4. Replace the placeholders in the appsettings.json file with your connection information and resources (Streams, Assets, etc.).
  5. Set a credential. See Microsoft's documentation for more information.
  6. 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