Database Access to OSYsoft PIMy prospect customer asks me a question about database access to OSYsoft PI from JMP.
They recently installed PI system in their factory and just started to discuss how to utilize data. They want to know the best practice to access to OSYsoft PI.
I saw the following discussion and understand JMP can access using ODBC driver.
https://community.jmp.com/t5/JMP-SE-Discussions/Can-JMP-Access-data-from-Pi/m-p/43392
But, I saw the following defects. It seems that it has some problems when access to PI.
https://sirius.na.sas.com/Sirius/GSTS/ShowTrack.aspx?trknum=7612389374
Anybody know the best way to access to PI?
I found a following addin, which connects to an OLEDB and converts it to a SQLite database to be used with Query Builder.
Do you think this is a better approach?
https://community.jmp.com/t5/JMP-Add-Ins/OLE-Database-Tools-Addin/ta-p/23702
Additionally, anybody have any picture of recommended system architecture ? I want to share the customer if we have.
Re: Database Access to OSYsoft PI
There are several options for interfacing to OSI PI from JMP.
As you noted the JMP interface options can include using an ODBC driver, HTTP request, or via Run Program and others.
The best option for customer is dependent on which OSI PI products customer has licensed and their comfort level with JSL.
I’ll examine some options below with (in my opinion) the pros and cons with each approach.
- The add-in uses an OLEDB interface and serves as an example of using Run Program and Python. Whereby, an OLEDB driver connects to the historian to create a snapshot of the tables contained in the database. One then uses JMP QB to develop an interactive query against the snapshot database. Once the query is defined sufficiently an option to submit against the Historian DB directly is possible. However, I would not recommend this method as it is difficult to get Python configured/installed properly as each customer has different security policies in place and minimum version of python, or no python installed, and different libraries and or versions. The add-in was written prior to having a JMP - Python integration available in the JMP software.
- Run Program with JDBC driver might be possible as well. I have not tried to implement such a solution but in theory it would work. The pros with this approach is the solution would a completely customizable solution by customer and could be deployed with a JMP add-in. The cons with this approach is the customer would need to be vested in using JMP JSL to provide a smooth and continuous solution without gaps for a scientist or an engineer to make use with out any JSL scripting.
- The OSI PI ODBC driver works well but the driver can have performance issues, i.e., slowness with pulling large volumes of data. This is an issue of the driver and not JMP.
- HTTP request is very robust and would be my recommendation. But HTTP request requires JSL knowledge to implement. This could be a positive as the solution can be completely customized by customer to their specific workflow. The con for this approach is the customer does need to have JMP JSL scripting skills to make the solution seamless for an engineer / scientist.
- Another option is to create a database link from a SQL Server to the Historian DB. This approach will not fully utilize all aspects of JMP Query Builder as the solution employs a custom query to pull the data into JMP. The benefits with this approach are if customer already has known SQL queries they would like to execute they can reuse them with minimal effort to get their data into JMP.
I cannot fully comment on the Proficy Historian architectures as they would be more related to customer and how they have chosen to set up within their corporate infrastructure in addition to those or based on the products licensed.
I hope this helps. Feel free to reach out to me with additional questions.
cheers,
Stan
Re: Database Access to OSYsoft PIStan, thank you so much for your comment. These information are very helpful.
