When querying AF attributes in PI SQL, it can be useful to transpose the result table such that each column is an attribute value, and each row is an element or event frame (or timestamp in the case of interpolated ranges). The following two queries output ten boiler elements and their Water Flow, Fuel Savings, and Process Feedrate snapshot values. The non-transposed output is 30 rows and needs a column to show the attribute name for each row. The transposed output only needs 10 rows, and the columns are named for the attributes. This style of output is more compact, easier to read, and more easily consumed by external applications.
This guide will show how to create a snapshot transpose function for the NuGreen boiler template using either of the RTQP or PI OLEDB Enterprise query engines.
With the RTQP Query Engine, there are two options for creating these transpose functions.
- Ad-hoc function
- Permanent schema object (view)
The ad-hoc approach is covered by:
- Not authorized to view the specified discussion 2664 starting at 5:00
- PI SQL Commander's Query Compendium under PI SQL Client > Queries > Element > 3.TemplateSpecificData.sql
- PI SQL Commander's Object Explorer under PI SQL Client > [AFServer\Database] > Catalogs > Master > Element > Templates > [Right click on any of them] > Execute Predefined Query
This guide, however, will focus on the creation of a permanent schema object, which in the case of snapshot values is handled by a view.
In PI SQL Commander's Object Explorer, right click on the Element schema, and select "Create Template Specific Data Model".
In the Template-Specific Data Model Wizard, select "Element" and "Boiler"
We want to make a snapshot transpose function, and the snapshot values of all the attributes are part of the element view, so we'll select "Add Element View". From the next screen, we can highlight any of the attributes in the list and hit Ctrl+A to select all of them, then drag them onto the panel on the right. This will add all of the Boiler attributes to the element view.
Next, we'll click Execute and wait for the creation scripts to run successfully:
In the Object Explorer, we can refresh our Views folder to see the view we just created:
We can right click on this view and select "Execute Predefined Query":
In the original example, we only wanted to see ten elements with three attributes each, so we can adjust the SELECT statement accordingly:
Alternatively, if the PI OLEDB Enterprise query engine is being used, the transpose function (either table-valued function or function table) can be added to the DataT schema. Note: there is no ad-hoc option for transpose queries in PI OLEDB Enterprise.
In PI SQL Commander, right click on navigate to PI OLEDB Enterprise > [AF Server Name] > Catalogs > [AF Database Name] > DataT, and select New Transpose Function > Dynamic Transpose Function (Asset).
In the transpose function wizard, select "Transpose Snapshot" then "Boiler":
Change the default name if you'd like, and select the base attribute path since we're not querying any child attributes in this transpose function:
If you would like to also create a function table, this is where you would enable this. As a general rule, I would suggest creating it so that you have in case you ever need it in the future. Also, you can select to send all values to the client as a variant data type instead of the data types of the attributes; this comes in handy when using a client that does not support certain datatypes, such as GUID.
Execute the creation scripts, and wait for it to finish:
Refreshing the Table-Valued Functions (and Function Tables if that was enabled) folders under DataT will now show the function that was just created:
To execute it, right click on the table valued function and execute the predefined query:
In the intial query, we were only interested in ten boiler elements and three of their attributes. We can adjust this query's SELECT statement to replicate that output: