Different ways customers connect to OsiSoft PI

HadleyMyers

Hello everyone,

 

We recently had a customer tell us that they would prefer not to use the ODBC driver to connect OsiSoft PI to JMP because of a security issue.  Other options to connect are Web API and OLEDB.  I was just wondering if anyone has come across PI databases and what their customers are using to connect.  Is ODBC still the standard, or is there a trend away from this towards something else?

martindemel

Hadley, 

there are several ways to connect to Osi PI. ODBC is often used but I believe the tendency will go (at leatst in the future to REST or OLE DB), some are using it also via "Run Program" from JMP JSL using e.g. Python. But the latter needs more 

 

Some other things to consider (this is from a support case we had in 2018, so may there have been updates and changes since now on side of OSI PI:

The query in JMP (3rd party) takes more than 10 minutes (for 720000 events) to deliver the data, if I execute the same query in PI SQL Commander it also takes a long time to get the result displayed but not so long as in JMP. If I execute the statement a second time in the Commander it is much more faster than the first call. Why? Caching?

Yes - the ODBC takes longer than OLEDB queries, this is expected.
This is becasue the chain in the architecture and ecery point takes some time: PI ODBC -> SQL DAS -> PI OLEDB -> PI Data Archive

PI SQL Commander can be understood as PI OLEDB Enterprise. While 3rd party SAS JMP as PI ODBC. 

Yes caching is also taking place. This is also correct. PI OLEDB or PI ODBC does not have its own cache with query results, but the pi data archive has read cache


2)
The query delivers around 720000 events. What is the normal rate events/sec using PI ODBC?

720 000 events depend on the location of pi data archive. Running the command locally is faster, remotly it takes more time. Also the archived data and the way of stored data in pi data archvie is important (how is the data stored internally within the archives - out-of-order, records, how many archives are covering the period (3 months), etc.)

On of the important remark: the issue is that this kind of queries (IN condition) are executed in a sequential way, in else the interpolation is made for one tag first, subsequently for the second, third and so on.. 
testing results:
so far.....??? need to quantify (testing)

Are there some possibilities to get some information what the ‘bottleneck’ is or is PI ODBC the wrong tool to get data for so much PI points for this long time range?

bottleneck -> PI DataArchive read cache (disk read performance) 

So it is most important how the database is setup and where. Please don't share the actual numbers or this thread (as it might not be current anymore), however you may want to extract some of the findings to provide at least some hints of "where to look at" to the customer.

 

Note: be sure about what OSI PI package they use, there are some differences what they offer, e.g. PI OLEDB Enterprise allows access to Asset Framework elements/attributes, Pi Event Frames and timeseries data from the PI Data Archive via an OLEDB connection. The typical PI currently not (afaik)