This document contains the answers to the Exercise: Making PI OLEDB Enterprise queries.
1. Generate a query that outputs the value of every attribute of Pump P-344 (NuGreen\Wichita\Distilling Process\Equipment\) yesterday at 10:00pm. You cannot use a table valued function in your final query.
SELECT eh.Name Element, ea.Name Attribute, i.Time, i.Value, i.Status FROM NuGreen.Asset.ElementHierarchy eh INNER JOIN NuGreen.Asset.ElementAttribute ea ON ea.ElementID = eh.ElementID, [NuGreen].[Data].[ft_InterpolateDiscrete] i WHERE eh.Path = N'\NuGreen\Wichita\Distilling Process\Equipment\' AND eh.Name = N'P-344' AND i.ElementAttributeID = ea.ID AND i.Time = N'y+22h'
Let's start by breaking this query down into three different parts:
- We need to access the attributes on a specific element
- We need to find an interpolated value for all attributes
- We cannot use table valued functions, and therefore need to use a function table
For the first part, we can use the Query Compendium's 1-CurrentElementHierarchy.sql (PI OLEDB Enterprise > Asset Database), specifically the 7th query:
This query outputs the attributes of every element under the Houston > Cracking Process > Equipment element:
From the SELECT statement, we see that eh.Name is outputting the name of the element, so we can use that in a WHERE clause to filter down by the specific element we're interested in:
For the second part, we can use the Query Compendium's 1-Data.sql (PI OLEDB Enterprise > Data), specifically the third query which shows us how to get the interpolated value for midnight using the Data.InterpolateDiscrete function.
We can combine this with our existing query for only the P-344 element, altering the timestamp to be 'y+22h' to get the interpolated value for yesterday at 10pm:
This satisfies the correct output, but it uses a function that must be CROSS APPLY'ed to get the results. This breaks the constraint of the application being used for the investigation, so we need to switch this to a function table (which only requires a JOIN in the query itself).
If we expand the Object Explorer for the NuGreen database, we see that there's a Function Table folder in parallel to the Table-Valued Functions folder. They both contain an interpolate discreet entry, which is what we used earlier:
If we right click on the ft_InterpolateDiscrete table and Execute Predefined Query, we can see how to use this table by passing it the arguments in the form of WHERE clauses instead of function arguments, and JOIN'ing it instead of CROSS APPLY'ing it:
Finally, we can alter this slightly as we combine it with the previous query to get the final form (removing i.ValueStr since we don't need the value as string):
2. 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 eh.Name Element, ea.Name Attribute, s.Time, CAST(UOMConvert(s.ValueDbl, ea.DefaultUOMID, UOMID(N'm3/s')) AS String) + N' m3/s' Value FROM NuGreen.Asset.ElementHierarchy eh INNER JOIN NuGreen.Asset.ElementAttribute ea ON ea.ElementID = eh.ElementID INNER JOIN NuGreen.Data.Snapshot s ON s.ElementAttributeID = ea.ID WHERE eh.Path LIKE N'\NuGreen\Little Rock\% Process\Equipment\' AND eh.Name LIKE N'K-%' AND ea.Name = N'Steam Flow' OPTION (FORCE ORDER)
This query is easily done by reading through the Query compendium (and the purpose of this is to reinforce the idea of looking through the compendium when developing queries). Navigating to Data > 3-UOMConversions.sql, we see the second query as follows:
This demonstrates how to use the built-in UOMConvert() function as well as navigating through the element hierarchy. Since we're looking for all elements in Little Rock, we can explore the hierarchy in PI System Explorer to determine the best way to filter it down. From here we see that there's two different processes under Little Rock, both named "<something> Process" and they each have compressors starting with "K-".
We can edit the query to find any element starting with K- and located under either Process with the following query, as well as filtering down to just the Steam Flow instead of any Flow attribute. Finally, we can change the UOM conversion and value output to m3/s instead of the query compendium's ft3/s:
To prove that the values were in fact adjusted to the new unit, we can output the original value and the new value in side by side columns:
And using google's unit conversion, we can confirm that the AF unit conversion produced the correct result:
3. Generate a query that outputs the average Fuel Gas Flow for yesterday for all heaters in the NuGreen database, ordering them by element name.
SELECT e.Name as [heater] , CAST(s.value as string) + ' ' + UOMAbbreviation(ea.DefaultUOMID) as [average fuel gas flow] FROM NuGreen.Asset.ElementTemplate et INNER JOIN NuGreen.Asset.ElementTemplateAttribute eta ON eta.ElementTemplateID = et.ID INNER JOIN NuGreen.Asset.ElementAttribute ea ON ea.ElementTemplateAttributeID = eta.ID INNER JOIN NuGreen.Asset.Element e ON e.ID = ea.ElementID CROSS APPLY [NuGreen].[Data].[Summarize] ( ea.ID, -- ElementAttributeID N'y', -- StartTime N't', -- EndTime N'1d', -- TimeStep N'Average', -- Summary Type N'TimeWeighted', -- Calculation Basis N'MostRecentTime' -- Time Type ) s WHERE et.Name = N'Heater' AND eta.Name = N'Fuel Gas Flow' ORDER BY e.Name OPTION (FORCE ORDER, EMBED ERRORS)
To build this query, let's start with the aspect of hitting all heaters in the database. This brings us to the query compendium's 3-ElementTemplates.sql (PI OLEDB Enterprise > Asset Database), specifically the 5th query
This shows us how to get the Motor Amps attribute on every element with the template of 'Pump'. We can easily change this to 'Fuel Gas Flow' and 'Heater', respectively.
Next, we need to get the summarized data for these attributes, namely the average. To use the summary, we can either look at the query compendium's 1-Data.sql's sixth query showing Averages, mins, and maxes, or we can execute the predefined query for the NuGreen > Data > TVF > Summarize function. Both routes should typically be explored, and in this case the predefined query is more verbose in terms of comments, so we'll use that.
From here we can see it building a table of summary types, which we only 'Average' from, and the Summarize parameters are commented, making it easy to replace them later.
Given the two queries we've found up to this point, we can combine them and edit them to get the query that we're looking for:
4 . Generate a query that outputs a list of units that, yesterday alone, 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 e.Name Unit, count(*) as [total] FROM NuGreen.EventFrame.EventFrameTemplate eft INNER JOIN NuGreen.EventFrame.EventFrame ef ON ef.EventFrameTemplateID = eft.ID INNER JOIN NuGreen.Asset.Element e ON e.ID = ef.PrimaryReferencedElementID WHERE eft.Name = N'OSIUnitProcedure' AND ef.StartTime BETWEEN 'y' AND 't' AND (ef.EndTime - ef.StartTime) > '30m' GROUP BY e.Name HAVING count(*) > 2 ORDER BY count(*) DESC OPTION (FORCE ORDER)
This query switches the context of the exercise to event frames, which is a very powerful tool for provided context and aggregated stats on the equipment and events at a plant. To begin to write this query, let's look at the 1-EventFrames.sql file in the query compendium (PI OLEDB Enterprise > Event Frames). We'll need to be able to query:
- the event frames by template name
- the name of the associated element
- the duration.
For the first one, we can find the ninth query in the file, "--Unit procedures which started yesterday":
Here we can see how to filter by EF template name, which is already set to the right string, as well as the time period.
From here we can either do the math ourselves in the WHERE clause to filter out the short duration (less than 30m) EF's, and also output it in the SELECT statement to confirm that we're on the right track:
The next portion of the query will need to output the name of the element. We can either use the object explorer's column list of the EventFrame table to find the most likely JOIN candidate for getting the element name, which in this case is PrimaryReferencedElementID:
or we can further explore the query compendium file looking for a query that outputs this information, such as the one towards the end:
With either approach, we know to join either the Element or ElementHierarchy tables by the PrimaryReferencedElementID of the event frame. Since we're not filtering down by the element hierarchy in this example, I elected to use the Element table:
To finish the request, we need to aggregate the results by unit type, find the count, and remove any unit with only 1 or 2 long duration event frames. All of these use general SQL to accomplish:
5. 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 NuGreen.EventFrame.FouledReactors)
CREATE VIEW [NuGreen].[EventFrame].[FouledReactors] AS SELECT e.Name Unit, count(*) as [total] FROM NuGreen.EventFrame.EventFrameTemplate eft INNER JOIN NuGreen.EventFrame.EventFrame ef ON ef.EventFrameTemplateID = eft.ID INNER JOIN NuGreen.Asset.Element e ON e.ID = ef.PrimaryReferencedElementID WHERE eft.Name = N'OSIUnitProcedure' AND ef.StartTime BETWEEN 'y' AND 't' AND (ef.EndTime - ef.StartTime) > '30m' GROUP BY e.Name HAVING count(*) > 2 ORDER BY count(*) DESC OPTION (FORCE ORDER)
Given the highly normalized nature of PI OLEDB Enterprise, queries can become very verbose. Just like other relational databases, it's a good practice to create views for queries being run regularly in an application, that way the SQL DBA can alter them as necessary without the application needing to change its query, and the query being executed is simple and easy to understand (if descriptively named).
In our case, we start with the query from the previous step:
Next, we navigate in the Object Explorer to NuGreen > EventFrame > Views, right click on Views, and select Create View:
In the query window that came up, we paste in the query from the previous step and we can change the name to something descriptive like "FouledReactors". When we execute this, it should complete successfully:
And we can refresh the EventFrame Views (right click on Views > Refresh) to see our newly created View:
As a reminder: this view is stored in the Configuration database in AF, so it will be immediately accessible to any PI OLEDB Enterprise client or any machine following a refresh.
We can either run SELECT * FROM <viewname> or we can right click on the view and select "Execute Predefined Query". Either way, we should see the same result as earlier, but a much simpler query: