Solution: Selecting the Best Product

Document created by kduffy on Sep 26, 2018Last modified by kduffy on Jul 2, 2019
Version 17Show Document
  • View in full screen mode

This document contains the answers to Exercise: Selecting the Best Product. The answers are provided for your learning, but make sure you have attempted them before looking at the answers.

 

1. Numerous end users are running non-trivial queries to retrieve shaped/summarized data through the AF server.

answer - Engine: RTQP || Architecture: PI SQL DAS (RTQP) on the PI AF Server, and PI SQL Client (OLEDB) installed on each client machine

 

The best option here is to use the RTQP Engine. This will off load all of the query processing onto the AF server and not have consume the machine resources of the individual client machines. Bandwidth, and therefore speed, is also an important note for aggregate/summary queries because only the final summarized values need to travel across the wire, as opposed to the entire data set (which is required for PI OLEDB Enterprise).

 

Alternatives:

- If the same summaries are needed on a regular basis by numerous clients, another option would be to use the PI Analysis Service and store the values as PI Points; this option is bad for adhoc summaries.

- PI OLEDB Enterprise on every client machine would dramatically increase the bandwidth usage as well as the machine resources on the client

 

2. Data from an edge device running Linux would like to be recorded into the PI Data Archive.

answer - Engine: PI OLEDB Provider || Architecture: PI JDBC Driver on the Linux device; PI OLEDB Provider and PI SQL DAS (OLEDB) on a middle server

There's two key points here. The first is that data is being written to the PI Data Archive; this requires the PI OLEDB Provider as it's the only read/write provider. The second is the use of a Linux device; PI JDBC Driver is that only product that can use the PI OLEDB Provider that can be installed on Linux. Since the device is Linux, this also necessitates the PI SQL Data Access Server (OLEDB) being installed on a middle server as this is a Windows service.

 

Alternatives:

- If the specific device already has an OSIsoft developed connector, this may be an option.

 

3. An existing customer has been using PI OLEDB Provider for years, but will be implementing AF soon.

answer - Engine: RTQP || Architecture: PI SQL DAS (RTQP) on the PI AF Server, and PI SQL Client (OLEDB) installed on each client machine

The RTQP Engine is currently the default product recommendation. Since AF is being implemented, the customer should move away from PI OLEDB Provider queries and start using RTQP.

 

Alternatives:

- Continuing to use PI OLEDB Provider will prevent any SQL client from leverages any of the benefits of implementing AF.

- PI OLEDB Enterprise has been replaced by RTQP, so any new projects should avoid its usage.

 

4. Client machines are locked down by IT and no software can be installed if it doesn't come with their Windows Operating System or the Microsoft Office suite, but they need to see data in Excel.

answer - Engine: RTQP || Architecture: PI SQL DAS (RTQP) on the PI AF Server; PI SQL Client (OLEDB) on a SQL Server that contains a linked server for the PI AF Server; SQL Native Client on each client machine

This is a tricky scenario that I wouldn't expect too many people to have gotten. The key is that the Microsoft SQL Server's OLEDB Provider, the SQL Server Native Client, is installed by default on Windows machines. Therefore, without additional third party software (OSIsoft is the third party in this case), this is one of the only things a client can use. The SQL Server Native Client can only communicate with the a SQL server, but the SQL server can have the PI SQL Client installed and a linked server configured to use it. This linked server will then communicate with the PI SQL DAS (RTQP) on the AF Server to retrieve data. Finally, Excel can use the SQL Native Client as an external data source.

 

Alternatives:

- The best option would be to request an exception be made for all clients to have PI DataLink installed

 

5. A large amount of money and time was spent designing queries for PI OLEDB Enterprise for a third party application, but the processing on the application server is now too high.

answer (short term) - Engine: PI OLEDB Enterprise || Architecture: PI ODBC Driver on application server; PI OLEDB Enterprise and PI SQL DAS (OLEDB) on middle server

answer (long term) - Engine: RTQP || Architecture: PI SQL DAS (RTQP) on the PI AF Server, and PI SQL Client (OLEDB) installed on the application server

The best option is to use RTQP. The complain of high machine resource usage on the application server is one of the main benefits of moving from PI OLEDB Enterprise to RTQP. However, given that the PI OLEDB Enterprise query development for this customer was a long and expensive process, the RTQP queries might be as well, and projects like this may not be immediately doable in the short term.

 

To alleviate the immediate issue of high machine resource usage, the PI OLEDB Enterprise query engine can be quickly moved to a new middle server and the application can be configured to use an ODBC DSN. This will reduce the resource usage on the application server given that the PI ODBC Driver is a thin client, and the replacement query writing can be postponed until the project of migrating to RTQP can be executed.

 

Alternative:

- Increase the machine resources on the application server. This is merely a "band-aid" solution and does not address the actual cause of the issue.

 

 

6. A single user needs to sometimes run a few simple queries on their laptop.

answer - Engine: RTQP || Architecture: PI SQL DAS (RTQP) on the PI AF Server, and PI SQL Client (OLEDB) installed on the laptop

RTQP is currently the default product choice, and this being a very default installation, it should be used. In addition, query development for PI OLEDB Provider and PI OLEDB Enterprise should only be done if there's a reason to not use RTQP.

 

Alternatives:

- PI OLEDB Provider and PI OLEDB Enterprise can be used just as easily used on the client machine and might be a better option if the AF server does not have PI SQL DAS (RTQP) installed, and the installation of that product is not immediately possible. Spending too much time and effort developing queries for PI OLEDB Provider and Enterprise should be avoided, however, unless there is a strong need to not use RTQP.

 

7. There is data in a LIMS system (relational database) that is updating every hour and it's important to get this data into the PI Data Archive. Data availability is very important.

answer: PI Interface for RDBMS on middle server

KB01921 - Deciding Between PI OLEDB Provider and PI Interface for RDBMS contains the complete walkthrough of when to use the PI Interface for RDBMS and when to use the PI OLEDB Provider. In this case, data availability is very important, so the features developed into the UniInt Framework and the interface itself will be of importance, and should therefore be the deciding factor.

 

8. A large relational database exists outside of the PI System, and the data needs to be migrated. This is a one time operation.

answer: Contact OSIsoft Tech Support to get the best answer for each specific scenario

 

There are so many options that exist in this scenario. PI OLEDB Provider (optionally starting with the PI ODBC Driver) could be used, but if there's an option to export the data to something digestible by the PI Interface for UFL (any plain text file type should work), then this may be the fastest. Other options would include Powershell Tools for the PI System, history recovery via the PI Interface for RDBMS, piconfig scripts, or a few others. Extremely large data sets can sometimes take weeks to import, so taking the time to investigate all options for your scenario is usually worth the effort.

 

9. There are a handful of data sources, one of which is the PI System, that need to be aggregated nightly in an ETL with SQL Server Integration Services.

answer: Engine: RTQP || Architecture: PI SQL DAS (RTQP) on the PI AF Server, and PI SQL Client (OLEDB) on the machine running the ETL

If you're unfamiliar with ETL's, feel free to skip this question. An ETL (Extract, Transform, Load) is a way of extracting data from multiple different sources, transforming all of it into a common format, and loading it into a single data source (typically a cube). Using the PI SQL Client on the server performing the ETL will reduce the query execution time and ETL server machine resources versus that of PI OLEDB Enterprise, resulting in a faster ETL.

 

Alternatives:

- Install PI SQL Client on a MS SQL Server and configure the ETL to read from a linked server on this SQL Server. This has the benefit of allowing the SQL Server to broker the ETL server's connection to the AF Server, but unless there is a necessity to use the PI OLEDB Provider or PI OLEDB Enterprise, this extra layer is not providing that much utility (if there is a necessity to use either of those query engines, then the SQL Server can carry the processing load of the query parsing).

 

10. A third party application needs to have a constant stream of data flowing to it from the PI System with new values every 15 minutes for select attributes on numerous elements.

answer: PI Integrator for Business Analytics

The PI Integrator for Business Analytics Advanced Edition is capable of sending updates on a scheduled interval, such as 15 minutes, and the configuration page makes it easy to send the values for selected attributes across numerous elements. It can connect direct to many external databases, but if the desired database is not in the supported list, then it can connect through something such as an MS SQL Server.

 

Full details on the PI Integrator for Business Analytics is beyond the scope of this class; see the LiveLibrary documentation for more details.

 

Alternatives:

- PI SQL Client could be used to query the values of these attributes on these elements in a straightforward manner, but something would be need to be put into place that executes the query and moves the data to the application. If the Application itself is capable of doing this refresh, then the use of PI SQL Client would be very straightforward. Otherwise, something such as Powershell could be used to broker the communication between the PI System and the application, and Windows Task Scheduler could be used to trigger the script every 15 minutes. The same approach can be taken by a SQL Server Integration Services package, executed by the SQL Agent service.

 

11. Reports need to be generated for every asset at a plant, either from on a daily frequency or on an adhoc basis.

answer: Engine: RTQP || Architecture: PI SQL DAS (RTQP) on the PI AF Server, and PI SQL Client installed on the reporting server

This is very similar to scenario 9. The best option would be to use the PI SQL Client on the reporting server and make an OLEDB connection directly to the PI SQL DAS (RTQP) running on the AF server. This takes the machine resources off of the reporting server and reduces the bandwidth between the reporting server and the AF Server, as compared to PI OLEDB Enterprise.

 

Alternatives:

- Install PI SQL Client on a MS SQL Server and configure the ETL to read from a linked server on this SQL Server. This has the benefit of allowing the SQL Server to broker the ETL server's connection to the AF Server, and if there is a necessity to use the PI OLEDB Provider or PI OLEDB Enterprise, this extra layer will remove the resource consumption from the reporting server. Finally, if there are other sources reading the same data and SQL scripting or stored procedures are used, then doing all the programming in one place on the SQL Server will allow for any clients, including the reporting software, to utilize this without additional effort.

 

12. The AF Server is not being used and data needs to be queried for a few tags.

answer - Engine: PI OLEDB Provider || Architecture: PI OLEDB Provider on the client machine

If the AF server is not being used, then PI OLEDB Provider is the only query engine option.

 

Alternatives:

- Implementing AF and switching to RTQP (with PI SQL Client installed on the client machine) would be the best option, but this will take time.

 

13. A third party application is being used that only allows ODBC data sources.

answer (PI SQL Client 2018 R2 and later): Engine: RTQP || Architecture: PI SQL DAS (RTQP) on the PI AF Server, and PI SQL Client (ODBC) installed on the application server

answer (prior to PI SQL Client 2018 R2): Engine: PI OLEDB Enterprise || Architecture: PI ODBC Driver on the application server; PI SQL DAS (OLEDB) and PI OLEDB Enterprise on middle server

The best solution is to use the RTQP Engine on the AF server and connect to it from the PI SQL Client (ODBC) on the application server.

 

This endpoint is not released until PI SQL Client 2018 R2, so prior to that release, the only ODBC connection that can be made to the PI System is through the PI ODBC Driver (aside from the PI ODBC Client making ODBC 2.0 connections). This would require that the application server use the PI ODBC Driver to connect to the PI SQL DAS (OLEDB) service on a middle server, which will then use PI OLEDB Enterprise to connect to the AF Server.

 

Note: For write operations to the PI Data Archive, the PI OLEDB Provider will need to be used, necessitating the data flow to consist of PI ODBC Driver to PI SQL DAS (OLEDB) using PI OLEDB Provider to the PI Data Archive.

 

14. A java application is being used, and therefore a JDBC connection is required.

answer (PI SQL Client 2018 R2 and later): Engine: RTQP || Architecture: PI SQL DAS (RTQP) on the PI AF Server, and PI SQL Client (JDBC) installed on the application server

answer (prior to PI SQL Client 2018 R2): Engine: PI OLEDB Enterprise || Architecture: PI JDBC Driver on the application server; PI SQL DAS (OLEDB) and PI OLEDB Enterprise on middle server

The best solution is to use the RTQP Engine on the AF server and connect to it from the PI SQL Client (JDBC) on the application server.

 

This endpoint is not released until PI SQL Client 2018 R2, so prior to that release, the only JDBC connection that can be made to the PI System is through the PI JDBC Driver. This would require that the application server use the PI ODBC Driver to connect to the PI SQL DAS (OLEDB) service on a middle server, which will then use PI OLEDB Enterprise to connect to the AF Server.

 

Note: For write operations to the PI Data Archive, the PI OLEDB Provider will need to be used, necessitating the data flow to consist of PI JDBC Driver to PI SQL DAS (OLEDB) using PI OLEDB Provider to the PI Data Archive.

 

15. An application server is running on Linux and must read data from the PI AF Server

answer (PI SQL Client 2018 R2 and later): Engine: RTQP || Architecture: PI SQL DAS (RTQP) on the PI AF Server, and PI SQL Client (JDBC) installed on the application server

answer (prior to PI SQL Client 2018 R2): Engine: PI OLEDB Enterprise || Architecture: PI JDBC Driver on the Linux application server; PI SQL DAS (OLEDB) and PI OLEDB Enterprise on middle server running Windows

 

The best solution is to use the RTQP Engine on the AF server and connect to it from the PI SQL Client (JDBC) on the application server since the PI SQL Client (OLEDB) and PI SQL Client (ODBC) cannot be installed on Linux.

 

This endpoint is not released until PI SQL Client 2018 R2, so prior to that release, the only PI SQL product that can be installed on a Linux server is the PI JDBC Driver. This would require that the application server use the PI JDBC Driver to connect to the PI SQL DAS (OLEDB) service on a middle server, which will then use PI OLEDB Enterprise to connect to the AF Server.

 

Alternatives:

- The application could be written to use HTTP instead of SQL, and the PI Web API can be used for data retrieval.

 

16. A third party application retrieves data from the AF server, but its machine resource usage is already pushed the limit. The AF server is version 2.7, and no machine hardware or software upgrade can be made in the short term to the AF server or application server.

answer (short term) - Engine: PI OLEDB Enterprise || Architecture: PI ODBC Driver on application server; PI OLEDB Enterprise and PI SQL DAS (OLEDB) on middle server

answer (long term) - Engine: RTQP || Architecture: PI SQL DAS (RTQP) on the PI AF Server, and PI SQL Client (OLEDB) installed on the application server

Just like scenario 5, the best answer is a long term move to RTQP. This will require only the installation of PI SQL Client on the application server, which is a thin client, so it will not affect the machine resources of the client. The installation of PI SQL DAS (RTQP), however, requires the usage of PI AF Server 2.10 or later. Since an upgrade of the AF server is not possible in the short term, the only option will be PI OLEDB Enterprise. Since PI OLEDB Enterprise is a thick client performing all of the query processing locally, the usage of the PI ODBC Driver on the application server would be beneficial. This architecture would require the PI SQL DAS (OLEDB) service to be installed on the middle server containing the PI OLEDB Enterprise.

 

Alternatives:

- Simply waiting until the AF Server can be upgraded to roll out the project may be better. Any queries written against PI OLEDB Enterprise will need to be rewritten against RTQP, so if the query development is not trivial, lobbying for an expedited time frame for AF server upgrade might be better.

 

17. A third party application can only make ODBC 2.0 connections

answer - Engine: PI SQL Subsystem || Architecture: PI ODBC Client on the client machine

For ODBC 2.0 connections, the only option is the PI ODBC Client.

 

Alternatives:

- We strongly recommend, if possible, to upgrade the application to one that uses either an OLEDB or at least an ODBC 3.0 connection.

 

18. An application needs to access data from four PI systems around the world. Two are using AF 2.10 and two are using AF 2.8 with no chance of an upgrade. The ping times from the application server to each AF server is over 200ms.

answer - Engine: PI OLEDB Enterprise and RTQP || Architecture: PI ODBC Driver and PI SQL Client on application server; PI SQL DAS (RTQP) on both AF 2.10 servers; PI SQL DAS (OLEDB) and PI OLEDB Enterprise on middle servers next to both AF 2.8 servers.

This one is tricky. The first key to this is that AF Servers 1 and 2 can use RTQP while AF Servers 3 and 4 cannot (RTQP requires AF 2.10 or later), meaning that PI OLEDB Enterprise must be used for data retrieval from both servers. PI SQL Client is a thin client for the PI SQL DAS (RTQP) service, so the 200ms ping time does not matter in this regards. PI OLEDB Enterprise, however, is a thick client for communicating with that AF Server as this is doing all of the query parsing and AFSDK communication with the server. In this situation, a 200ms ping time will cripple the application from a query processing time. To get around this, the use of the PI ODBC Driver on the application server and installing the PI SQL DAS (OLEDB) and PI OLEDB Enterprise on a middle server on each AF servers' network will result in the same thin client behavior at the client. The PI ODBC Driver will send off the query and get the results, just like the PI SQL Client does, and each PI SQL DAS (OLEDB) service will act as the AFSDK client on the networks of their AF servers, allowing for the bulk of the communication to be done at a <1ms ping time, and as little as possible on a 200ms ping time.

 

Finally, using PI OLEDB Enterprise is a necessary for the older AF servers, but it should only be temporary until a newer version is installed at a later date. Because of this, using RTQP where possible and PI OLEDB Enterprise only where necessary will optimize the overall query performance of the application. The duplication of effort in terms of writing the queries for both RTQP and PI OLEDB Enterprise may be a problem, however, that same duplication of effort is simply postponed by using PI OLEDB Enterprise now and migrating to RTQP as soon as the AF Server upgrades are feasible.

 

Alternatives:

- Pushing for an upgrade of AF Servers 3 and 4 so that the entire project can use RTQP, and therefore consistent queries across servers, would be a very good solution if the inability to upgrade is something that can be challenged. Needing to only develop the queries for one query engine cuts down on the amount of training required to build the queries, cost of developing the queries, and time spent maintaining and troubleshooting the queries. Since RTQP is our current recommended query engine, we strongly suggest picking this over PI OLEDB Enterprise.

- Rolling out the entire project to use PI OLEDB Enterprise for the query engine and PI ODBC Driver for the application would keep the queries consistent across the servers as well (and therefore driving down development cost), but since the query execution time is faster for RTQP, there's an unnecessary slow down being introduced. If the queries being written are complex enough that two entire deployments of them (RTQP and PI OLEDB Enterprise) is impractical, then ideally only the first alternative would be employed and the two 2.8 AF Servers would be upgraded.

 

19. Data on one PI Server needs to be sent to another PI Server.

answer - PI to PI Interface

This is another example of keeping your eyes open to solutions outside of the PI SQL Framework. The PI to PI Interface was developed for this exact use case and it should be the default choice.

Note: The decision of whether to have the interface installed on the destination, source, or in between is beyond the scope of this course.

 

Alternatives:

- If there's a specific functionality that the PI to PI Interface is missing, then you can use the PI OLEDB Provider to write to the destination PI server, any of the PI OLEDB providers (PI SQL Client, PI OLEDB Enterprise, PI OLEDB Provider) to read from the source server, and some service to control the operation (such as SQL Server Agent or Powershell)

 

20. An application needs to access data from the PI System but the application does not allow OLEDB Data Sources; it only allows connections to SQL Servers.

answer - Engine: Any || Architecture: SQL Server as middle server. App uses SQL Native Client to connect to SQL Server; queries are against a linked server that uses a PI OLEDB endpoint (PI SQL Client, PI OLEDB Provider, PI OLEDB Enterprise) to connect to the PI System on the application's behalf.

 

In this scenario, the application is not capable of connecting directly to the PI System via any of the PI SQL endpoints, which is covered by the "Compatibility" point in Deciding Between Direct OLEDB Connections or Routing Through an External Database. This is a common scenario as some applications are built to allow SQL Server connections, but not built to allow any generic third party OLEDB connection. In these cases, the application can connect to a SQL Server, and the SQL Server can connect to the PI System on the application's behalf.

 

 

Alternatives:

- If the application cannot connect to a generic OLEDB Data Source, there may not be any other options. Similar functionality to the SQL Server's linked server certainly exists in other products, but their usability would be governed by the external data source options within the Application. Otherwise, using a different application may be the only option.

1 person found this helpful

Attachments

    Outcomes