Retrieve data using PowerBI

You can use PowerBI to retrieve Insight data. For syntax examples, see the Query Examples section below.

Note: You will need a query endpoint from AVEVA Insight for your query.

To import Insight data to Power BI

1. In AVEVA Insight, identify the retrieval endpoint for your Insight solution. You will use this in step 4.

2. Open PowerBI.

3. Click Get Data, and choose OData Feed.

4. In URL, specify the retrieval endpoint (from step 1) followed by any query parameters. Click OK.

   See the "Query examples" section below.

   

5. Specify Basic Authentication, and provide your Insight username and password.

   

6. Click Connect.

7. Power BI shows a preview of your data retrieved from Insight.

   

8. Click Load.

9. In the upper-left corner, click to view the table of results.

   

Query examples

• Using GET method with ProcessValues:

  Historian/v2/ProcessValues?TagFilter=EngUnit eq ‘rpm’&DurationHours=2

  Historian/v2/ProcessValues?$filter=DateTime le 2019-01-15T03:57:29Z&TagFilter=startswith(Description, ‘Pump’)&DurationHours=10

  Historian/v2/ProcessValues?$filter=DateTime ge 2019-01-15T03:57:29Z and DateTime le 2019-01-17T03:50:54.881Z&TagFilter=startswith(Location, ‘/Houston/Mixing’)

  Historian/v2/ProcessValues?$filter=DateTime ge 2019-01-15T03:57:29Z&TagFilter=startswith(FQN, 'testds1')&DurationHours=5

  Historian/v2/ProcessValues?TagFilter=endswith('Level',FQN)

• Using GET method with AnalogSummary:

  Historian/v2/AnalogSummary?TagFilter=EngUnit eq ‘rpm’&DurationHours =2

  Historian/v2/AnalogSummary?$filter=EndDateTime le 2019-01-15T03:57:29Z&TagFilter= startswith(Description, ‘Pump’)&DurationHours=10

  Historian/v2/AnalogSummary?$filter=StartDateTime ge 2019-01-15T03:57:29Z and EndDateTime le 2019-01-17T03:50:54.881Z&TagFilter=startswith(Location, "/Houston/Mixing")

  Historian/v2/AnalogSummary?$filter= StartDateTime ge 2019-01-15T03:57:29Z&TagFilter=startswith(FQN, 'testds1')&DurationInHour=5

  Historian/v2/AnalogSummary?TagFilter=endswith(FQN, 'Level')

• Using GET method with StateSummary:

  Historian/v2/StateSummary?TagFilter=EngUnit eq ‘rpm’& DurationHours =2

  Historian/v2/StateSummary?$filter=EndDateTime le 2019-01-15T03:57:29Z&TagFilter=startswith(Description, ‘Pump’)& DurationHours =10

  Historian/v2/StateSummary?$filter=StartDateTime ge 2019-01-15T03:57:29Z and EndDateTime le 2019-01-17T03:50:54.881Z&TagFilter=startswith(Location, ‘/Houston/Mixing’)

  Historian/v2/StateSummary?$filter= StartDateTime ge 2019-01-15T03:57:29Z&TagFilter=startswith(FQN, 'testds1')&DurationHours=5

  Historian/v2/StateSummary?TagFilter=endswith(FQN, 'Level')

Notes about post-retrieval filtering

Any post-retrieval filtering may require you to also change the data type for a specific the column in the results list.

For example, suppose you ran this query:

/Historian/v2/ProcessValues?$filter=OPCQuality eq 192&DurationHours=24&Resolution=3600000&TagFilter= endswith(TagName,'%23testtag')

The results would be:

Then suppose you wanted to filter the "DateTime" column to include only yesterday's date. You must change the data type of the DateTime column first, and then select the date you want:

10. Right-click the "DateTime" column header, select Change Type. and then click Date/Time.

11. Right-click the "DateTime" column and choose Yesterday from the displayed list.

Then the filtering will take effect.