Hi and welcome to this PI SQL class. In this video we're going to provide
an overview of the PI SQL family what the products are, what their data flows are.
The goal of this video is to provide guidance on this diagram. This diagram
shows all the current products and their data flows; as somebody who's going to be
developing with the PI SQL products it's important to know what your options are.
In order to understand this as it's a little complicated I find the best way to describe it
is to go through a history of the PI SQL family and to see what the landscape looked like at the
release of each product and we'll provide a little bit of guidance on why they were released. We'll
start from the beginning when the PI data archive was connected to by client applications using the
PI API. This required that the applications were natively built against the PI API to
connect directly to the PI data archive. This posed a problem whenever you're trying to use
an application that uses things like ODBC data sources. ODBC is the open database connectivity
standard which allows for one application to use this ODBC interface to connect to multiple
different ODBC data sources or even just one that they don't want to write native code against.
So in 1998 OSIsoft released the PI ODBC client to expose the PI data archive as an ODBC data
source. This uses the 2.x ODBC API hence the two dot ex above the little box as you can see
in the legend I'm trying to make it a little bit obvious on what the endpoints are the
connectivities of everything by having circles for native PI connections as you can see this
is the smoothest and most efficient way to to communicate and then a little box for ODBC. This
is a little bit you know box here and not as fine tuned for one specific data source but it
still very much gets the job done. The PI ODBC client utilizes the PI SQL subsystem on the PI
data archive for all this query parsing. The application will write a SQL query,
the PI ODBC client we'll send it over to the PI SQL subsystem and the PI SQL subsystem will
parse it into the data that it needs from the PI data archive whether the snapshot subsystem or
the base subsystem or the archive subsystem etc. then it'll send it back to the client and then the
ODBC client will provide the connectivity to the application to get the result set.
In 1996 Microsoft introduced the OLEDB standard that expands upon ODBC, as you can see from my
little symbol the OLEDB protocol has a little bit more to it than just the ODBC. That's a
good way to think about it because it expands on the functionality but it's effectively the
same thing. There's things like metadata that is provided with OLEDB that is not provided
with ODBC. So in 2001 in response to this the PI OLEDB provider is released. This exposes the PI
server as an OLEDB data source. PI OLEDB provider also moves the query parsing off of the PI data
archive this allows the client architecture to absorb some of the processing requirements and
allow the PI data archive to focus on other things. This allows for higher throughput
of let's say snapshot updates because the PI data archive is not doing things such as PI SQL
subsystem query parsing, but the client is doing that and simply communicating with the PI SDK.
In 2010 the PI JDBC driver was introduced for Java applications as you can see from
the symbol that I use for JDBC it's a lot like the ODBC but a little bit different it's coming
from Java applications. JDBC is an integral part of Java runtime environments so any
Java application or any Java development that you're going to do will inherently allow JDBC
connections. Since this changed for the PI JDBC driver is only a change in endpoint from OLEDB
or ODBC to JDBC we decided not to rewrite any of the query parsing itself and to simply provide
the JDBC driver with a way to access the PI OLEDB provider. This way all the queries that
you could write for the PI OLEDB provider in an OLEDB application can be written in
JDBC as well. To do this we introduced the PI SQL data access server this as shown here the
diagram provides a secure channel using either HTTP or Net Tcp and it passes the JDBC query
from the client application to the PI OLEDB provider; then the PI OLEDB provider will
speak to the PI data archive on its behalf. When the results come back the PI SQL data
access server will provide the result sets back to the PI JDBC driver and theJava application.
In 2014 we introduced a 3.x ODBC driver this uses the same data flow as PI JDBC again to make sure
that we don't replicate any form development and have the same code written multiple times,
that kind of thing. Now the same query parsing can be done through three different end points
a OLEDB, JDBC and ODBC. In 2007 the AF server was released but the PI OLEDB provider cannot
connect with it because it's using PI SDK. This posed a problem for customers as there
is data on the AF server that you would like to query from your OLEDB application,
your JDBC application, or ODBC application. So in response to this in 2010 we introduced
a new OLEDB provider for exposing AF servers to PI OLEDB enterprise. This name can be a little
bit confusing because it is itself an OLEDB provider however we already have a product
called the PI OLEDB provider. The name PI OLEDB enterprise also reinforces the idea
that AF and therefore this validity provider is an enterprise wide solution for your data.
If you're struggling to remember which one is the PI OLEDB provider and which one is the PI
OLEDB enterprise just remember that the PI data archive came before the AF server and
clearly the PI OLEDB provider was the first OLEDB provider for PI. Therefore the PI OLEDB
provider must expose the PI data archive and PI OLEDB enterprise exposes the AF server. Now the
data flow of PI OLEDB Enterprise is similar to any other AF SDK application. You have an
AF SDK connection to the AF server, to the AF application service. The AF application service
then makes a SQL Native Client connection to the SQL server hosting the PI FD database.
To resolve any PI point data references the AF SDK client itself in this case PI OLEDB
enterprise makes an AF SDK connection to the PI data archive to resolve this.
Now this led to an interesting problem that some applications such as the Microsoft SQL
server cannot use OLEDB providers that have .NET code. This query engine uses the AF SDK
which is itself a .NET library that means that because the query engine is on the client and
the PI OLEDB enterprise requires an AF SDK therefore .NET connection the Microsoft SQL
server among other applications are going to have to load OLEDB providers with .NET code.
Since this does not work in the first release of the PI OLEDB enterprise you could not make
an in process link server with the Microsoft SQL server. We'll go over what that means in
a little bit if you don't already know. But what this led to in 2011 was the introduction
of the PI OLEDB enterprise agent service. This decoupled the OLEDB endpoint from the
query engine and therefore AF SDK calls. This means that the process space of the Microsoft
SQL server process does not need to include the AF SDK or any .NET libraries. The OLEDB
endpoint itself is calm therefore everything is separated as necessary the PI OLEDB enterprise
agent is a different process that itself can host the AF SDK and be the AF client.
So that leads us to an overview of the 2011 through 2018 PI SQL family overview. Over those
years we've released new versions of everything but we didn't make any landscape wide changes to
any of the products or the data flows. So we'll go through the data flows one more time just to make
sure that everything is understood. So if you're using a 2.X ODBC application connecting to the
data archive it would look something like this: PI ODBC client connecting with a PI API to the PI SQL
subsystem. If you're using an OLEDB application direct to the PI data archive you'll be using
the PI OLEDB provider to use PI SDK to talk to the data archive. OLEDB application to the AF server
would use PI OLEDB enterprise going through the PI OLEDB enterprise agent service to talk to the AF
server and the PI data archive. You can optionally turn off the PI OLEDB enterprise agent service
if you would like to and if your application allows it and your data flow looks like this.
For 3.X ODBC applications talking directly to the data archive you have this data flow;
it goes from the PI ODBC driver to the PI SQL data access server which will then use the PI OLEDB
provider and then it will speak PI SDK to the data archive. JDBC is the same thing but with the
JDBC driver and a Java application. For an ODBC application connecting to the AF server it would
use the PI SQL data access server to communicate to the PI OLEDB enterprise which would then use
PI OLEDB enterprise agent to connect to the AF server and data archive. Same thing with the JDBC
driver except that it's using the new JDBC driver instead of the ODBC driver. In 2018 we released a
new product called the PI SQL data access server RTQP or real-time query processing. This new query
engine sits on the AF server itself and uses SQL Native Client to speak directly to the SQL server
that hosts the PIFD. Clients use the OLEDB, ODBC or JDBC endpoints just like before to communicate
to the PI SQL DAS. This data flow is similar to the ODBC driver and JDBC driver from earlier,
however the communication with the PI SQL DAS is substantially simplified. As you can see from the
diagrams on the left versus diagrams on the right. This change also allows the OLEDB endpoints to
be decoupled from the query processor; this is something that the ODBC and JDBC drivers could
do however the OLEDB endpoints given that they were connected to the query processing for PI
OLEDB provider and PI OLEDB enterprise had to reside on the same machine for the application
and the query processing. With PI SQL DAS RTQP we no longer have to do that for OLEDB applications.
This completes our walkthrough of the diagram that we showed at the beginning of the video,
hopefully by this time you have a little bit of understanding of what the products are,
what their data flows are and we can start to go over the implications of which ones to use
in certain scenarios which we'll do in the next video, thank you!