2 Replies Latest reply on Aug 17, 2017 5:03 PM by mkreideweis

    Drawing trend-line in ProcessBook only from first event to last event between trend-starttime and -endtime but not interpolating beyond


      Hello Community,


      we have a special requirement for visualizing sample-data from an external database in PI ProcessBook: Drawing trend-line in ProcessBook only from first event to last event between trend-starttime and -endtime but not interpolating beyond. So there should be no interpolated line between the first event in the trend and the event before (outside the trend) and no interpolated line between the last event in the trend and the event after (outside the trend). I attached a screenshot of a trend.


      Currently we use a PI COM Connector for OLEDB sources for accessing this sample-data from the external database and the SQL-query called by the PI COM Connector for OLEDB sources adds a No_Sample-event before the first and the last event in the trend. But we need to exchange the PI COM Connector for OLEDB sources by the PI Interface for Relational Database because we have some issues with it and want to be independent from the availability of the external database.


      With the PI Interface for Relational Database we'll copy the data from the external database into the PI System and we cannot easily add a No_Sample-event before the first and the last event in the trend like we did with the PI COM Connector for OLEDB sources.


      I already thought about using ODBC-datasets in ProcessBook and adding the No_Sample-events by the corresponding SQL-queries. These ODBC-datasets would then read the data from the PI-Tags and not from the external database (because we'd like to be independant). But this option seems not to be user-friendly. The users want to build their own displays and would need to know how to configure the SQL-queries. Programming with VBA in ProcessBook to make it user-friendly is also difficult because it is a validated system and we'd need to validate. And we have also quite a lot of PI-tags connected to this sample-data (about 4000) and I'm afraid that we'll get a performance-issue.


      Do you have any idea how we could realize this functionality? We'd prefer solutions, which don't require any programming.



      Thank you and kind regards



        • Re: Drawing trend-line in ProcessBook only from first event to last event between trend-starttime and -endtime but not interpolating beyond

          Hi Michael,


          This is an interesting request and I'm not sure that ProcessBook can do this without something custom. In order to explain why it's not a standard feature I'll speak to what the trends are showing and what it means to the PI system in general.


          For your tag, you would like to show a subset of the data from, in your example, May 31 to June 9. There's some data in that time period that you would like to interpolate through, but you don't want to interpolate from the point most previous to the trend to the one around 10am on May 31. This idea clashes with the way the PI server stores data because our storage algorithm rests on the fact that data is interpolated between consecutive values. Exception and compression govern which values make it into the snapshot and then which values are stored in the archive files such that you minimize storage space and maximize speed of data retrieval without affecting the retrieved values by more than the configured threshold.


          That's a lot of words to say that if ProcessBook is asking for a trend of the tag starting at 1am on May 31, it's assumed that the user wants to know the value at 1am on May 31. Because our storage algorithm is completely on the backend, the users should see data regardless of if a value is actually stored at that time stamp. If the reverse is true and the user actually doesn't want to see any data prior to 10am on May 31, then it's assumed that the trend's start time would have been set to 10am May 31, instead of 1am.


          If you are querying a data source such that values between two consecutive points can't be trusted, then the "correct" way to handle this in terms of the PI system would be to add a value in the archive file itself to show that the tag is offline. For example, this trend shows this:

          And this is happening because there is a value of "Shutdown" at 4am today for this tag:


          So when I query from any client tool at all for a value of this tag between the time of 1am and 4am, that value is 30. But my data source was shut down at 4am, so I don't want any client to interpolate data anymore. Starting at 4am and ending at 9am when I get another value, any client tool querying for a value in this time range will result in a "Shutdown" value, which ProcessBook renders as a gap in the trend.


          If you look at the other end of the trend where the snapshot value is extrapolating beyond the timestamp it received, this is also the way that the PI system handles current values. Every tag has a current value, and it's the latest value that the PI Server has received from the data source. Since Processbook receives updates from the PI Server for any tag that it's trending, ProcessBook assumes that the current value is true until it hears otherwise.


          If you set a trend to go into the future, for example, by setting to the end time to *+2h, you can see this demonstrated:


          We will have the current value continually extrapolated to the current time stamp, but we won't go beyond it. This is consistent with the way everything works in terms of interface polling rates and snapshot exception settings.


          To cycle back to your request, since it seems to be clashing with the way that the PI system was set up to interpret the data, I can come up with two good options:

          1. If you have a true gap in your data, I would address the inclusion of something like "Shutdown" into the archive files from the interface side, and not from the client side. If those gaps are signified in the archives themselves, then all client tools will handle this the same way and you'll have a seamless experience querying this data.

          2. If you want to only handle things from the client side and you only trust a small window of data, I would change the trend start and end times to match the window that you trust. For example, if you only sampled for 5 days at 10am every day, then I would set those exact timestamps to be the start and end of the trend.


          Does this make sense in terms of why this isn't a build in feature to turn off interpolation on only some of the values of a ProcessBook trend? If you'd like to turn off all trace lines to just show the compressed values as dots, then this is straight forward. Turn on trend markers for the trend itself, then go to each pen and turn the line style to <none>.



          This will result in no interpolation at all, although it doesn't sound like this is what you're looking for



            • Re: Drawing trend-line in ProcessBook only from first event to last event between trend-starttime and -endtime but not interpolating beyond

              Hello Kelsey,


              thank you for your detailed answer and your suggestions. I understand of course that this functionality is no standard feature and I'm afraid, that it is only solvable by programming.


              As far as I know, the goal of this requirement is to interpolate only the sample-values that belong to one batch-run and do not interpolate sample-values between different batch-runs (beyond the trend). But currently it is solved in a general way by adding No_sample-events always at start- and endtime of the trend with the PI COM Connector for OLEDB sources. Because the users only use PI BatchView-Trends and only show the data of single batch-runs this works as expected. Sorry for not mentioning that detail. But my first approach was to find a solution which replaces the current solution.


              Your first suggestion to add events to the archive, could be a solution, if we do that only for batch-start and end-times. This is not a nice solution because these events do not really belong to the raw sample-events and are only needed for visualization purposes. Currently the batch-data is written to the PI batch database by a customized application. This customized application could theoretically add these events to the archive.


              Your second suggestion does not sound like a good solution for the users, as they are working with PI BatchView-Trends and the trend-start- and -end-times should fit to the batch-start- and -end-times as they fit also to the gantt-chart.


              Perhaps you have an additional idea, when you think about only not interpolating between batch-runs. I will leave the question open for a few days. Perhaps anyone else has an idea.



              Thank you and kind regards