Hi and welcome back to the PI SQL online course. This video is going to be all about
transpose functions. We're going to cover what they are, why to use them and how to
make them in PI OLEDB enterprise. Let's begin by opening up PI SQL commander.
Let's begin by connecting to the AF server under the PI OLEDB enterprise data sources. Let's start
by writing a query that gets every element based on the boiler template. The element template name
can be found from the element template table so let's execute a predefined query. This is
the template we're interested in so now we know we have the right table. Next let's
join the elements table. Our join condition can be found by looking at the columns. Here we see
that element template has an ID column and element has an element template ID column.
Let's use those in the join. Finally, let's add a where clause to filter down for just boiler.
Great, we returned 16 rows and it contains all the different elements based on the
boiler template. Finally let's get the attribute values of every attribute on
all of these elements. To do this we'll join the element attribute table as well as the
data dot snapshot table. The basis for this query can be found by right-clicking on the
snapshot table under the data schema and executing a predefined query. Let's grab
the bottom half of this query and add it to our existing. Predefined query was using the
element hierarchy table but we're using element table so that we can get the element template
ID. Let's change the join condition to match that of the element table. Now let's change
the select to just get the information that we want. Let's execute this query.
Here we see all the snapshot times and snapshot values of the attributes on
each of these elements. For a total of three hundred and thirty six rows. This is found
because we have twenty-one attributes on each of the elements. There's 21 rows for
B-499 then the next 21 rows are for B-209. As you scroll through the results that we can see
that these values are all there however the format of the output is not very digestible.
Since all of these elements follow the same template and have all of the same attributes
we should be able to view them on 16 rows with 21 different columns, one for each attribute . Tis
is a purpose of a transpose function; let's create one now and see what it looks like.
As I mentioned in a previous video the dataT schema is blank by default. To create a new
transpose function right click on dataT, new transpose function. As seen in the menu the two
options are dynamic or custom and asset or event frame. Asset corresponds to element templates
whereas event frame corresponds to event frame templates. Dynamic vs. custom is the difference
between all attributes created from the template which is dynamic versus being able to specify
which attributes you would like to add to the transpose function output, which is custom. We'll
start by doing dynamic. We'll create a snapshot transpose function and select the boiler template.
All of our attributes are root level attributes so in this menu we'll select just the route path for
the attribute. Selecting values as variant will cause the transpose function to return variants
as the data type for all the attributes and create function table will create the function
table as well as table value function. Some SQL applications are not compatible with functions,
therefore the function table will be necessary to run the query in those applications. We recommend
creating the function table in addition to table value function just in case you ever need it.
Let's click Next and execute the query. Great, now let's refresh both their function tables
and our table value functions. Notice that we now see the boiler. In a new query let's
execute the transpose snapshot function. As we can see the output has 16 rows; one for each
element and all the columns are the different attribute values. So if we scroll through,
we can see that all the attribute values are there and that the previous query returns the
same data however in a much different format. This format is much easier to understand and
is also much easier to digest by other formats. This format is also easily turned into a CSV.
Next let's look at the difference between the function table and the table value function.
Notice in the table value function we cross applied the transpose function to the elements
that were found by filtering by element template type of boiler. With the function table we're
seeing the same output however we're joining it as if it were a table. Next let's create a custom
transpose function for the same template so that we can show some summary values. Let's right click
on dataT again, view transpose function, custom transpose function. Here we'll select transpose
summarized, because we want to obtain summary values. Let's select the boiler template again and
here we will drag the attributes on that we would like. Let's get fuel gas flow, process feed rate
and water flow. Let's obtain the average for fuel gas flow as well as process feed rate and the min,
max and standard deviation of water flow. We'll click Next and create the transpose function.
Now let's query the summarize table for the boiler. The predefined query uses a one-hour time
range with 30-minute time steps so we see two rows per element. Let's change this to only one row.
Notice here that we have 16 rows, one for each element and we have the average of both the fuel
gas flow and the process feed rate as well as the water flow min, max and standard deviation for
the last hour. Now just as a note the NuGreen database maps all the different attributes of
different elements to the same PI tag, that's why we see the same values here. They're all maps to
our built-in tags that way everything will work. On your systems the different elements should have
different values. Now what if we wanted one query output to have the five summary values
shown here in addition to the snapshot values we saw in the previous query. Let's create that now.
Let's copy each query into the corresponding portion of the combined query.
In both query outputs we can see that the element path and name has been provided.
This can be our join. In both cases it's provided as the name element which if we
were to have two of them would cause a naming conflict. So let's change that
now to element and element two. We'll create the join. One of our attributes
is pointing to the wrong server so let's fix that now. Let's run it again. Here we see sixteen rows,
one for each element and we have all of the different current values of all the attributes
and at the end we have all of the summary values provided from the summaries table.
Now this is a fairly robust query so if we're going to use this in a production
system it's probably best to create a view. Let's do that now. Under the dataT schema
let's right click on views and create view. Let's copy everything from the query window,
paste this for view definition and I'll change the view name and execute it. Rfresh our views and
execute the view itself. Now we have a two line query that produces one element on each row as
well as all the current values and summary values for the last hour for each of the attributes.
This concludes the video on transpose functions and I encourage you to repeat
this process using the event frame templates. Thanks for watching.