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:
- Not authorized to view the specified discussion 2661
- Accessing the PI SQL Query Compendiums and Sample Queries
- Table-Valued Functions vs Function Tables
- Generate Sample Event Frames for NuGreen Database
As well as these additional resources:
- Not authorized to view the specified discussion 2655
- PI SQL Commander
- The PI OLEDB Enterprise 2017 R2 User Guide's explanation of catalogs (page 41-78)
- General SQL Training - w3schools
- Nugreen Database
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