Data loss between sensor and PI server can happen due to network issues when sensor side has no data buffering feature. The time-series between two PI tag values will be used to identify such data losses. One way is using OSISoft PI DataLink for Microsoft Excel that this article will describe.
Compressed Data formula has to be used on the cell of MS Excel page with the following parameters:
- Data Item(s) – PI_tag
- Start time – start of timeseries to be analyzed (e.g. *-1y)
- End time – end of timeseries to be analyzed (e.g. *)
- Filter expression – ‘PI_tag’ <> “Bad Input”
- Show Time stamps is selected
Store the output in csv and load it as table into MS SQL Server.
Use LAG function to find the gap between time-series:
SELECT column1, column2, datediff(MINUTE, LAG(column1, 1) OVER (ORDER BY column1), column1) diff
FROM [dbo].[22QI52001_4] t
Apply max, sum, avg, and count functions to the script above. Please note that if gap can be considered as acceptable, so use diff > 2 instead of diff > 1:
Select max(diff)
from
(
SELECT column1, column2, datediff(MINUTE, LAG(column1, 1) OVER (ORDER BY column1), column1) diff
FROM [dbo].[22QI52001_4] t
) p
where p.diff > 2
However, due to large amount of data this method might not be suitable for long time-series with frequent value generation (e.g. more than 2y)
Source
SQL Server LAG() Function By Practical Examples:
https://www.sqlservertutorial.net/sql-server-window-functions/sql-server-lag-function/
Article in PI Square: