Skip navigation
All Places > PI Developers Club > Blog > Author: ray

PI Developers Club

15 Posts authored by: ray

Next Generation PI SQL Processing

Posted by ray Jan 10, 2018

We have been working on a new SQL query processing engine we call the Real-Time Query Processor (RTQP). The RTQP Engine has several important goals. First and foremost, we plan to improve performance and scalability of your SQL queries. We understand that some queries involving large PI System databases and large amounts of time series data could be improved. Secondly, we are taking the opportunity to simplify the design of the tables that support SQL queries. Our goal is to simplify our design so that fewer table joins are required to perform your most common tasks.


The OSIsoft PI SQL Team is proud to announce the availability of the second Community Technology (CTP2) release of the Real-Time Query Processor (RTQP)! There was a CTP1 which was shared with a few early adopters; we thank them for their feedback. To install CTP2, you need a new OLEDB provider called PI SQL Client OLEDB. This provider communicates with the PI SQL Data Access Server (RTQP Engine) which performs the SQL query processing. You can download the PI SQL Client OLEDB, documentation and release notes from the Technical Support Download Center. The required PI SQL Data Access Server (RTQP Engine) is an optional component in the PI AF Services 2017 R2 install kit.


Our new table designs are not compatible with PI OLEDB Enterprise. We hope you will be motivated to migrate but we will continue to support PI OLEDB Enterprise and PI OLEDB Provider indefinitely so we don’t break your working configurations. They can be installed in parallel.


We welcome feedback! Please contact us at instead of calling Technical Support. Or, you can post comments here.


PI Web API 2017 R2 is released!

Posted by ray Dec 29, 2017

I am pleased to announce the release of PI Web API 2017 R2, our suite of REST services for interacting with the PI System. The PI Web API is a member of the Developer Technologies suite of products. Click to see our Technical Support Product Release Bulletin which in turn will direct you to the right pages in our Download Center. We have stopped shipping the dedicated PI Web API install kit. Instead, PI Web API is part of PI AF Services 2017 R2.


There are many enhancements and bug fixes in this release. I encourage you to read the Release Notes to learn about what we did. Improving performance is Job #1! You'll find many work items that were directed at improving speeds of data retrieval and search. I wanted to mention two features:


WebId 2.0: we have received many comments about our WebId concept which are unique identifiers to just about everything you reference through PI Web API. While accurate, they can get to be a bit big. To address this, we have introduced WebId 2.0. We now have different types of WebId that vary in length and precision. It is even possible to create WebIds yourself if you already have access to AF SDK object ids. Let me know if this is interesting to you.


Channels: we have received a lot of positive feedback about our websocket "Channel" implementation. One of your biggest problems, it seems, is that you can't be completely sure if the channel is healthy if you have not received data updates for a while. For technical reasons, we are unable to implement the web socket standard RFC6455 "Ping" and "Pong" features to ascertain health. What we have done instead is allow you to request a periodic "heartbeat" response which consists of an empty message if there are no data values to send.


I hope you can upgrade to PI Web API 2017 R2 soon. Let me know what you think!

I am pleased to announce the release of several products in the PI SQL family which are members of the Developer Technologies suite of products:

All of these products provide access to PI System data through SQL queries. Click on these product names to see the Technical Support Product Release Announcements which in turn will direct you to the right pages in the Technical Support Download Center. There are just too many enhancements and bug fixes to list them all here. Congratulations to the OSIsoft PI SQL development team on these releases!

Our architecture for PI Web API Indexed Search relies on a Crawler that scans PI System databases for meta-data and builds an index to this information. The PI Web API provides REST services that deliver search results to clients. This works well but it's less than ideal if you want to create a cluster of PI Web API servers working together. Each PI Web API server needs to hold its own index, so it needs its own Crawler to build it. So, if you build a cluster with 3 members, then you will have 3 indexes and 3 Crawlers, all doing the same work.


In PI Web API 2016, we are launching a Community Technology Preview (CTP) of a Shared Index. This means that all members of a cluster will share the same index. It also means that only a single Crawler is necessary to scan the PI System databases. All search results returned to clients will be consistent regardless of which cluster member performs the search.


We have the Shared Index working but we don't have time in this release to create a proper administration tool. If you would like to build a cluster with a shared index, you will have to configure it manually. We will give you documentation on how to do this, of course. Let me know if you are interested!

We've made a minor change to the name of our upcoming PI OLEDB Enterprise release. We had been planning to release it in 2015 and call it 2015. We had to delay our release so our development team could focus on exposing data from the PI Integrator for Business Analytics through PI ODBC 2015. So, we now look forward to releasing PI OLEDB Enterprise 2016 around the time of OSIsoft User Conference 2016 in San Francisco. Here's a short list of enhancements:

  • Future Data support,
  • Hidden and Excluded AF Attribute support,
  • AF's delta time strings which means, for example, a monthly report will cover the calendar month and not just 30 days,
  • Support for Summaries as both Table-Valued Functions and Function Tables, and
  • UTC mode which causes all times as be represented in UTC. We have added functions to convert to and from local time.

This name change from 2015 to 2016 also affects the Classic provider: PI OLEDB Provider 2015 is now PI OLEDB Provider 2016.

After careful consideration and after talking to many of you, we have decided that it is not appropriate to deprecate the PI OPC DA Server and the PI OPC HDA Server. It turns out that OPC is still a vital interconnection method for many industrial software applications and is still in use in mission-critical applications. So, you can once again consider our OPC Servers to be supported products with a strong future. We are working on a completely new implementation of the PI OPC DA Server using our AF SDK that should solve some of the performance and scalability problems you have been seeing. The architecture of the PI OPC HDA Server will remain as-is for the time being but we will be addressing some of its outstanding issues.


The PI OPC Servers are freely available for download from the Technical Support website just as the other Developer Technologies. As with the others, you will need a license from OSIsoft to deploy client applications that use the PI OPC Servers. The PI OPC DA Server and PI OPC HDA Server are covered by the PSA license but also the older DA license. Talk to your Account Manager if you have questions.


Deprecating the PI SDK

Posted by ray May 21, 2015

That’s a surprising blog post title but please don’t panic! We are not cancelling the PI SDK and will not do so for some time to come. We’ve just been doing some careful examination of our older PI Developer Technologies to see if they still have a place in the changing world of technology. We understand that many of you have built applications that rely on the PI SDK so this is an important discussion. We believe we can’t deprecate a technology unless a replacement is available with equivalent or superior features and performance. We believe this is the case with the PI SDK. Microsoft has been moving away from COM technology towards the .NET Framework for some time. Our AF SDK is built with the .NET Framework and now has the vast majority of the features of the PI SDK including direct access to points in the PI Data Archive. We think the AF SDK is ready to take over!


We have been talking about deprecating the PI SDK at several OSIsoft events including the recent User Conference 2015 in San Francisco. Feedback has been supportive. Still, we would like to make sure that you understand where we are going. The PI SDK is now officially “Transitioning-to-Deprecated.” This means that the actual end date for the product is years away which gives you…and us…time to migrate. However, we recommend that you start creating a transition plan for both new and existing developments. For more details about the roadmap of PI Developer Technologies please see this presentation from our San Francisco User Conference. We offer an online workshop on the AF SDK that you can follow at your own pace or register for an official offering. If you would like to talk about your own development roadmap, please let us know.

We have made our PI Developer Technologies more easily available. Anyone with an account on the Tech Support website or PI Square is now able to download the PI AF SDK, PI Web API, the PI OPC DA and PI OPC HDA Servers, and all products in the PI SQL Framework (PI OLEDB Provider, PI OLEDB Enterprise, PI JDBC Driver and PI ODBC Driver). We are doing this to make it easier for you to build applications that leverage PI System data. We are also allowing you to blog in public forums about using our PI Developer Technologies products, and to contribute to open-source projects that involve the PI System.


This does not mean that the PI Developer Technologies are license-free. You still need a PI System Access (PSA) license to deploy your applications. This is not a new requirement and we are not changing it. You might think of this as a run-time license. Note that the PI OPC DA and HDA Servers were also available under the older DA license. Talk to your Account Manager if you have questions about PSA or DA licenses.

We just made a minor change to the name of our upcoming PI OLEDB Enterprise release. We had been planning to call it 2014 but it seemed a better idea to wait until we could incorporate the features of the upcoming AF 2.7 release which includes future data and support for excluded and hidden AF Attributes. We are also implementing support for AF's delta time strings which means a monthly report built using PI OLEDB Enterprise will cover the month and not just 30 days. The last reason is, well, it's 2015...


Change in direction for OData

Posted by ray Dec 24, 2014

We started work on an OData Service for the PI System almost two years ago. At that time, it looked like OData would be the next big thing in data access from Microsoft. It’s designed using REST principles, has simplified but powerful query capabilities, and rich meta-data. Since it’s built on the HTTP protocol, it is a good choice for data access from client platforms other than Windows.

Unfortunately, now that our OData implementation is ready it looks like the market for it has not materialized. We don’t yet have any feedback from customers who say they absolutely require OData. Instead, it appears that OLEDB connectivity remains dominant in the market. It also looks like the ODBC standard will continue to grow now that it is being revitalized by Microsoft.

So, what should we do with our OData service? We have decided to make it available as an add-in to the PI Web API. We are calling this the OData Service CTP (Community Technology Preview). We encourage you to experiment with it. The CTP designation means we reserve the right to withdraw it completely if we find no significant use of it. On the other hand, if we find that a significant number of you do indeed find value in OData, we will go ahead and release.

We just launched the CTP release of PI Web API. This release will contain the PI OData CTP. If all goes well, PI Web API 2015 will be released in January. The OData service itself will be in the CTP state until we make our final decision. Let us know what you think!

I am pleased to announce the release of the PI Web API 2015 Community Technology Preview (CTP). You can download the software from our Technical Support website. This CTP release includes several enhancements such as write support for PI System configuration data, evaluation of performance equations, bulk time series data calls against Element or Event Frame streams, gzip compression of responses, and an OData service. We hope you are able to download and test this software soon. We would like to release this software in January 2015. Questions and feedback should be sent to

I am pleased to announce the availability of the beta release PI ODBC 2014. This is our new implementation of the ODBC standard to access PI Server data. PI ODBC 2014 is compliant with version 3.8 the ODBC API and offers the same SQL query functionality as the PI OLEDB Enterprise Provider and the classic PI OLEDB Provider. The PI ODBC 2014 runs on Windows and has both 32-bit and 64-bit drivers in its installation kit.


PI ODBC 2014 Beta has been tested with several ODBC clients including Microsoft's time-honored Access database. PI ODBC 2014 also supports Oracle's Database Gateway for ODBC which is part of the Oracle Heterogeneous Connectivity suite. You may remember that Oracle discontinued support for OLEDB data sources in Version 11g which left us without an option to directly connect Oracle to the PI System. PI ODBC 2014 will finally solve that problem. To help you configure Oracle to use PI ODBC 2014, we've created an Oracle Database Gateway Configuration Guide.


PI ODBC 2014 is not a direct upgrade from our current PI ODBC Client SQL query syntax and capabilities are different but we think you'll appreciate the improvements. PI ODBC 2014 can be installed in parallel with PI ODBC Client so we won't break any of your working configurations.


How do you get the bits? The PI ODBC 2014 Beta installation kit can be found on the Technical Support website. You will also need to download and install the PI SQL Data Access Server (PI SQL DAS) beta. The PI SQL DAS provides the actual SQL processing capability and is often installed on the same Windows machine as the PI ODBC 2014 Driver. Whichever machine runs the PI SQL will also have the PI OLEDB Enterprise Provider which must be installed first.


If you have any questions, comments or problems to report, please contact us at

I would like to introduce myself as the new Product Manager for Developer Technologies. Some of you may know me already. I've worked at OSIsoft for quite a while now in various roles. My most recent function was Director of Development for Data Access technologies. We've changed the name of this product category from Data Access to Developer Technologies in order to focus our attention on what you do: use our technology to build applications that bring the value of PI System data to your customers. I would like to hear stories about what you're building and what you need from us to make it better. I'll be supporting our AF SDK, our PI Web API and the SQL family of products (PI OLEDB Enterprise, PI JDBC and PI ODBC).


Getting Summaries

Posted by ray Sep 6, 2009

We call these both summaries and aggregates interchangeably. I am talking about the classic OSIsoft set of summaries that you can request for any PI point: average, minimum, maximum, total, range, standard deviation (both types) and mean. To get any of them, you must time interval instead of a single time. When you retrieve any of these summaries, we also give you a "percent good" value which indicates what fraction of the time interval had good data in it.


The current CTP design for getting summaries is admittedly a bit odd. We accept a "columns" argument which is a list of summaries you want. What you get back is a "cross-product" of this list and the Paths. For example, if you request "Average" and "Total" from Paths "sinusoid" and "cdt158," you get 4 summaries back: sinusoid(Average), sinusoid(Total), cdt158(Average) and cdt158(Total). If you've tried it, you will see these "pseudo-Path" strings in the returned data set.


This is less than ideal. We need a better design. One option on the table is to create a separate service for summaries which would return them as a Table instead of  a Timeseries. This way, each column would contain one summary. Each row would contain the summary's timestamp (which in our case is the timestamp at the end of each time interval) and the "percent good" value. The service would return a collection of tables, one for each passed Path.


Let us know what you think.


What about Annotations?

Posted by ray Sep 6, 2009

All events stored by a PI Server can have an annotation associated with them. They are usually strings with some kind of relevant comment about the event, but they can also contain structured data. The majority of events stored in PI Servers have no annotations. Even when they are present, most of our applications don't actually retrieve them unless you ask.


We are struggling with the best way to expose annotations through the PI Web Services. In the current CTP, there is no support for them at all. We are considering these options:

  • Create a separate web service to retrieve annotations. It would likely take the same Context argument as the normal data call. We would have to return timestamp and value as well so you know with which event the annotation is associated. This makes it pretty much the same as the regular data service.
  • Add a nullable annotation object to the TimedValue. This means that any event returned by the normal data call could hold an annotation. We would add an "option code" to the parameter list so that you can tell the service whether you want annotations returned or not.

Whatever choice we make, we will add support for the "status flags" associated with each event. This is a set of booleans that tell you if an event is Questionable, has been Substituted or is Annotated. The last flag means you can find out if an annotation is present without retrieving it.

Filter Blog

By date: By tag: