Objective: The goal of this exercise is to practice the generation of PI OLEDB Enterprise transpose 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 2662
- What is Cross Apply?
- Outputting a Query to a CSV File
As well as these additional resources:
- Not authorized to view the specified discussion 2655
- Not authorized to view the specified discussion 2661
- PI SQL Commander
- Accessing the PI SQL Query Compendiums and Sample Queries
- The PI OLEDB Enterprise User Guide's explanation of catalogs (page 41-78)
- General SQL Training - w3schools
- Nugreen Database
Produce a PI OLEDB Enterprise transpose function(s) and query for the following scenario that outputs the requested data:
Note: The following scenario 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 the 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. A report needs to be generated showing the statistics for yesterday for all equipment at all plants, along with plant KPIs for the plant where this equipment is located.
- Generate a query, creating any necessary transpose functions, that outputs the ProcessFeedrate min, max, and total for yesterday for every piece of equipment in the NuGreen database, but also show the average uptime and quality, for the same time period, of the plant where this equipment is located. Each piece of equipment should have all of its data and the data for its plant on the same line.
Example output (all the data is the same because all attributes reference sinusoid for compatibility reasons):
2. The output of the previous query needs to be consumed by another application, but that application can only read from csv (comma separated values) files.
- Generate a csv file of the previous query's output
Have questions? Ask the community
When you are ready, compare your answers with the Solution: Writing PI OLEDB Enterprise Transpose Queries