The goal of this video is to install, configure and test the PI ODBC driver.
We'll start by installing the necessary software. In a previous video we went over the data flow of
the PI ODBC driver to show that PI ODBC driver needs to be installed in the client machine and
it will talk to the PI SQL data access server which that will use PI OLEDB either provider or
enterprise to talk to the PI System. We're going to put everything in the same machine so we're
going to start by installing PI SQL DAS 2016 r2 you can obtain this from the tech support
download site. Once you have the install kit right click and run as administrator.
Great now the PI SQL data access server is installed let's go ahead and install
PI ODBC 2016 r2 same way right click run as administrator. Great this completed
successfully as well. Let's go ahead and open up PI SQL commander lite. Now that we
have PI ODBC driver installed over on the object Explorer on the left hand side we see ODBC data
sources. However there's nothing populated there. That's because this is crawling the
list of all DSM's on the operating system that are using PI ODBC driver as the ODBC
driver. So let's go ahead and create some of those. We're going to start menu and type in
ODBC. You see ODBC data source is 32-bit and 64-bit. The 32-bit will allow you to create
and edit 32-bit data sources and the 64-bit will do the same for 64 bit. We'll start with 32-bit.
We'll create a system DSN and click Add. I'm gonna find PI ODBC driver in the list. We'll give it a
name 32-bit PI server, we'll specify the name of our PI SQL data access server. Now remember when
I said everything is all on the same machine so the name of the PI SQL data access server
is the name of this machine, PISRV01, trusted connection means that we're going to use our
windows authentication for the person running the application and in datasource we'll select the
type of PI OLEDB provider for now in our first DSN. This is where I mentioned that PI SQL data
access server can use either PI OLEDB provider or PI OLEDB enterprise queries. The service
itself can use either provider, however each individual ODBC connection can only use one. So
we're going to select PI OLEDB provider for this one and type in for our PI data archive PSRV01,
initial catalog PI archive which we'll go over the schema of PI OLEDB provider in a
future video but for now just type in PI archive and let's keep integrated secured equals SSPI
as this uses Windows syndication. So we'll test the connection we'll see that it works.
So now we've created our 32-bit PI ODBC driver DSN for the PI data archive. Now if we go back
into PI SQL commander lite and we refresh ODBC data sources we still don't see anything. This
is interesting, we just created a DSM and yet we don't see it in our list of ODBC data
sources despite where I said this is a list of all the DSNs. The reason for this is that
we currently have open PI SQL commander lite 64-bit but we created a 32-bit DSN
so let's go into Start menu and let's open up 32-bit version of PI SQL commander lite. Sure
enough under ODBC data sources we now see 32 bit PI server. Same DSN we created here.
So now let's do the same thing with a 64-bit and prove that it shows up in the 64-bit version.
Test connection again to show that it's successful, click okay and now we have two
different ODBC DSNs for the PI ODBC driver both of which are pointing to the PI data
archive directly using PI OLEDB provider one is 32-bit one and 64-bit. Now if we open up again
our 64-bit version and refresh our ODBC data sources we'll see 64-bit PI server. Now let's
start querying the PI data archive through this PI ODBC DSN. We'll right-click and connect and
we see the same schema we saw before, we'll go over this in a future video I just want to show
that the functionality is still the same. Under PI archive let's do the PI interp2 queries again.
We see they are returning values. We can even go down in to views right click on test view,
execute predefined query and we have a successful data flow. Now let's continue
with AF queries using PI OLEDB enterprise as the data source for the PI ODBC DSN. So let's open
back up ODBC data source administrator 32-bit system DSN, let's add ODBC driver again this
time do 32-bit AF server. PI SQL data access server is still this machine so we'll keep
that the same and here we'll select a PI OELDB enterprise from the drop-down and type in the
name of our AF server. We'lll put system in initial catalog again we'll go over this in
a future video and we'll keep the options as integrated security equals SSPI. Test
the connection and show that it succeeded. Click OK and now we have a 32 bit AF server ODBC DSN.
Just a reminder the bit-ness is important here because the application must be the same
bit-ness as the ODBC DSN that's being used, this is all handled behind the scenes on the
operating system level but since you as a PI SQL developer will be the one creating the DSM's they
have to exist as the correct bit-ness this is why it's very important to make sure that you
created either the correct bit-ness or just one of each bit-ness. So to open up PI SQL commander
lite 32-bit so that we can use our 32-bit AF server ODBC DSN. In the object Explorer you
can see now that we have our 32-bit AF server DSN. Let's go ahead right click and connect let
me see the same schema that we saw before, these are AF databases so go into database one asset,
tables and our element query should work just like it did before. As well as our views.
Great everything is successfully working and to prove the data flow between PI ODBC and PI OLEDB
I'm going to stop the PI SQL data access server service. I'm going to try to execute these same
queries as a PI ODBC driver data source and a PI OLEDB provider or enterprise data source.
So here let's try to execute predefined query and we get an error message that says could not
connect to our PI SQL data access server. Let's connect to the PI OLEDB enterprise AF server and
run the same query directly from there. That query works so this proves that the PI ODBC
driver is routing all of its connections through the PI SQL data access server,
then the PI SQL data access server is what's using PI OLEDB provider or PI OLEDB enterprise
to communicate to the PI System. If we're not using ODBC in this case we can just use PI
OLEDB provider enterprise directly and those queries still work. But since we've stopped
the PI SQL data access server service the ODBC connections have no way of reaching the PI System.
One final thing I want to cover in this video is how to use the initialization properties
that we showed in the previous video about PI OLEDB provider from within the PI ODBC
driver connection. So let's go into ODBC data sources again. Let's create another 32-bit PI
server DSN this time let's callit 32-bit PI server default time step. Let's use the same
server for the PI SQL data access server, let's select PI OLEDB provider type in the PI server
name. This is PI archive again keep integrated security with SSPI correct and now under options
under the provider section this is where we put in the initialization properties. So you can do
something like default time step equals 6h. Now any connection using this PI ODBC DSN will have
this default time step specified. We can also go into the protocols tab and change the order
of NetTcp versus HTTPS. In a previous video I showed that both of these could be used,
we can switch the orders if we would like. There's also a third tab with a little bit
more configuration. We'll keep everything like this and we'll use default time step equals 6h.
We'll test the connection show that it succeeded and we'll click OK I'll click OK on this as well.
Now let's open up the 32-bit version of PI SQL commander lite and we see that we have both
32-bit PI server and 32-bit PI server default time step. Let's connect to the first one and do the
PI interp2 query. Let's change it to one day and notice that our time steps are all one hour. Now
let's connect to the 32-bit PI server default time step DSN, now let's do the same query.
Notice that the output is using six hour time steps and we only receive five values
across a full one day period. This is proving that the DSN itself is holding
configuration that we can put for the PI OLEDB provider or enterprise section of
the connection and those will be used for all applications using this DSN.
This concludes the video on installation, configuration and testing PI ODBC driver.