Exercise: Write Transpose Queries for RTQP (PI SQL Client)

Document created by kduffy on Oct 4, 2018Last modified by kduffy on Jul 2, 2019
Version 18Show Document
  • View in full screen mode

Object: The goal of this exercise is to practice the generation of Real Time Query Processing (RTQP Engine) transpose queries. Whether the application is using PI SQL Client (OLEDB), PI SQL Client (ODBC), or PI SQL Client (JDBC), the queries themselves are the same.



Using the following resources:

As well as these additional resources:


Problem Statement:

Produce an RTQP query for each of the following scenarios that outputs the requested data using PI SQL Client (OLEDB):


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.

[hint: If you're receiving an error message when using two GetSummary based functions at once, try changing one of them to GetSummaries with a '1d' TimeStep; see solutions guide for full walk-through and detailed explanation]


2. A new application is being investigated for future use in production to generate the report in step 1, but it does not support functions.

- Rewrite the first query, creating any necessary custom database objects, that allows for the query to be executed by applications that reject table-valued functions


3. In the report, there was a particularly bad value noticed for one piece of equipment. We need to take a detailed look at the value throughout the day, as well as another value on that equipment that affects this value, to better identify the problematic timeframe.

- Generate a query, preferably not creating any new custom database object as this is a one-time query, to show interpolated values for the Process Feedrate and Motor Amps of cooling fan F-200 for all day yesterday at 15 minute intervals.


4. Another new application is being investigated for future use, but it does not allow OLEDB data sources. The data can come in from csv (comma separated values) files.

- Output the results of the first query (either with or without a table-valued function, your choice) to a csv file. As this is a one time development test, the automation of this csv file generation does not need to be implemented. Something that can do it one time is fine; however, it would be helpful to think about different ways in which to automate this process if this application were to be approved for use and rolled out to production.


Have questions? Ask the community

When you are ready, compare your answers with theSolution: Write Transpose Queries for RTQP (PI SQL Client)