So the first step in this project will be to create the queries and the custom database
objects required to accomplish these. Let's begin by using PI SQL client to
connect to the NuGreen database. To keep everything organized nicely let's create
a catalogue for the guided project. We'll right click on catalog, create catalog and
call it guided project. Now let's create two schemas one called SSIS and one called SSRS.
Now let's create some of our custom database objects so that we can get the queries written
that we need to store the data in the SQL server. Let's right-click on the SSIS schema
and create template specific data model. In our data export let's grab data for the boilers,
compressors, pumps and heaters. I'll start with the boilers. We'll get an element view
so we can grab things like the plant name as well as the process name. As we build
out these custom database objects let's open up PI System Explorer so we can follow along
by looking at the AF hierarchy. We'll drill down to an example element under Houston,
cracking process, equipment, boiler 210. For each of these elements I'm interested in storing
15-minute intervals for the process feed rate value. Here we see it for the boiler and if
we move to a heater element we can see the same value. And finally pumps have this value as well.
When I later access the data on a SQL server I'd like to be able to slice by either location
or process type. So let's make sure those values are in the result set as well. Let's
go back to PI SQL commander and build the queries. Start with an element view so we
can get things like the asset name, plant name and process name. We'll uncheck air,
unit of measure and timestamp since these are just static values. Next do get sample
values so that we can return processed feed rate and we'll uncheck air and UOM since we
don't want to store those in a SQL database. We'll click Next and execute the query. If
we expand out the views in the table value functions, we see the two that we just created.
Let's query them and make sure that they have the data that we're looking for. We'll right-click on
a table value function and execute predefined query and we'll replace the first part of the
query with our view so that we can get the extra data. We want we don't need the template since
the guided project dot SSIS boiler view that we're using is already filtered by
boiler type. The default settings for this is a one-hour time step so let's changed that to
15 minutes and we'll click execute. You see we have over 1,500 rows which makes sense given the
amount of data that we're looking for. Now let's filter the select statement to just
that data that we want to store in the SQL server and we'll execute the query.
Okay now we see we start off with the B-555 element we have the plant name,
the process name and we have every 15 minutes the process feed rate value. If we scroll down
we should see the other elements as well. Here we see have changed from B-765 to B-125. We also see
that we have both Houston and Little Rock elements and we also have extruding plant and distilling
plant elements. Now the boiler is only one thing that we're interested in so let's continue and
make this for the other three element types. Now I could go through and create the other 3 views and
other three table value functions by using the wizard and selecting compressor, then heater,
then pump but instead I'm going to correct these other three by using the scripting. Let me right
click on this boiler, go to scripts, create view, new query window and I'll change boiler to heater.
I'll execute the query and repeat this for compressor and pump. I'll refresh my views
and confirm that they're all there. Now let's repeat the process for the get sampled values.
Change this to compressor as well as this. Execute it and repeat the process for pump
and heater. Now let's refresh these to confirm they're created, and let's execute them to
test that they work. Great now we're going to be doing four different writesto the SQL
server as part of our SSIS package so let's create those views now just to make sure that
everything is as clean as possible within the queries within the SSIS package. So I'll go back
to our original query. I'll save this as a view. And we'll do the same thing for the compressors,
pumps and heaters. Great let's refresh our views and confirm they're created. Let's run
the queries. Great the boiler's list returns what we want, as does the compressor list,
the heater list and the pump list. Now as part of the SSIS package we would like to
write data back to the PI server to validate that the SSIS package completed successfully.
Since the RTQP engine is read-only we'll need to use PI OLEDB provider for this. Let's make
a connection to our PI server using PI OLEDB provider and create the tag that we need.
Close out the query windows and we'll open up the query compendium. Let's look at point
database statements. We want to write a value of success this tag every time the SSIS package
completes so we need to make it a string tag. So let's take this statement and copy
it to a new query window. We'll name it SSIS package tracker. And we'll have it as type
string. Let's execute the query to create the tag. Great, point was successfully created.
Now let's go back to the query compendium and see how to write a value to it. Let's take this query
and copy it to the original query window. If we remove the questionable flag I'll put in the new
name of the tag, the time of now and a value of test. Let's execute this and let's confirm that
it worked. We can go to the query companion and grab one of the queries that queries the values
for a tag let's copy it to the original query window as well. Change it to use our tag name
and we'll execute this. We can see originally it had a value point created and now it has the value
of test that we wrote. Great so this query now works if we ever want to write a value to this
tag. Let's save this query file so we can use it again when we're making our SSIS package.
Finally, let's create our queries for SSRS. Close out these query windows as well as the query
companion. In the SSRS report we want to be able to specify a plant name and have a list of all the
elements separated by template. So let's create template specific data models starting off with
boiler. I'll create an element view that supplies the plant name. That way we can filter by it and
we'll get rid of timestamp, unit of measure and error since we're not interested in those for this
value. We'll create a summaries so we can look at the fuel gas flow and look at the minimum,
maximum, average and total across the last 24 hours. Total, average, minimum and maximum.
I'll go and check those boxes as well. Finally we're going to want to put everything together
into one query for all four of the element types so in order to make sure the column names match
we're going to change the value of the column name right here using utility. This will be consistent
across all template types, great now let's execute the query. I'll repeat the process from earlier,
I'm using the script to make all the other element types. I'll refresh the views to make sure
everything is created. Great and I'll do the same thing for the get summaries table value function,
I'm going to have to change the attribute on some of the elements. For the heaters
it's fuel gas flow as the attribute name so we can keep things the way they are.
However when we look in PSC we see that for pumps utility type is motor amps so
we need to change the attribute name to motor amps. Let's execute this query. I
will do the same thing for a compressor this time naming the utility attribute steam flow.
Great, we'll refresh the table value functions, want to check each one to make sure it works.
Great now build it into the form that we're looking for in the SSRS report. At the plant
name and we'll change this to the view that we just created. Execute this and confirm that we
have everything in the correct form. Finally let's add one more static string to the output
to show what type of equipment it is. Execute this query and confirm that it shows up. As I
mentioned before in SSRS it's helpful to have everything in one query so let's create one
more view that has all four of these outputs union together. I right click on views scripts
create view new query window I'll change this to utility usage report. I'll take this query
that I just created for boilers and I'll union it three times, each time changing the element type.
Finally I'll change all the time steps to be one day so that we can get our daily report.
Confirm that this view is created. And we'll execute it once to make sure that
everything shows up. Great, we have all the elements across our entire enterprise,
their equipment type and their utility, total, minimum, maximum and average across the last
day as well as what plant they are in. And since we'll be doing this in the exercise
report let's add a where clause for the plant name. Great here we see twenty five rows that
are returned for all the elements at the Houston plant, here are the 16 elements at Little Rock,
twenty five elements at Tucson and the 25 elements at Wichita.
This concludes the video upgrading all the custom database objects and queries
necessary to use in our packages. In the next videos we'll build out
our SSIS package as well as our SSRS reports, as well as our Microsoft SQL
server link server object that we will use to broker our rights to the data archive.