Migrating PIAdvCalcFilVal Calculations from PI DataLink to PI OLEDB: Calculating Uptime Based on Conditions

Migrating calculations from PI DataLink's advanced functions to PI OLEDB often presents unique challenges. A common use case is translating the PIAdvCalcFilVal() function—used for advanced, condition-based calculations in Excel—into an equivalent SQL query via PI OLEDB. This blog post explores this migration, focusing specifically on calculating equipment "uptime" based on a tag condition over a given time range.

The Challenge

A customer requirement called for migration of the following DataLink function to OLEDB:

=PIAdvCalcFilVal("TAG","y+6h","t+6h","'TAG'>50","count","time-weighted","pt. compressed","10m",0,1,0,"SERVER")

This advanced calculation counts (with time-weighting) the number of intervals where the tag value is greater than 50 between 'y+6h' and 't+6h'. The result is typically divided by 3600 to derive uptime in hours.

Key Considerations in OLEDB Migration

When translating such logic to PI OLEDB, several factors must be considered:

• Choice of calculation basis: PIAdvCalcFilVal allows both event-weighted and time-weighted options.
• Aggregation method: Whether counting, summing, or calculating uptime as a percent or as duration.
• Interval and step handling: How to mimic the requested time-step or chunking of data.

Approaches in PI OLEDB

Multiple OLEDB strategies exist for calculation of uptime or event duration based on tag conditions. Here are several proven examples:

1. Using the PIAVG Table to Calculate Uptime Percentage

The PIAVG (PI Archive Average) table can provide the fraction of a time period when a condition was true, which can be converted to duration:

SELECT VALUE * 86400 / 3600 AS UPTIME_HOURS
FROM PIARCHIVE..PIAVG
WHERE EXPR = '''TAG'' > 50'
  AND TIME BETWEEN 'y+6h' AND 't+6h'
  AND CALCBASIS = 'TIMEWEIGHTEDDISCRETE'

• VALUE * 86400 gives the number of seconds the condition was true (assuming the interval is one day; adjust 86400 as needed).
• Divide by 3600 to convert to hours.

2. Counting Interpolated Intervals with the PIINTERP Table

For precise per-second (or other interval) evaluation of a condition, count the number of rows where the tag exceeds the threshold:

SELECT COUNT(ROWCOUNT) / 3600 AS UPTIME_HOURS
FROM (
  SELECT 1 AS ROWCOUNT
  FROM PIARCHIVE..PIINTERP
  WHERE TAG = 'TAG'
    AND TIME BETWEEN 'y+6h' AND 't+6h'
    AND TIMESTEP = '1s'
    AND VALUE > 50
) T1

Each row represents one second; summing and dividing by 3600 yields uptime in hours.

3. Filtering Directly in PICOunt Using Expressions

Rather than joining multiple tables, you can leverage the EXPR column directly for conditional counting:

SELECT VALUE
FROM PIARCHIVE..PICOunt
WHERE EXPR = '''TAG'' > 50'
  AND TIME BETWEEN 'y+6h' AND 't+6h'
  AND CALCBASIS = 'TIMEWEIGHTED'

Tips & Caveats

• Ensure the time range matches your calculation needs.
• Adjust CALCBASIS and TIMESTEP to match the original PIAdvCalcFilVal behavior.
• Some DataLink options (like 'pt. compressed') may not map directly to OLEDB; test different approaches.
• Always validate results with known-good DataLink calculations to avoid subtle discrepancies (as seen when initial queries were off by several minutes).

Conclusion

Translating PIAdvCalcFilVal functions for advanced, condition-based uptime calculations into PI OLEDB is entirely feasible—once you understand how to use PIAVG, PIINTERP, and PICOunt tables effectively. Each method provides different perspectives and has different implications for accuracy and performance. Always cross-validate with your original DataLink results, and consider edge cases, such as interval overlaps or tag compression.

Have you tackled a similar migration? Share your experiences and tips in the comments below!