Retrieve data using PowerBI

You can use PowerBI to retrieve AVEVA 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 AVEVA Insight data to Power BI

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

2. Open PowerBI.

3. Select Get Data, and choose OData Feed.

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

   See the "Query examples" section below.

   

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

   

6. Select Connect.

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

   

8. Select Load.

9. In the upper-left corner, select 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 select Date/Time.

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

Then the filtering will take effect.