In this video we're going to provide a general overview of the PI SQL data
access server real-time query processing or PI SQL RTQP query engine in schema. Let's begin
by opening SQL commander. In this video we're going to focus on the NuGreen database which
is an example database provided by OSIsoft for training purposes. Let's begin by opening the
query compendium for the PI SQL client. In the queries folder we see tutorial dot SQL and we
also see data model dot JPEG. Data model dot JPEG is a flow chart that describes the schema
in great detail and how all the different tables are related. I encourage you to pause
the video now and look through this as much as you would like. Now let's look at the tutorial.
The tutorial SQL file contains queries that are run against the NuGreen database. The NuGreen
database is an AF database provided by OSIsoft for training purposes. If your development server
does not already have the NuGreen database I recommend importing it now. Before we can
begin we need to create a connection to the NuGreen database, let's right-click on PI SQL
client and connect. Type in the name of the AF server and the name of the AF database.
Now let's set the connection in this query window to the newly created RTQP connection,
and let's run the first query. Here we see a list of all sixteen elements that are based on
the boiler template across the entire AF database; if you recall from the PI OLEDB enterprise query
schema the template name is not included in the element table. The element template will simply
include the element template ID and the element template table which contain the name of the
template would need to be joined in order to add the where clause for the template name. While PI
OLEDB enterprise's schema is normalised the RTQP schema is not; this makes for simpler queries
and fewer joints. Let's look at some other queries that are contained in this tutorial.
For example this query returns sample values for all attributes on this element. As you can see we
have hourly values for each of the attributes on this element. But I want to point out in specific
is that the attribute table itself contains the information for the element name. This
also is not the case at PI OLEDB enterprise; in order for it the attribute values to be returned
with the element name as the filter multiple joins had to exist. In this data model simply
the attribute table needs to be cross applied with the get sample value table value function.
Let's expand the master schema and take a look at some of the other items. As I mentioned before the
RTQP schema is denormalized so when we expand out the tables and table value functions we
see substantially fewer than we did in PI OLEDB enterprise. Each of the tables can be expanded
to see the columns list to see which items are contained within the table. I encourage you to
go through the rest of the tutorial file and play around with the different tables in the
master schema. For now let's move on to creating a custom database object. In PI OLEDB enterprise
this was constrained to views and transpose table value functions. The RTQP query engine
has a much larger range of customization. Let's right-click on catalogs and create
template specific data model. Let's give our custom catalog a name and create a new schema
for the equipment. In this schema we'll put things like boilers, heaters, compressors etc.
We'll start with the boiler template here we have the option to create different types of
database objects so let's begin with a view. We'll have the attributes we're interested in
to the right side of the view. We'll keep the timestamp unit of measure and error although
those are optional columns. We'll click OK; we'll repeat the process for a get sample
value as well as get summaries. We'll click Next and before moving on let's copy what we see here
in this screen into a notepad file. This file contains the query execution for creating all
of these functions and views. We'll click Next and execute. Let's execute our view to see all
the different values that we were interested in for the boiler template. You see that the view
we created is automatically in the transpose format; it also provides units of measure as
well as the values. Now let's filter this down to just the fuel gas flow and execute the query.
Now let's explore the template folder within the master catalog. Here we see value. Let's
right click on this template, script, create view, new query window. Let's get the view
name and put it to our newly created catalog as well as our equipment schema. Let's put
in boiler m/s change to template to boiler. Now let's return to the notepad file to make
sure that we get the rest of the items correct. We're interested in the fuel gas flow so let's
grab those items. Let's change the desired UOM to liters per second. Let's remove the rest of
the items since this is the only attribute we're interested in at the moment. Let's
execute the query to create the view. Now let's refresh our views and the equipment
schema and execute the liters per second version. Let's change a select statement
to match select output of the other query. Now let's compare the results to the previous query.
Here we see a value of 99.8 in feet cube per second and here we see 2827 for liters per
second. As I look through the notepad file we see that not only was the view
created like this but also all the functions. I encourage you to use the wizard to generate
an output file such as this and then use the templates folder to try to make some on your
own. If you'd like to use the template in this folder however you did not save
the output from the notepad file and you would like to look at an example before continuing,
simply right-click on any of your existing views go to scripts, create view, new query
window. This gives you the definition of the view and it's exactly what we saw in a notepad file.
For example we could use this technique to create a new view for the heater template. Let's do
that now. We'll look at the heater template and see what attributes it has. It has asset name,
fuel gas flow and process the new process feed rate or water flow. Let's change the template
on this script to heater and remove the last two items. Change the name to heater and we'll execute
the creation. Great, now let's refresh the views and execute the heater view. We see that we have
all of our heaters based on the heater template and we have the asset name, the fuel gas flow
and the process name. If we later decide we want to add another attribute, let's say model we can
do that easily by right-clicking on heater, going to scripts alter view, new query window. Copy what
we have here and change it to manufacturer. And execute the query; now let's refresh our view.
Here we see in addition to the asset name, fuel gas flow and process name,
we now have the manufacturer name. We can further customize the schema by right-clicking on a schema
name going to scripts and create the view, function or function table, which provides
you with a header to start your creation of your own function. Further at the catalog level there's
also a script to create a new schema. Let's do that now and let's refresh the catalog. I'm
going to further build out the schema according to the NuGreen database. Now refresh my catalog
and show all the different views that I was able to create in my custom schema.
A highly customizable schema over the RTQP query engine should hopefully result in better logical
organization of database objects for those who are using them and building the PI SQL queries.
This concludes the video on the general overview of the schema for the PI SQL DAS
RTQP or PI SQL data access server real-time query processor. Thanks for watching!