Solution: Writing PI SQL Client and RTQP Queries

Document created by kduffy on Sep 26, 2018Last modified by kduffy on Feb 14, 2019
Version 12Show Document
  • View in full screen mode

This document contains the answers to Exercise: Writing PI SQL Client and RTQP Queries .


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.

SELECT a.Element, a.Name Attribute, sv.Value
FROM Master.Element.Attribute a
INNER JOIN Master.Element.ft_GetSampledValue sv ON a.ID = sv.[@AttributeID]
WHERE a.Element = 'P-344'
AND sv.[@Time] = 'y+22h'


To start developing this query, let's look at the query compendium for examples on interpolated attribute data. For RTQP, there's two different query compendiums that we can use.

  • Migration - Provides RTQP queries for corresponding PI OLEDB Enterprise queries
  • Queries - Provides RTQP queries from scratch without mentioning PI OLEDB Enterprise


Which compendium you use is personal preference, but for this guide, we will show both.


If you knew the PI OLEDB Enterprise query already and simply want to migrate it, you can use the third query in the Migration > Data > 1-Data.sql file:


If you wanted to start from scratch, you could use the fifth query in Queries > Element > 2-AttributeData.sql:



Either way, we have the general form of the query. We need to use the [GetSampledValue] table-valued function to get the data, and either the [Attribute] table directly if we only need the element name as a filter, or the [ElementHierarchy] table as well if we need the element path.


In this case, we don't need the [ElementHierarchy] table because "P-344" is a unique element name within NuGreen. So we can take the second example, change the element name, change the timestamp, and remove the attribute name filter. This produces the query seen here, confirmed with the correct output:


Note: Although not required here, if you wanted to specify an element path, then you could use the first query as a basis and it would end up looking like this:


The final constraint on this example is that we're not allowed to use table-valued functions, so CROSS APPLY Master.Element.GetSampledValue (a.ID, 'y+22h') needs to be replaced with a table.


Table-Valued Functions vs Function Tables provides an explanation on the topic, as well as steps on how to create function tables in RTQP engine.


From that guide, we see that we can right click on the existing GetSampledValue table-valued function, and create the function table:


We'll stick with the default name of ft_GetSampledValue:


Once we have the new table, we can use the Object Explorer (or RTQP Engine Data Model) to confirm what the names of the input parameter columns are, and adjust our query accordingly:


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.

SELECT e.Name, v.SteamFlow_TimeStamp, v.SteamFlow_Value, v.SteamFlow_UnitOfMeasure
FROM Master.Element.Element e
INNER JOIN Master.Element.Value
'Compressor', --Template
'|Steam Flow', -- AttributeTemplatePath
'SteamFlow_TimeStamp', -- TimeStampColumnName
'SteamFlow_Value', -- ValueColumnName
'SteamFlow_UnitOfMeasure', -- UnitOfMeasureColumnName
'SteamFlow_Error', -- ErrorColumnName
'm3/s' -- UnitOfMeasure
} -- AttributeMetadata
> v
ON e.ID = v.ElementID
WHERE e.Template = 'Compressor'
AND e.PrimaryPath like '%Little Rock%'


For this query, we need to find all the compressors in NuGreen, filter it down to just the Steam Flow attributes, obtain snapshot values, and convert them to m3/s. There's a few ways that we can arrive at this, and for learning purposes I will start with the more obvious way that leads to a sort of dead end, then show the less obvious way that leads to a successful query.


If we want to find the attribute values in general, we can do this by looking at the query compendium and the object explorer. For example, in Queries > Element > 2-AttributeData.sql, we can see the first query is capable of pulling the snapshot values from the attributes straight from the Master.Element.Attribute table:


But in the Object Explorer (or RTQP Engine Data Model), we see that this table doesn't have an [ElementTemplate] column, but it does have an [ElementID] column:


In the [Element] table, we see that it has the ID column we can use to join the [Attribute] table with, as well as the [Template] column that we can use to find down by "Compressor":


Combining these in the query and outputting the UoM, we see this:


The next step in this would be to simply convert the ft3/s output to m3/s, but when we go to the query compendium's Migration > Data > 3-UOMConversions.sql, we see this on the first line:

"-- RTQP Engine 2018 doesn't support any unit-of-measure functions."


This is a dead end for at least the first release of the RTQP Engine, but there's another way to do this. If you remember from 5:08 - 6:05 of Not authorized to view the specified discussion 2664, we can use Master.Element.Value in the Templates folder to perform UoM conversion at the template level. This is also hinted at in the query compendium in Queries > Element > 3-TemplateSpecificData.sql. All of the example queries show NULL as the UnitOfMeasure, but it demonstrates to us where we can put the new name for the UoM and have the conversion take place:


With this in mind, let's execute this template with our Steam Flow attribute and Compressor template (and cleaning up the SELECT statement and comments) to show that we can get the values in their default UoM:


Note that we received 23 rows, which matches the total number of Compressor throughout the NuGreen Enterprise. You can confirm this from PI System Explorer, but since PI SQL Client does not necessitate the AF SDK being installed on the client machine, it may be better to use RTQP's FindElements function to confirm this count:


Now that we've confirmed that we have the correct data, we can apply the UoM override in this query to perform the conversion from ft3/s to m3/s:


Note: If you're unsure of how to specify the unit of measure in the query, you can look in PI System Explorer > Unit of Measure, then find the class of UoM (Volume Flow Rate in this case), and find the Abbreviation of the UoM of interest:


We can confirm that the conversion happened correctly by using Google's unit converter:


The final portion of the query, which could have been done earlier in you'd like, is to reduce the output down to only the Little Rock compressors, rather than all compressors in the NuGreen enterprise. To do this, we need to find a way to reference the Plant name. If we look at an example Little Rock compressor in PI System Explorer, we can see that it has both an attribute named 'Plant' whose value we could reference, as well as 'Little Rock' in the element path name:


Either way could be done, but given the RTQP schema, it's much easier to filter by the element path than by an additional attribute value. We can see this by looking at the object explorer's column list for Master.Element.Element:


To confirm this value is the path we're expecting it to be, we can either add this column to the SELECT portion of our existing query, or we can run a side test query against any element that we'd like:


Since the query already has the Element table in it, all we need to do is add a WHERE condition for any path containing 'Little Rock', and we have the correct elements in the output:


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.

SELECT a.Element, a.Name Attribute, s.TimeStamp, s.Value Average, s.Error
FROM Master.Element.Attribute a
INNER JOIN Master.Element.Element e ON a.ElementID = e.ID
CROSS APPLY Master.Element.GetSummaries
a.ID, 'y' /*@StartTime*/, 't' /*@EndTime*/, '1d' /*TimeStep*/,
'Average' /*@SummaryType*/, 'TimeWeighted' /*@CalculationBasis*/, 'MostRecentTime' /*@TimeType*/
) s
WHERE a.Name = 'Fuel Gas Flow'
AND e.Template = 'Heater'


This query is focusing on a similar concept as the last one, with one attribute for all elements on a certain template, but instead of applying a UoM conversion, we want to find summarized data for the attribute.


In the query compendium, we can find an example of attribute summaries for a specific attribute on a specific element with queries such as the sixth one in Queries > Element > 2-AttributeData.sql:


In the last example, we saw how to use the Element table to filter by Template name, and the Attribute table to join with the ElementID. In this case, we use that same approach to come up with the following query:


Now, we can combine the attribute summary output in the first query (changing the timestep to 1 day) with the element template filtering in the second query as such:


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.

SELECT PrimaryReferencedElement Unit, count(PrimaryReferencedElement)
FROM Master.EventFrame.EventFrame
WHERE Template = 'OSIUnitProcedure'
AND StartTime BETWEEN 'y' AND 't'
AND Duration > '30m'
GROUP BY PrimaryReferencedElement
HAVING count(PrimaryReferencedElement) > 2
ORDER BY count(PrimaryReferencedElement) DESC


For this query, the first steps would be to find all the OSIUnitProcedure event frames in the last day, showing their associated element and the duration.


Most of this information is found in the eighth query in Queries > EventFrame > 1-EventFrameSearch.sql:


The only part that missing is the duration, which we could calculate manually from the start and end times, but if we look in the Object Explorer (or RTQP Engine Data Model) for the Master.EventFrame.EventFrame's column list, we see that this duration column is already available:


So we can immediately use this in the WHERE clause to filter by 30m duration, as well as add it to the SELECT statement as a quick check that we're only returning the correct event frames:


At this point, we can use general SQL techniques to apply the GROUP BY, HAVING, and ORDER BY clauses necessary to massage this output into the form requested:


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 Master.EventFrame.FouledReactors)

CREATE VIEW [Master].[EventFrame].[FouledReactors]
SELECT PrimaryReferencedElement Unit, count(PrimaryReferencedElement) Total
FROM Master.EventFrame.EventFrame
WHERE Template = 'OSIUnitProcedure'
AND StartTime BETWEEN 'y' AND 't'
AND Duration > '30m'
GROUP BY PrimaryReferencedElement
HAVING count(PrimaryReferencedElement) > 2
ORDER BY count(PrimaryReferencedElement) DESC



Despite the denormalized nature of the RTQP Engine and the, therefore, simpler looking queries, it can still be a good practice to create views for any production query, especially if it's being hard-coded or there's programming done to the output of it. This extra layer allows for the SQL DBA's to change something about the query without changing how to execute it. For example, if 30min is too short and needs to be lengthened to 1h, or if 2 is too low a threshold and needs to be raised to 4, these changes can be made to the view, leaving the application's query unchanged while applying the new logic to the results.


To create this view, we start by navigating to Master > EventFrame > Views, and we can right click on Views to select Scripts > Create View > New Query Editor Window:


This generates the template for us to fill in, naming the view FouledReactors, and pasting in the query from the previous example:


Once executed successfully, we can refresh the views list and SELECT * from the new view to prove the same output is delivered: