Hi and welcome back to the PI SQL online class. In this video we're
going to install, configure and test PI OLEDB provider and PI OLEDB enterprise.
Let's begin by running the installation kit for PI OLEDB enterprise 2017 R2, you can obtain this
from the tech support download site if you don't already have it. Gonna right-click on the file and
select run as administrator. As you can see from the installation tip PI OLEDB provider, PI OLEDB
enterprise and PI SQL commander lite are part of this install. We'll click OK and I'll skip ahead
of the video to the finish of the installation. Great the installation completed successfully.
So now let's go ahead and open up PI SQL commander. PI table commander is
an application that's developed to appear like SSMS, SQL server management studio and
to provide basic functionality for PI SQL queries. If we open the application we can
see the object Explorer on the left shows OLEDB data sources, PI OLEDB enterprise and PI OLEDB
provider. Now the PI OLEDB enterprise will set your AF server and your PI OLEDB provider
will set your data archive. Let's start by investigating how these servers appear in
the list. Let's open up about PI SDK and let's show the known servers table. Here we have just
PISRV01 which matches what we have here. Now if I were to add another server, let's call it dummy,
it's a dummy server and I'll uncheck confirm that way it'll let me add it. Now a dummy is in my KST
so if I right-click on PI OLEDB provider and select refresh we now see dummy in the list.
We don't actually want this server though so let's go ahead and remove it. Okay so now to
connect to the PI data archive let's right-click on the server name and select connect. Let's not
worry about this screen just yet so we'll click OK and we see a bunch of catalogs that are things
like PI archives, PI batch etc. We'll go through the whole schema in a later video so for now let's
just make sure that we have basic functionality and that our queries can work. So let's go through
PI archive and open up the tables and we'll do an interpolated value query so we'll look at
PI interp two and on any of these tables if you right click on them and say execute predefined
query it will execute a basic query that's usually using a tag such as sinusoid and we'll see that we
get values. So these are interpolated values for the last hour and now for sinusoid. So
the fact that this returned values means that our installation is successful for PI OLEDB provider.
Next let's create a view to confirm that the basic functionality of creating database objects
is valid. Let's first copy this query to the clipboard and go down right click on views
create view. Let's paste in this query that we had before, name this test view. Execute that,
command completed successfully and now let's go down to views right click refresh. Now we
see test view. Let's right-click on that execute predefined query. Here we see the same values as
before but we're executing this time test view as opposed to the PI interp two explicit query. Let's
explore where that was saved. So close out about PI SDK let's open up PI system management tools
let's go to operation module database, we'll drill down to see PI SQL, catalogs, PI archive, views,
test view, properties and under definition we see the query that we had in the Select statement.
So what this means is that the query is being saved in the module database and therefore
it's on the server. Every client that's using PI OLEDB provider would have access to this data and
therefore to the view configuration. So if you create the view on one machine and refresh your
views on another machine you should see the view appear. Great now this is working let's go ahead
and close it out and let's show one final test to make sure everything works the way we expect it
to. We'll go back to our PI interp two query and we see that we have a time step of one hour and
if we change this to one day for the time range, will see that our time step is still one hour.
Our times are all increasing by one hour and we have our time step column over here. Now what if
we want to specify a time step of something other than an hour, we can add that to the
query but we can also do it from the connection properties itself. So let's open up the user guide
for PI OLEDB provider which you can find in the installation folder. So let's go to PI home OLEDB,
doc and then let's open up PI OLEDB provider 2017 r2 user guide. Scroll down to the table
of contents and let's look for supported OLEDB initialization properties this is a
list of all the initialization properties you can provide when connecting to the PI
OLEDB provider. I'm going to show an example of using one that's called default time step. I'd
encourage you to read through this guidance to see all the different properties that
you can use on your own while I show the functionality of using default time step.
So let's go back to PI OLEDB provider and PI SQL commander let's right-click and disconnect.
Let's reconnect . So in this screen that I glossed over before let's click options and
this brings us to a list of options that we can specify. Under extended properties this
is where you would put something in that is not one of the already defined boxes. So I'm
going to put in default time step equals 6h. So now if I do not specify a time step in my
interpolated queries I'm going to use a time step of six hours. So let's click
OK and let's just rerun this query. Notice now that all my time steps are six hours;
this concludes the testing of the PI OLEDB provider portion of the installation.
So let's go ahead and disconnect from the data archive and let's move on to PI OLEDB enterprise
and let's connect to our AF server. Now I suggest using PI System Explorer when writing PI OLEDB
enterprise queries because PI System Explorer lets you visualize the hierarchy and see your elements
and attributes and their hierarchical manner while writing your queries. So we're going to start with
connecting just to database 1 which is a very simple database made just for this tutorial.
It has one element called test element, it is a template, it has two attributes the value of 1 and
a value 2 for test attribute one, test attribute two. So when we connect to database 1 in PI SQL
commander we see four different schemas listed: asset data, dataT, event frame we'll go over the
schemas in a future video, so for now let's just explore asset, table, then let's do element. Right
click execute predefined query and we should see one element returned with the name test element.
If we look this is what we have for the test element so that is a correct functionality. We
confirmed that the PI OLEDB enterprise query is returning what it should in this case.
So now let's do the same thing with the view that we did for PI OLEDB provider and let's
copy this query to the clipboard, let's go down to views, let's create a view, paste the query,
name this "TestAFView". Let's execute it and confirm that command completed successfully.
Now to show you where the view was just created let's go back into PI System Explorer and we'll
connect to the configuration database. Explorer OSIsoft PI SQL database objects, database one,
asset, "TestAFView". Under general we can look at the extended properties and we can see the query
that we pasted in. This is our view and as this is stored server side just like it is in PI OLEDB
provider any client can access this view once it's been created by one client. All they have to do is
refresh their views. So let's do that now. Let's refresh our list of views and we see "TestAFView".
Execute predefined query and here we have the exact same results. This was a successful test.
One final thing I wanted to show is the data flow that I went over in a previous video,
like to show how that corresponds with PI OLEDB enterprise agent. Let's open up services dot MSC
and we see PI OLEDB enterprise agent. If we were to stop this service and try to execute
this query again it would fail: cannot connect to PI OLEDB enterprise agent. This makes sense
because dataflow is running through the PI OLEDB enterprise agent service. Now I mentioned the
previous video that we can have the option of not doing that and having it go directly to the
AF server. I'd like to demonstrate that. Let's right click disconnect, right click connect this
time under options and extended properties let's type in use agent equals false. Let's
click connect. You're connected successfully. Now let's refresh the query and we see that we have
our results. We can confirm that the service is still stopped. This proves that we are
successfully able to use or not use the PI OLEDB enterprise agent when using PI SQL commander.
This concludes the video on installation,
configuration and testing of PI OLEDB enterprise and provider.