Our Tags are of 4 distinct types:
--- Real Time (1,904 of those) - gather data as and when it changes. Could be as often as every few seconds or rarer than once in 24 hours.
--- 1 Minute Average (490) – receive values every minute even if that value is zero
--- 30 Minute Average (1,824) – receive values every 30 minutes even if that value is zero
the above 3 can receive both positive and negative values though most only hold positive.
--- 30 Minute Average Imp/Exp (1,004) – receive values every 30 minutes even if that value is zero. These tags exist as pairs one for Import data the other for Export. Typically one will show zero when the other has a (positive only) value other than zero but as the power flow can change during the half-hour there could be values in both tags of a pair.
Using ProcessBook, it is easy for the experienced eye to spot the anomalies. That’s fine for a few tags but what I desperately needed was a repeatable, automatic process that would identify anything other than actual values - zero’s being treated as potential errors until proven wrong and the assumption being that actual +ve or –ve values are good data of course.
I started off with a spreadsheet to gather the data and used Conditional Formatting to highlight offending cells. I could then ‘see’ all of the data at once – but it was too much. I’d been using this approach for a few years (being a power engineer and not a mathematician!) until I recently hit on the idea to rank the failures/zeros. Recent failures were of greater importance than more historical zero’s or errors. I gave each cell a score, if it failed a validation test, based on powers of 2; the most recent I gave 2^0, the previous value got 2^1, and so on. Summating these scores and sorting gave me what I wanted but at a price – it took ages to refresh as I was looking at several days worth of data at a time. I was not interested in occasional failures but I did want to identify long term problems. The compromise between performance and a bigger picture was settled on by gathering 96 values/tag over 8 days (i.e. once every 4 hours).
There is still so much data on the screen that the data columns are only 5 pixels wide but Conditional Formatting is still there to ‘show’ me the data. My next challenge is to use more VBA to fully automate the process allowing me time to make another cup of coffee!
That’s my story, not very well written and probably less well implemented but it does the job. I would however be very interested in any other methods of achieving the same.
Regards,
Miles
MilesUK
ProcessBook v3.0.15.3
