Alibek

How to identify data losses between sensor and PI server using PI Datalink and MS SQL

Blog Post created by Alibek on Sep 2, 2020

The idea is to get the time between two PI tag values to identify data loss between sensor and PI server.
First way is using PI DataLink for Excel by putting Compressed Data formula on the cell 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 the slight 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)

 

Sources:

SQL Server LAG() Function By Practical Examples 

Outcomes