Solution: Writing PI OLEDB Enterprise Transpose Queries

Document created by kduffy on Sep 26, 2018Last modified by jkim on Nov 22, 2018
Version 6Show Document
  • View in full screen mode

This document contains the answers to Exercise: Writing PI OLEDB Enterprise Transpose Queries .


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.

SELECT t1.Path + t1.Element Element, t2.Plant, t1.Time
, t1.[Process Feedrate Minimum], t1.[Process Feedrate Maximum], t1.[Process Feedrate Total]
, t2.[Quality Average] [Plant Quality], t2.[Reliability Average] [Plant Reliability]
SELECT eh.Path Path, eh.Name Element, tid.*
FROM [NuGreen].[Asset].[ElementTemplate] et
INNER JOIN [NuGreen].[Asset].[Element] e
ON et.ID = e.ElementTemplateID
INNER JOIN [NuGreen].[Asset].[ElementHierarchy] eh
ON e.ID = eh.ElementID
CROSS APPLY [NuGreen].DataT.[TransposeSummarize_Unit](eh.ElementID,N'y',N't',N'1d',N'MostRecentTime') tid
WHERE et.Name in (N'Boiler', N'Pump', N'Heater', N'Compressor', N'Cooling Fan')
SELECT eh.Path + eh.Name Element, eh.Name Plant, tid.*
FROM (SELECT DATE(N't') [Time]) t,
[NuGreen].[Asset].[ElementTemplate] et
INNER JOIN [NuGreen].[Asset].[Element] e
ON et.ID = e.ElementTemplateID
INNER JOIN [NuGreen].[Asset].[ElementHierarchy] eh
ON e.ID = eh.ElementID
CROSS APPLY [NuGreen].DataT.[TransposeSummarize_Plant](eh.ElementID,N'y',N't',N'1d',N'MostRecentTime') tid
WHERE et.Name = N'Plant'
)t2 ON t1.Path LIKE (t2.Element + '%')


The simple use cases of transpose functions are covered in the video and the tutorials, so we're starting this exercise with a fairly complex example. When breaking down the requirements of the query, these ideas stick out:

  • We need to find the min, max, and total for Process Feedrate for every piece of equipment in NuGreen
  • We need to find the average uptime and quality for each plant
  • We need to combine the two, correctly tying the plant statistics to the equipment statistics.


For the first two, we're going to make Custom Transpose Function (Asset) functions as this allows us to use the summarize type:


We know to use transpose functions here because we need to put multiple pieces of information from the same element onto the same row in the output (and also the fact that this is the transpose query exercise). InNot authorized to view the specified discussion 2662 we saw the usage of the summarize type, but we could also come to this conclusion by seeing the use of [TransposeSummarize_Boiler] in the query compendium's 1-TransposedData.sql (PI OLEDB Enterprise > Queries > Transposed Data) file's "--Boiler attribute data summaries" example.


For the first transpose summarize function, we're trying to query every piece of equipment for its Process Feedrate, but the different equipment types are different templates (boiler, heater, etc). In PI System Explorer, however, we see that each of these equipment template types derive from the Unit template, which has the Process Feedrate attribute:


This means that we can create a transpose function against the "Unit" template, and query all of the derived templates against that. This is also a good use case for have clear template hierarchies with shared attributes. Since there's a business case to be querying this common attribute across all equipment types, it's meaningful for each piece of equipment, and should, therefore, be on the base template. We didn't get lucky, per se, that this was possible to be queried easily, this is a product of the template hierarchies being built in a normalized fashion, making queries like this easier.


Back in PI SQL Commander's Transpose Function Wizard, we can select the custom function (asset) and Summarize (non-versioned), then we drag the Process Feedrate attribute into the Attribute list three times, selecting each summary type once:


After the creation of the function, we can navigate to it in the Object Explorer (NuGreen > Table-Valued Functions > TranposeSummarize_Unit) and execute its predefined function. Nothing will come up at first since it's filtering by template name of Unit, but this is an abstract template with no element built directly off of it. Since the equipment types we're looking for all derive from Unit, though, it's safe to enumerate them in the template name filter, as follows:


We also see that it found 116 different pieces of equipment, which is correct if you were to add up all the pumps, boilers, heaters, compressors, and cooling fans in the NuGreen database. It's a good practice to pause at each step and make sure the output makes sense.


For the next transpose function, we need to follow the same steps, but for the plant template, and drag just the quality and reliability attributes in the attribute list:


We create the function and test it in the same way, this time keeping the predefined query as is (with the exception of the time ranges) because the plant template is the final template type:


There are only four plants, so the four rows are the correct output.


Finally, the last piece of this is to combine the two outputs into one query. We could try to do this from the element hierarchy tables that are feeding two transpose functions, but since there are two functions with different elements, we would still need to have two instances of the element hierarchy table joined together. Instead, a much cleaner way to do this, from a query writing perspective, would be to join the two outputs together into one query.


The first output is showing the element path, and the second output is showing a subset of this path. We can join the two outputs where the second table's path is a subset of the first table's path. This is again not a lucky occurrence but is, in fact, a result of proper element hierarchy building. If you would like to apply certain attributes at the plant level to each piece of equipment then it helps to have built the hierarchy in this manner where the plant is always the great-grandparent element of the equipment. If this approach didn't make sense in real life, then the query we're building wouldn't make the most sense. And finally, if there are multiple ways that this information should be grouped, then parallel hierarchies in AF could be built to reflect this, again moving the burden of simplicity onto the AF Server admin and not on the PI OLEDB Enterprise query developer.


Using general SQL techniques, we can do the combination:


Finally, the last step would be to rename the columns in the output to be more descriptive and not repeat the same data from each of the two combined tables:


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

There are a very large number of ways to handle this, such as using an SSIS package to direct the output of an OLEDB query to a csv file; using Powershell to parse through an OLEDB recordset writing it to a csv file; copying the output in SQL Commander, pasting it into Excel, and saving it as a csv; and many others.


For manually running queries and directing the output to a csv file, PI SQL Commander can do this right from the application. Navigate to Query > Result To and select Result to File


Execute the query again, and it will prompt you with a Save As dialog box. Once completed, the PI SQL Commander will show that the output was finished

And the file will be created where specified: