Scenario 9: Understand and use data

Data that is not 100% reliable can still be of value. Using data quality parameters, you can find out why data quality was marked uncertain, and when you find it useful, add data marked "uncertain" to your results.

You can use data quality indicators to answer questions like:

• What data did we lose because of a device failure?

• What was the volume readout before the power outage?

Data Quality in AVEVA Historian

When AVEVA Historian receives data from a data source, it adds a timestamp and quality indicator to the data value. Then, it stores the combined value, timestamp, and quality indicator (VTQ) as a data tag. The quality indicator has two parts, exposed by two separate columns:

• OPCQuality: What was the quality of this data as reported by the source?

• QualityDetail: What special processing has AVEVA Historian performed on the data?

OPC Data Quality: The Good, the Bad, and the Uncertain

OPC Data Access is an industry standard for real-time data exchange between hardware and software components. This standard defines a consistent way of exchanging live values by appending a data value with a timestamp and a quality value. In AVEVA Historian, this quality is exposed in the OPCQuality column. OPC defines quality as a 16-bit integer. The most significant bits for Historian are bits 6 and 7. They indicate whether the data's overall quality is "good", "bad" or "uncertain".

Historian stores and retrieves the timestamps and qualities for "good", "bad", and "uncertain" data. Historian always converts values with "bad" quality to NULL to protect users and applications from unwittingly propagating these values known to be bad.

The OPC standard allows status of "bad" to include a substatus. For example, a substatus of "5" means "Bad, Last Known Value" and "3" means "Bad, Device Failure". In a strict "current value only" protocol (like OPC DA), the best you can do is provide the quality and that previous value. However, AVEVA Historian will also store that previous value (with its quality and timestamp).

Note: Although in many cases OPCQuality and QualityDetail use the same codes to indicate similar concepts (for example, "192" indicates normal "good" values), they will often be different and should not be used interchangeably.

QualityDetail: More to the Story

QualityDetail tells what happened with data while it was processed by various AVEVA Historian subsystems. For example, it can indicate that some data was stored as the first value after the connection to the historian was restored after a temporary disconnect, or it can tell that the retrieved value is not the actual value generated by an I/O Server or DAServer, but a manually corrected one.

Quality detail codes are metadata used to describe data values stored or retrieved using the AVEVA Historian. Quality details are 2 byte values that use the low order byte for the basic quality detail and the high order byte to store quality detail flags. The basic quality detail and quality detail flags are OR'd together.

Viewing Quality Values and Details

Descriptive labels for the many OPC quality codes are stored in the OPCQualityMap table of the Runtime database. To view OPCQuality values and descriptions, execute the following query:

SELECT OPCQuality, Description FROM OPCQualityMap

Similarly, descriptive labels for the many quality detail codes are stored in the QualityMap table of the Runtime database. To view the complete list of QualityDetail values and descriptions, execute the following query:

SELECT QualityDetail, QualityString FROM QualityMap

Note: Although a quality detail of 65536 is used to indicate block gaps, NULL values are not produced for block gaps.

Using WWQualityRule

AVEVA Historian provides three different options for interpreting the stored OPCQuality:

• Good: Filters out "uncertain" values, as if they did not exist, and converts "bad" values to NULL.

• Extended: Includes "uncertain" values, but takes them out of the "PercentGood" calculations and makes the OPCQuality "uncertain" for calculated results that include included them. Extended also converts any "bad" valuesto NULL.

• Optimistic: Filters out "bad", but is otherwise similar to "extended".

Examples: Using OPCQuality to Refine Results

Suppose you wanted a report to tell you the average production level for a certain storage tank for each 8-hour shift. You could run this query:

select DateTime, Value, OPCQuality, PercentGood

from History

where TagName like 'Flow273.PV'

and DateTime > '2016-03-02 0:00'

and DateTime <= '2016-03-02 3:00'

and wwRetrievalMode = 'integral'

and wwResolution = 60*60*1000

and wwInterpolationType = 'stairstep'

--and wwQualityRule = 'extended'

Note that the last line is commented out because "QualityRule = 'extended'" is the default.

The results are:

To force a query to exclude points with uncertain OPC quality, set wwQualityRule to Good. For example, you can change the previous example to this:

select DateTime, Value, OPCQuality, PercentGood

from History

where TagName like 'Flow273.PV'

and DateTime > '2016-03-02 0:00'

and DateTime <= '2016-03-02 3:00'

and wwRetrievalMode = 'integral'

and wwResolution = 60*60*1000

and wwInterpolationType = 'stairstep'

and wwQualityRule = 'good'

The results are:

A third option is to change the quality rule to Optimistic:

select DateTime, Value, OPCQuality, PercentGood

from History

where TagName like 'Flow273.PV'

and DateTime > '2016-03-02 0:00'

and DateTime <= '2016-03-02 3:00'

and wwRetrievalMode = 'integral'

and wwResolution = 60*60*1000

and wwInterpolationType = 'stairstep'

and wwQualityRule = 'optimistic'

The results are:

Finally, queries to AnalogSummaryHistory use the Optimistic quality rule exclusively . For example:

select StartDateTime, Value, OPCQuality, PercentGood

from AnalogSummaryHistory

where TagName like 'Flow273.PV'

and DateTime > '2016-03-02 0:00'

and DateTime <= '2016-03-02 3:00'

and wwRetrievalMode = 'integral'

and wwResolution = 60*60*1000

and wwInterpolationType = 'stairstep'

and wwQualityRule = 'optimistic'

The results are: