Exercise: Writing PI OLEDB Enterprise Transpose Queries

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

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:

As well as these additional resources:


Problem Statement:

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