Writing a report in Excel using the SDK and need to calculate the length of time tags were in certain conditions. What is the SDK equivalent of the Time Filtered Function?

Writing a report in Excel using the SDK and need to calculate the length of time tags were in certain conditions. What is the SDK equivalent of the Time Filtered Function?

Thanks for the response Steve, but still need some assistance. I used the IPICalculation help file as a resource but have not been able to collect the data that I need for the report. Here is the datalink function I need to replicate in the SDK - PITimeFilterVal("'tag1'>0",start_time,end_time,"hours", 0,"server") and output this to a cell. Using the IPICalculation methods I can get the events that meet thsi expression but not the amount of time.

Why use the SDK? Mainly for perfromance issues and ease of use for our engineers. This current report is an emissions report and has a lot of data and with all the datalink function is would take forever to update, we also do a lot reports involving PIBatch. Our engineers like reports where they enter a start time, end time, and product and then just click an update button to run the report and then just have data in the cells instead of arrays and functions.

Datalink uses a little trick to calculate that:

Dim myPISdk As PISDK.PISDK

Dim myPIServer As PISDK.Server

Dim myIPICalc As PISDK.IPICalculation

Dim myPIValues As PISDK.PIValues

Dim myStartTime As PITimeServer.DynamicTime

Dim myEndTime As PITimeServer.DynamicTime

Dim myTimeSpanSeconds As Integer

Dim myFilterExpression AsStringmyPISdk = New PISDK.PISDK

myPIServer = myPISdk.Servers.DefaultServer

myIPICalc = myPIServermyStartTime = New PITimeServer.DynamicTime

myEndTime = New PITimeServer.DynamicTimemyStartTime.InputString = "Y"

myEndTime.InputString = "T"myFilterExpression = "'BA:PHASE.1' <> ""PHASE1"""

myPIValues = myIPICalc.PercentTrue(myStartTime, _

myEndTime, _

Nothing, _

myFilterExpression, _

PISDK.FilterSampleTypeConstants.fstExpRecordedValues)myTimeSpanSeconds = myEndTime.LocalDate.Subtract(myStartTime.LocalDate).TotalSeconds

MsgBox(myPIValues(1).Value.ToString() _

+ Environment.NewLine _

+ "Total time (seconds): " _

+ (myPIValues(1).Value / 100.0 * _

myTimeSpanSeconds).ToString())Instead of calculating the time you have to calculate the percenttrue for the expression and afterwards multiply it by the time interval.

I just asked myself same question this morning : "where's hide this 'time filtered' Datalink function into the SDK ?"

I know that i previously saw something on vCampus, and found this useful thread.

I did the following in excel/datalink :

filter expression: 'SINUSOID' > 80

start time : y

end time : t

server : es_mpi

time units : seconds

Gave me a nice 24648 seconds in the excel sheet

Then i just used your code to check if results are consistent.

Only change to your code was :

myPIServer = myPISdk.Servers("es_mpi") myStartTime.InputString = "Y" myEndTime.InputString = "T" myFilterExpression = "'SINUSOID' > 80"

Surprisingly gave me a 23910 seconds

I also tested this SDK method through Excel VBA with following code :

Dim toto As IPICalculation Set toto = Me.Worksheets(1).PISrvPickList1.SelectedServer Dim res As PIValues Set res = toto.PercentTrue(StartTime, EndTime, "", "'SINUSOID'>80", fstExpRecordedValues) Dim ts As Long ts = DateTime.DateDiff("s", StartTime, EndTime) Dim res As Long res = ts * res(1)

Gave me same result as yours : 23910 seconds

Andreas/Steve am I missing something somewhere ?

Thank you

Pierre

Pierre,

turns out that the PISDK emulation and the DataLink function return identical results only for digital tags. I will do some further research and post my results.

Thank you Andreas.

I made a quick and dirty Excel VBA to find if this was only wrong with your code but it seems that it's a language independant 'issue'.

Isn't this because for a non-digtal point type there will be interpolation between recorded values at the time boundary? A digital point is either a state or not, numerical (if not stepped) points will be interpolated at your time boundary to check your expression.

Have you tried the same check with a stepped point? (I haven't but assume you get the same behaviour as a digital point).

As a side note, your Excel VBA code looks incorrect. You have the variable "res" declared twice and your last line should read "res = ts * (res(1)/100)".

Yes sorry, it should be something like you said.

Dim res2 As Long

res2 = ts * res(1) / 100

I made a quick merge from 2 functions to explain but did it without exhaustive checking.

I think I'll be back on this issue in a few days, hope we get further then.

edit : i just switched step attribute from SINUSOID point and your thought was correct Rhys as I get the result 23910, same as result from vba snippet of Andreas.

Maybe Datalink is doing another calculation method depending on step attribute ?

Yes, it boils down to how best you can interpret a time interval for both stepped/digital and continuous-type points. While stepped points have clear-cut transitions from one state to another, continuous points are sampled only once every so often and the intermediate values are interpolated. That would cause some difference in interpreting the time interval in which a point takes a certain value.

After adapting Andreas' code to Excel VBA and performing some testing, I found out what is required to make the code match the result of the DataLink Time Filtered function:

Dim myPISdk As PISDK.PISDK Dim myPIServer As PISDK.Server Dim myIPICalc As PISDK.IPICalculation Dim myPIValues As PISDK.PIValues Dim myStartTime As PITimeServer.DynamicTime Dim myEndTime As PITimeServer.DynamicTime Dim myTimeSpanSeconds As Long Dim myFilterExpression As String Set myPISdk = New PISDK.PISDK Set myPIServer = myPISdk.Servers.DefaultServer Set myIPICalc = myPIServer Set myStartTime = New PITimeServer.DynamicTime Set myEndTime = New PITimeServer.DynamicTime myStartTime.InputString = "y" myEndTime.InputString = "t" myFilterExpression = Worksheets("Plan1").Range("B1").Text Set myPIValues = myIPICalc.PercentTrue(myStartTime, _ myEndTime, _ "", _ myFilterExpression, _ fstExpRecValwithMinSampTime, _ "10m") myTimeSpanSeconds = myEndTime.UTCSeconds - myStartTime.UTCSeconds MsgBox (CStr(myPIValues(1).Value) _ + vbCrLf _ + "Total time (seconds): " _ + CStr(myPIValues(1).Value / 100# * _ myTimeSpanSeconds))

As you can see, the idea is to use the fstExpRecValwithMinSampTime option for the SampleType parameter of the PercentTrue Method of the IPICalculation interface with a 10-minute SampleInterval.

From the PI-SDK documentation:

*fstExpRecValWithMinSampTime: This option allows the user to get more accurate answer than the fstExpRecordedValues option by evaluating the expression more frequently. Normally, this option is only necessary if the expression involves comparison of analog tag values. Also, the PI 3 server limits SampleInterval to between 1/100 and 1/10000 of the calculation period. PIAPI uses this option for PI 3.x servers.*Thanks Daniel for doing this evaluation and finding out the solution!

Actually I was talking to the developers and DataLink uses the exact same call.

Thanks for confirming that, Andreas!

After posting that comment, I observed that the same page of the PI-SDK documentation has some futher explanation on the fstExpRecValwithMinSampTime option:

*This option is the same as fstExpRecordedValues with the additional requirement that the evaluation interval has to be less than or equal to the passed interval (SampleInterval). Therefore, the number of evaluations using this option will always be more or the same as the number of evaluations using the fstExpRecordedValues option or fstInterval option with the same SampleInterval. This option is NOT IMPLEMENTED for PI 2 server.*I tried to emulate the fstExpRecValwithMinSampTime option with some Excel and DataLink functions (not using the DataLink Time Filtered function, of course), to make sure I understood what exactly the fstExpRecValwithMinSampTime option does, but haven't been able to make the results in Excel/DataLink match the results for the fstExpRecValwithMinSampTime option.

I am attaching the Excel worksheet I used for these attempts. Each sheet in the file has a copy with pasted values, so that you can see what I got here, even if you don't have a connection to my PI System.

What I did in Excel/DataLink for "emulation attempt 1" was:

- use the DataLink Compressed Data function
- create manually a sorted array of timestamps containing
- the timestamps retrieved in step 1 (marked in green),
- the start time of the period (marked in green),
- the end time of the period (marked in green) and
- "blue" timestamps separated by 10-minute intervals between consecutive "green" timestamps

- use the DataLink Timed Data function to get the values for the array of timestamps on step 2
- for every value retrieved in step 3: if it is greater than 80, then return how many seconds there are between the timestamp of the current value and the next; else return 0
- sum the time periods obtained on step 4

What I did in Excel/DataLink for "emulation attempt 2" is almost the same as in "emulation attemp 1" and the difference is only on step 2. However, neither the result of emulation attempt 1 nor the result of emulation attempt 2 matched the result for the fstExpRecValwithMinSampTime option.

Could anyone maybe tell me what is missing here?

Daniel,

I have a few questions to clear up what you exactly did to emulate the behavior.

- Which result is right? What you get using DataLink or what you get out of your emulation?
- Why do you divide up the time between samples to 10 minutes? Interpolating may be playing a role messing up your time calculations.
- Can you try making your time interval break-downs smaller (e.g. a few seconds instead of 10 minutes) to see if the two results get closer to each other?

Ahmad

Which result is right? What you get using DataLink or what you get out of your emulation?

Let's first define what's right in this specific situation...

The tag I am using is SINUSOID and this is a ProcessBook trend of the tag over the period I am analyzing:

We want to know how long the tag had value greater than 80 in this 12-hour period. This corresponds to the time between the two red circular markers I've drawn above.

In order to get this time with a precision that I consider to be highest possible, I calculated the exact timestamp of the first red marker (based on the timestamps of its previous and next archived events) and did the same thing for the second red marker. This gave me 12373 seconds and this is the result I consider to be absolutely right.

However, my point here is not to calculate the "right" result, but to find out what exactly the fstExpRecValwithMinSampTime option does.

Having said all this, this is the answer to your first question: neither the DataLink Time Filtered function nor any of my emulations gave me the result that I consider to be "right". The DataLink Time Filter function returned 12597 seconds, while emulation #1 returned 12261 seconds and emulation #2 returned 12420 seconds.

Ahmad

Why do you divide up the time between samples to 10 minutes? Interpolating may be playing a role messing up your time calculations.

We know that the DataLink Time Filtered function uses the fstExpRecValwithMinSampTime option with a 10-minute SampleInterval, as Andreas confirmed with the developers. I am now trying to emulate what fstExpRecValwithMinSampTime option does for the DataLink function behind the scenes, so that I can be sure that I understand what exactly the fstExpRecValwithMinSampTime option does in general.

We know that a picture is worth a thousand words, so I took the time to try to illustrate the possible interpretations I have for the documentation of the fstExpRecValwithMinSampTime option.

The red markers in the time axis indicate the timestamps of the events in archive, while the blue markers in the time axis correspond to sampled timestamps. These are the timestamps which I am using the DataLink Timed Data function with.

"Delta t" stands for the sample interval.

interpretation #1 (emulation #1)

interpretation #2 (emulation #2)

I just made a third interpretation:

I implemented this third interpretation and it gave me the result 12600 seconds, which is closest to the DataLink Time Filtered function (12597 seconds). Really close, by the way, the difference is only 3 seconds.

Ahmad

Can you try making your time interval break-downs smaller (e.g. a few seconds instead of 10 minutes) to see if the two results get closer to each other?

Well, I could do this, but this wouldn't help in emulating what the fstExpRecValwithMinSampTime option does for the DataLink function behind the scenes. As I said, I am doing this to make sure I understood what exactly the fstExpRecValwithMinSampTime option does, as the PI-SDK documentation was not clear enough for me.

PIDatalink just calls the PISDK IPICalculation.PercentTrue. For the option of ExpRecWithMinSampTime, PI server limits the sample interval length to between 1/100 and 1/10000 of the calculation time range (depending on the version). For example, if the requested time range is one day, the user input sample interval will be limited to 8.64 sec to 864 sec.

There is also an inherited inaccuracy for the PercentTrue calculation. Basically, calculating the exact time when the expression changes sign (T to F or F to T) would be expensive on the server, so the server just evaluates the expression at selected times and assumes the result of the expression holds over the interval until the next evaluation point.

The accurate way to get timeTrue for expressions like sinusoid > 50 is to call PE built-in function TimeGE(tag,50). The PE built-in function will interpolate sinusoid value to find the time where the value is 50. But the function is limited to one tag and one form of the expression.

Ahmad

There is also an inherited inaccuracy for the PercentTrue calculation. Basically, calculating the exact time when the expression changes sign (T to F or F to T) would be expensive on the server, so**the server just evaluates the expression at selected times and assumes the result of the expression holds over the interval until the next evaluation point.**

This is exactly my point. Which are these selected times?

Let's go back to the concrete case I tested. These were the timestamps where the SINUSOID tag had compressed data between 06-dec-10 00:00 and 06-dec-10 12:00:

06-dec-10 01:30:21

06-dec-10 02:39:21

06-dec-10 03:44:21

06-dec-10 04:57:21

06-dec-10 07:30:51

06-dec-10 08:39:51

06-dec-10 09:44:51

06-dec-10 10:57:51

The SampleInterval parameter was set to 10 minutes. So, in this case, which would be the times selected by the fstExpRecValwithMinSampTime option to evaluate the expression 'SINUSOID'>80 ?Daniel,

Here is the algorithm:

1. First the server looks at all the input tags used in the expression (there could be more than one) and gets the archived events for all these tags for the requested time range.

2. Merge all these timestamps into one series. This is the ExpRecordedValues option.

3. With the ExpRecValWithMinSampTime, the interval between the timestamps obtained in step 2 is further reduced, if necessary, to the MinSampTime. Also, the user support minTime itself is subjected to the range limit.

In the example of 06-dec-10 00:00 and 06-dec-10 12:00:

PI DataLink default of 10 minute minSampTime becomes 7.2 minutes (e.g. 12 hours/100 is the high limit of the ministep).

So starting with 06-Dec-10 00:00, the server evaluates the expression with the 7.2-minute time increment. After 125 evaluation, the timestamp is 1:30. Since there is an archive event at 1:30:21 which is smaller than the next timestamp computed from the ministep (1:37.2), the archive event will be used for next evaluation. Then the next timestamp is the archive event + 7.2 minutes and so on until the interval to the next archive event is smaller than the ministep.

If the user is calling PISDK directly and passes the sampleInterval of one second, then the lower limit of 12 hours/10000 or 4.32 sec will be used as the ministep.

Thank you very much, Ahmad! Now it's all crystal clear!

Reading your detailed explanation of the algorithm applied to the concrete case I tested, I realized that I overlooked this remark for the fstExpRecValWithMinSampTime option and SampleInterval in the PI-SDK documentation:

"the PI 3 server limits SampleInterval to between 1/100 and 1/10000 of the calculation period."

I adapted the calculations in my spreadsheet and now it matches the DataLink Time Filtered function.

The PI SDK equivalent of the PI DataLink "Time Filtered" function is the TimedCalculate method from the IPICalculation interface.

Just curious though... why is it that you are writing a report using the PI SDK? Any specific functionality of PI DataLink that's either missing or that doesn't suit your needs?