Exercise: Making PI OLEDB Enterprise queries

Document created by kduffy on Sep 25, 2018Last modified by kduffy on Jul 2, 2019
Version 23Show Document
  • View in full screen mode

Objective: The goal of this exercise is to practice the generation of PI OLEDB Enterprise queries. Whether the application is using PI OLEDB Enterprise directly or using it through the PI ODBC Driver or PI JDBC Driver, the queries themselves are the same.



Using the following resources:

As well as these additional resources:


Problem Statement:

Produce a PI OLEDB Enterprise query for each of the following scenarios that output the requested data:


Note: Each of the following scenarios represents a real-world usage of PI system data, but the tags being used are the default tags in order for everyone to be able to execute the query. If a scenario is particularly applicable to your operation, I encourage you to swap out the default tags with your own tags to make the queries more realistic.


1. An incident occurred at the Wichita plant yesterday at 10 pm; a snapshot of the pump in question needs to be looked at for investigation purposes. The tool used for investigations, however, cannot execute queries that contain functions.

- Generate a query that outputs the value of every attribute of Pump P-344 (NuGreen\Wichita\Distilling Process\Equipment\) yesterday at 10:00 pm. You cannot use a table-valued function in your final query (hint: Table-Valued Functions vs Function Tables).


2. A dashboard is showing the current values of the utility flow to all of the Compressors in a plant, but engineering would like another dashboard displaying the values in a different unit.

- Generate a query that outputs the current value for Steam Flow for all of the compressors in Little Rock (NuGreen database), displaying their values in m3/s instead of ft3/s.


3. A report needs to be generated showing the utility usage of the heaters across all plants for the last day.

- Generate a query that outputs the average Fuel Gas Flow for yesterday for all heaters in the NuGreen database, ordering them first by element name.


4. Long reactions can be a sign of reactor fouling, so maintenance would like a list of all vessels with multiple reactions yesterday taking longer than expected.

- Generate a query that outputs a list of units that had more than 2 reactions, stored in the AF server as 'OSIUnitProcedure' type event frames, with a duration longer than 30 minutes, ordered by their number of occurrences.

Note: To do this task, you'll need to Generate Sample Event Frames for NuGreen Database


5. The members of the maintenance team are not SQL experts and don't feel comfortable running a query this complex just to get their list of units. They want to have a very simple query to execute and have the SQL DBA be in charge of keeping the query up to date with any changes.

- Create a view in the Event Frame schema that outputs the list generated in item 4 using a query in the form of (SELECT * FROM NuGreen.EventFrame.FouledReactors)


Have questions? Ask the community

When you are ready, compare your answers with the Solution: Making PI OLEDB Enterprise queries