Skip to main content
InSource Solutions

TN Hist300 Historian - Querying Values Based on their Data Quality

  1. Last updated
  2. Save as PDF

InSource_Logo_Transparent.png

Description

 

This article from InSource shows how to query values from the Historian based on their data quality.

  • Author: J. Bruce Telford
  • Published: 03/01/2019
  • Applies to: Historian (all current versions)

Details

I have seen and heard of some folks querying the Historian filtering for OPC quality of 192 (Quality = 192) only. I believe this may be because of SQL examples in training classes or other documented examples we have seen over the years, and truth be told, I used to do the same in the past also. The reality is that 192 is not the only Good quality value, there are quite a few. As referenced by the tech note Historian OPC Quality and Quality Detail Codes, if you look in the Historian “OPCQualityMap” table for a list of expected OPC quality code values, you will find Good, Bad & Uncertain codes as well. Uncertain is simply not sure (not Bad). So, while some might debate this, depending on the use case it probably makes sense that you probably need to include them as well. 

Fundamentally the “OPCQuality” values are categorized in ranges of:

  • 0 – 35 are BAD
  • 64 – 91 are UNCERTAIN
  • 192 – 219 are GOOD

In a nutshell … You can filter on “OPCQuality >= 64 AND OPCQuality < 219” when querying data from the historian (Uncertain & Good) or unless specifically requiring Good, then “OPCQuality >= 192 AND OPCQuality < 219”.

Better still, you would be better off using the “Quality” and/or “wwQualityRule” for these filters as they do a some natural grouping for you. There is some nuance to it, see the “HistorianRetrieval.pdf” (page 111 +) included with Historian installation for more detail:

  • Quality = 0 … is Good values (includes multiple Good values of Quality Detail/OPC Quality)
  • Quality = 1 … is Bad values (includes multiple Bad values of Quality Detail/OPC Quality)
  • Quality = 16 … is Uncertain (Doubtful) values
  • wwQualityRule = ‘OPTIMISTIC’ … A quality rule of OPTIMISTIC means that calculations that include some good and some NULL values do not cause the overall calculations to return NULL.
  • wwQualityRule = ‘EXTENDED’ … “A quality rule of EXTENDED means that data values with both good and uncertain OPC quality are used in the retrieval calculations. Values with bad QualityDetail indicate gaps in the data” (also the default used by the Historian).
  • wwQualityRule = ‘GOOD’ … “A quality rule of GOOD means that data values with uncertain (64) OPC quality are not used in the retrieval calculations and are ignored. Values with bad QualityDetail indicate gaps in the data”.

NOTE: “The OPTIMISTIC setting for the quality rule lets you retrieve information that is possibly incomplete but may nevertheless provide better results in the counter and integral retrieval modes where the calculation cycle contains data gaps. This setting calculates using the last known good value prior to the gap (if possible)”.

All others in the QualityMap table are WW Historian values with specific feedback defined by the QualityString for QualityDetail values, I don’t believe using QualityDetail as a filter is beneficial, and there are literally 100’s of them. However, they will likely help you troubleshooting issues when including the QualityString field to the query.

Examples:

SELECT TagName,DateTime, Value, Quality, QualityDetail ,OPCQuality ,wwQualityRule
FROM History
WHERE History.TagName IN ('Line1.Temperature')
AND Quality IN (0,1,16)
AND wwRetrievalMode = 'Cyclic'
AND wwCycleCount = 100
AND wwQualityRule = 'Optimistic'
AND DateTime >= @StartDate
AND DateTime <= @EndDate

clipboard_e9c180b4b7aa8aff43f2b73a399d7becb.png

NOTE: Even though “Quality” was set to include Bad (1), the “Optimistic” overrides the result. I other cases “Quality” can override. You will need to experiment.

SELECT TagName,DateTime, Value, Quality, QualityDetail ,OPCQuality ,wwQualityRule
FROM History
WHERE History.TagName IN ('Line1.Temperature')
AND Quality IN (0,1,16)
AND wwRetrievalMode = 'Cyclic'
AND wwCycleCount = 100
AND wwQualityRule = 'Extended'
AND DateTime >= @StartDate
AND DateTime <= @EndDate

clipboard_ebf593eb6ae57cfb1cbfe0c2807a31664.png