AnsweredAssumed Answered

Retrieving PI data as relational query

Question asked by Idris on Sep 13, 2016
Latest reply on Sep 19, 2016 by Idris

Hi All,


I was wondering if anyone can help in showing me how I can extract the value(s) of a PI tag every second when the value of another PI tag reach a certain limit, for all the assets in that template.

This type of query is easily done on SQL database using the "where" clause.

I'm not sure if there is an easy way to extract this data from the PI system?


Background / Use Case:

Our PI system collects numerous data points from 200 locomotives we have in our fleet. As the locomotives are built the same, we have used AF template to create the attributes for all the locomotives.

The 2 data points that I am interested in are (to make it simple):

  • The speed of the locomotives (km/hr); and
  • Chainage (the location of the locomotive from the Port in km distance). For example 140km chainage means that the locomotive is 140km away from Port.


I would like to extract the speeds of all locomotives when they go through a certain chainage of the track (eg. between 140km - 145km chainage).

I'd like this to be shown in 1 table so that I can do further manipulation of the data. (Ultimately I'd like to create some sort of a graph with chainage as the X axis and speed in the Y axis, and maybe show the average speeds of all locomotives as they travel away from Port).


The table should look something like the following:

Date TimeLocomotive #Chainage (km)
Speed (km/hr)
12-Aug-16  13:45:011234140.160
12-Aug-16  13:45:021234140.259



Attempts so far:

1. I have tried creating an event frame template to trigger when the chainage tag reaches value between 140-145km. I have also set it up so that it records the speed attribute of the locomotives during this event frame.

However it seems that event frames only give the value of the attribute at the end of the event frame, not every second inside the event.

I can try extract the data using Datalink tools (i.e. Export the Event Frames result and then use Sampled Data to get per second data of the attributes between the Event Frame start/end times)

The problem with this is that I would have to manually point to all the data start and end times for each event frames. I don't know if there is a way to give all the event frames values in 1 table automatically?


2. I have tried using the SQL commander by following the tutorial created by OSI Soft:

Video 3 and 4 explains how I can setup DataT function to extract the data that I need.

However it returned with the following error: "Cannot modify Element 'Database Objects' in Element 'PI SQL' in Element 'OSIsoft' because the current user does not have Write permission."

Does anyone know what type of permission I need in order to use this functionality? Does it put excessive load on the server if more people start to use this function?



Any other ways anyone can recommend?

Thanks so much in advanced.