Solution: Making PI OLEDB Provider Queries - Reads

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

This document contains the answers to the Exercise: Making PI OLEDB Provider Queries - Reads .


1. Find the previous 10 values of CDM158, expressed as a string, as well as the timestamps of those values.

SELECT TOP 10 DIGSTRING(CAST(value as int32)), time
FROM [piarchive]..[picomp2]
WHERE tag = 'cdm158'
AND time BETWEEN '*-1h' AND '*'


This is a combination of a query compendium example and the digstring tutorial.


From the query compendium > PI OLEDB Provider > Queries > 1-Archive Statements.sql, the following is the fifth query:

-- Returns the last 10 events of the "sinusoid" tag.

-- Both the "picomp" and the "picomp2" table optimizes "SELECT TOP ... ORDER BY tag, time DESC"

-- statements so that you can quickly get the most recent events.

SELECT TOP 10 tag, time, value, status FROM piarchive..picomp2

WHERE tag = 'sinusoid' ORDER BY tag, time DESC


From there, we can switch out the tag name for cdm158, but we'll see the negative integer values as seen in the beginning of the digstring tutorial document. Changing SELECT value to SELECT DIGSTRING(CAST(value as int32)) results in the values being reported as strings:



2. Find the snapshot value of all tags with a pointsource of R whose current values are greater than their typical values.

SELECT s.tag
FROM [piarchive]..[pisnapshot] s
JOIN [pipoint]..[pipoint2] p on s.[tag]=p.[tag]
WHERE p.pointsource = 'R'
AND s.value > p.typicalvalue


This is an adaptation of the first query in the "2-Point Database Statements.sql" query compendium file, and it can use either the [pipoint]..[classic] or [pipoint]..[pipoint2] tables.

-- Returns tags of point source "R" from the "classic" point class.

SELECT tag, descriptor FROM pipoint..classic WHERE pointsource = 'R'


By inspecting the columns list in the Object Explorer for this table, we can see that [typicalvalue] is the name of the column associated with the same field in SMT's Point Builder:


We can first query the pipoint2 table and confirm that we have everything we're looking for:


Then join it to the pisnapshot table adding the condition that the current value is higher than the typicalvalue


3. Find the total of all recorded values for CDEP158 from yesterday for midnight to 8am, 8am to 4pm, and 4pm to midnight.

SELECT value, time as [shift end time]
FROM [piarchive]..[pitotal]
WHERE tag = 'cdep158'
AND time BETWEEN 'y+8h' AND 't'
AND calcbasis = 'EventWeighted'
AND timestep = '8h'


This one is little more difficult as there's nothing in the query compendium for this exact use case. It relies on either reading the PI OLEDB Provider user guide's explanation of catalogs and tables or simply looking at the tables in the Object Explorer and testing a few of them until you find the right one. In the user guide, we see the list of aggregate tables listing pitotal as "Totals" for the aggregate type, and later we see that calculation basis can be set, meaning that we can query this table for the event totals:


Executing the predefined query for this table in PI SQL Commander we see calcbasis is TimeWeighted by default:


So we can switch this to EventWeighted in the WHERE clause. We also see timestep is allowed, which means that we can use this to break the time range into the eight-hour intervals as necessary:


We'll now notice that there are four intervals, instead of the three we're looking for, and that both end points of midnight are given full eight-hour intervals. We'll need to change the time range so that it cuts off one the extraneous interval. The PI OLEDB Provider by default will use the time specified as the end time of the interval (one can override this default behavior by using the Timestamp Interval Start = True connection option), so the first interval is actually 4pm the day before yesterday to yesterday at midnight; this is the one that needs to be trimmed off.

Note: If you're unsure about which interval to trim, you can always use the picomp2 table to return all recorded values and visually inspect them to determine which intervals are correct.


Since we need to trim off the first interval and we know it's using the timestamp for the end time of the interval, we can change the time range to be 'y+8h' to 't'. To clarify what the time column is referring to, let's rename the output column to be the shift end time.


As is common with the PI OLEDB Provider, there's another way to do this. While a direct performance equation for this use case (TagTot) is only applicable for time-weighted totals and not event weighted totals, there's still the ANSI SQL aggregate function of SUM() that can be used directly in the SELECT statement (as seen in the user guide):


Using the SUM() function, we can take a list of recorded values over the time periods and simply add them this way. Given that the time periods are difficult to group by (since it's grouped by eight hours periods), a UNION statement is the easiest.


This approach would be cleaner for queries that combine the days, or single hours; for example, this query for the last few days:


In general, it's good to keep in mind that there are multiple ways to approach this, especially in regards to aggregate functions and tables.


4. Find all connections made to the data archive in the last week

SELECT time, message
FROM [pilog]..[pimessagelog]
WHERE time BETWEEN '*-7d' AND '*'
AND source = 'pinetmgr'
AND message like 'New Connection%'


This one is also not in the query compendium, but is a good demonstration of how to explore the tables and check the user guide as well.


It should be fairly obvious that the correct catalog for log messages is [pilog], but the tables may not be as clear. From the user guide, we see that [pimessagelog] is the server logs and [pisdklog] is the local client logs:


Once we know which table to use, we can run the predefined query to see what the columns typically look like. Connection attempts are handled by the PI Network Manager subsystem, so we can filter down by [source] = 'pinetmgr' and the earlier hint specified that the log message will start with "New Connection ID: <#>". Putting these together (and restricting the output to just the time and the message), we get the final result:


5. Report the batchid; time range; and the min, max, and average temperatures during the time period of a batch of your choice

SELECT batchid, starttime, endtime, al.tag as "temp tag", av.value as "average temp",
mi.value as "min temp", ma.value as "max temp"
FROM [pibatch]..[piunitbatch] ub
JOIN [pimodule]..[pialias] al on ub.moduleuid = al.moduleuid
JOIN [piarchive]..[piavg] av on al.tag = av.tag
JOIN [piarchive]..[pimin] mi on al.tag = mi.tag
JOIN [piarchive]..[pimax] ma on al.tag = ma.tag
WHERE ub.[starttime] BETWEEN '*-3d' AND '*'
AND ub.[batchid] = '1001'
AND ub.[moduleuid] = MODULEUID('\Reactor1')
AND al.[name] = 'temperature'
AND av.[time] BETWEEN ub.[starttime] AND ub.[endtime]
AND mi.[time] BETWEEN ub.[starttime] AND ub.[endtime]
AND ma.[time] BETWEEN ub.[starttime] AND ub.[endtime]


This last example could be started with the query compendium's "5-Batch Database Statements.sql" , the user guide, or simple exploration of the schema. I'll start with the query compendium, specifically the fifth query; even though this is an insert query, it still demonstrates usage of the [piunitbatch] table and the MODULEUID function which converts a module path into a uid without needing to join the [moduleh] table (similar to the DIGSTRING function for digital state strings).


Now that we know the correct table and the MODULEUID function, let's right click on [piunitbatch] in the object explorer, run the predefined query, add the MODULEUID function to narrow it down to the right reactor, and expand the time frame to see the batches of interest.


We see two unit batches:


Next, we need to be able to filter down by alias, which we know is related to the module, and obtain the underlying pi tag. Because of this, we can either expand the [pimodule] catalog and notice the [pialias] table, or we can open the "4-Module Database Statements.sql" file in the query compendium and see the eighth example:

-- Creates an alias for the "TestModule" version valid since today.

-- When creating aliases and properties, you should always specify the module unique ID and

-- effective date. For this purpose, PI OLEDB provides the "MODULEUID" and "MODULEEFFECTIVE" functions.

INSERT pimodule..pialias (moduleuid, moduleeffective, name, tag)

VALUES (MODULEUID('\TestModule'), MODULEEFFECTIVE(MODULEUID('\TestModule')), 'TestAlias', 'sinusoid')


This is again an INSERT query, and the MODULEEFFECTIVE function does not apply to us since we're using a SELECT query, but it still shows us how to interact with aliases.


We can expand the columns of the [pialias] table and use this list to join the table to our existing query. To demonstrate that we have the correct pi tag for the temperature, let's output it as well:


The last tables we need to join are the ones that will give us the min, max, and average temperature. Using the methods described earlier in the exercise, we can see that the [pimin], [pimax], and [piavg] tables can be used here. To ensure that we know how to query them, we can right click on [pimin] and execute the predefined query, changing the output to what we need:


At this point we have the batch we need, we know how to get the pi tag from the module alias, and we know how to query statistics against a tag for a time range. We can put it all together where the stats tables receive the pi tag from the alias and the time range from the unit batch (using '1001' here), and everything of note is added to the final SELECT statement:

1 person found this helpful