Indicators applied to a table

Code

[ON] T-I01

Name

Table last modification date

Use other indicators

N/A

Specification

Computes the last modification date of a table. This date is updated in real-time using a trigger on the table.

For modifications on a table performed by methods that do not activate triggers (archive import, dataspace merges) the indicator must use the history of the data to find the date of the last modification. Data history is used when the indicator is executed 'on demand' and only if the 'Use history when not real-time' input parameter is set to 'True'.

Table filter predicate

Yes

Linked records

No

Applied to D.E.C. type

Table

Output parameters

'Table last modification date' : timestamp

Storage procedure

Any resulting value is stored

Reporting table

'Flat data report' - One record is saved in the Flat data report table.

Input parameters

'Use history when not real-time': boolean

• If set to 'True': table history is used when execution is set to 'on demand'.

• If set to 'False': table history is not used when execution is set to 'on demand'.

Setting this option to 'True' is useful for updating the indicator result when a modification has occurred in the table that bypasses the trigger.

Computation frequency

'Real-time', 'On demand'

Periodicity of control and watchdog

Periodicity of control=no

Minimum threshold=no

Maximum threshold=no

Tolerance number=no

Probe

Trigger

Save last value only

Configuration

Email alert

Configuration

Data view

ONTableLastModificationDate

Code

[ON] T-I02

Name

Table updated

Use other indicators

[ON] T-I01 - Table last modification date

Specification

Check if the table has been updated during a certain period compared to the current date of the indicator execution and the minimum threshold. This computation relies on the result values of T-I01 ('Table last modification date').

The computation is performed in an 'absolute' mode, and not on a rolling basis. For example, for the periodicity 'monthly', if the last modification date is March 21 and the indicator is executed on April 1, then the table is not considered to be updated. The table is not considered to be updated if the number of updated records is less than the minimum threshold.

Table filter predicate

No

Linked records

Records of the last modification date will be stored if 'Table updated' is 'True'.

Applied to D.E.C. type

Table

Output parameters

'Table updated': boolean

• 'True': the table has been updated

• 'False': the table has not been updated

Storage procedure

Any resulting value is stored

Reporting table

'Flat data report' - One record is saved in the Flat data report table.

Input parameters

Void

Computation frequency

'On demand'

Periodicity of control and watchdog

Periodicity of control=yearly, semester, quarterly, monthly, weekly, daily

Minimum threshold=yes, label=Minimum of modification

Maximum threshold=no

Tolerance number=no

Probe

No

Save last value only

Configuration

Email alert

Configuration

Data view

ONTableUpdated

Code

[ON] T-I03

Name

Number of records

Use other indicators

N/A

Specification

Compute the number of records in a table.

Table filter predicate

Yes

Linked records

Yes

Applied to D.E.C. type

Table

Output parameters

'Number of records': integer

Storage procedure

Any resulting value is stored

Reporting table

'Flat data report' - One record is saved in the Flat data report table.

Input parameters

Void

Computation frequency

'On demand'

Periodicity of control and watchdog

Periodicity of control=no

Minimum threshold=no

Maximum threshold=no

Tolerance number=no

Probe

No

Save last value only

Configuration

Email alert

Configuration

Data view

ONNumberOfRecords

Code

[ON] T-I04

Name

Table completeness rate

Use other indicators

N/A

Specification

The completeness rate of a table is the average percentage of every record completeness rate.

Example for a table with 10 fields:

- record 01 => 3 fields empty (70% of completion)

- record 02 => 9 fields empty (10% of completion)

- record 03 => 4 fields empty (60% of completion)

The table completion rate is then: 46.66%

Table filter predicate

Yes

Linked records

Uncompleted records will be stored.

Applied to D.E.C. type

Table

Output parameters

''Table completeness rate': Decimal

Storage procedure

Any resulting value is stored

Reporting table

'Flat data report' - One record is saved in the Flat data report table.

Input parameters

Void

Computation frequency

'On demand'

Periodicity of control and watchdog

Periodicity of control=no

Minimum threshold=no

Maximum threshold=no

Tolerance number=no

Probe

No

Save last value only

Configuration

Email alert

Configuration

Data view

ONTableCompletenessRate

Code

[ON] T-I06

Name

Table freshness

Use other indicators

N/A

Specification

The freshness of a table is the freshness average of its records. The result is a number of days. The freshness of a record is computed by subtracting the execution date of the indicator from the last modification date of the record.

Example for a table with these three records:

- record 01, 2013-01-10

- record 02, 2013-01-05

- record 03, 2013-01-01

When the executed date of the indicator is 2013-01-11 the add-on computes the freshness of every record as follows:

- record 01, 1 day

- record 02, 5 days

- record 03, 10 days

Then the table freshness is: 5,33 days

Table filter predicate

Yes

Linked records

No

Applied to D.E.C. type

Table

Output parameters

''Table freshness (in days)': Decimal

Storage procedure

Any resulting value is stored

Reporting table

'Flat data report' - One record is saved in the Flat data report table.

Input parameters

Void

Computation frequency

'On demand'

Periodicity of control and watchdog

Periodicity of control=no

Minimum threshold=no

Maximum threshold=no

Tolerance number=no

Probe

No

Save last value only

Configuration

Email alert

Configuration

Data view

ONTableFreshness

Code

[ON] T-I07

Name

Number of operations on a table

Use other indicators

N/A

Specification

This indicator computes the number of operations on a table. The following computation frequencies are available for this indicator:

• Real-time: every time a creation, modification and deletion operation is done on the table, the indicator increments by one the previous number of operations.

• On-demand: the computation relies on the data history of the table and the configuration of periodicity of control.

Three figures are computed for the corresponding operations, namely create, modify and delete.

Example for a table with this initial indicator values:

- Nb. create =10

- Nb. update = 0

- Nb. delete = 5

When a new record is created then the indicator values are as follows:

- Nb. create =11

- Nb. update = 0

- Nb. delete = 5

When a record is updated:

- Nb. create =11

- Nb. update = 1

- Nb. delete = 5

Table filter predicate

No

Linked records

No

Applied to D.E.C. type

Table

Output parameters

''Number of creation': Integer

''Number of modification': Integer

''Number of deletion': Integer

Storage procedure

Any resulting value is stored

Reporting table

'Flat data report' - One record is saved in the Flat data report table.

Input parameters

Void

Computation frequency

'Real-time' and 'On-demand'

Periodicity of control and watchdog

Periodicity of control=yearly, semester, quarterly, monthly, weekly, daily, any

Minimum threshold=no

Maximum threshold=no

Tolerance number=no

Probe

Trigger

Save last value only

Configuration

Email alert

Configuration

Data view

ONNbOperationsOnTable

Code

[ON] T-I08

Name

Matching statistics

Use other indicators

N/A

Specification

This indicator computes for each matching state value the number of records and the corresponding percentage. The table must integrate the Matching meta-data type otherwise an error is raised in the overview UI result.

A parameter allow you to select which state value(s) to compute. It is possible to create several indicator definitions from the prebuilt 'Matching statistics' and get dedicated indicators to oversee one or many matching state values.

Table filter predicate

Yes

Linked records

Records of each configured matching state will be stored.

Applied to D.E.C. type

Table

Output parameters

"State": String

"Number of records per state": Integer

"Percentage per state": Percentage

Storage procedure

Any resulting value is stored

Reporting table

'Flat data report' - One record is saved in the Flat data report table for each configured state.

Input parameters

"State values": String[*] (enumeration data type). The enumeration includes the following states: definitive golden, golden, pivot, suspicious, suspect, merged, deleted, unmatched, to be matched

Computation frequency

"On demand"

Periodicity of control and watchdog

Periodicity of control=no

Minimum threshold=no

Maximum threshold=no

Tolerance number=no

Probe

No

Save last value only

Configuration

Email alert

Configuration

Data view

ONMatchingStatistics

Code

[ON] T-I11

Name

Number of active users on a table by operation

Use other indicators

N/A

Specification

This indicator computes the number of active users by operation on a table during a certain period of time. This computation relies on the data history of the table.

The computation is applied for the create, update and delete operations, not for the read.

The periodicity of control is used to define the time frame for the computation: from the current execution date of the indicator up to the periodicity of control. The computation is performed in an "absolute" mode, not on a rolling basis. For example:

- periodicity of control = "monthly",

- current execution date = January 20, 2014

The indicator saves the following values: Number of users for creation, Number of users for modification, and Number of users for deletion

When the periodicity of control is equal to "any" then all time is considered.

Table filter predicate

Yes

Linked records

No

Applied to D.E.C. type

Table

Output parameters

Number of users for creation, Number of users for modification, and Number of users for deletion

Storage procedure

Any resulting value is stored

Reporting table

'Flat data report' - One record is saved in the Flat data report table.

Input parameters

Void

Computation frequency

"On demand"

Periodicity of control and watchdog

Periodicity of control=yearly, semester, quarterly, monthly, weekly, daily, any

Minimum threshold=no

Maximum threshold=no

Tolerance number=no

Probe

No

Save last value only

Configuration

Email alert

Configuration

Data view

ONNbActiveUsersOnTableByOperation

Code

[ON] T-I12

Name

Record creation matching state

Use other indicators

N/A

Specification

Every time a record is created it saves if it is a golden or not. The Matching state meta-data is used to know if the state is golden or not.

Table filter predicate

No

Linked records

No

Applied to D.E.C. type

Table

Output parameters

''Golden record': Boolean

• 'True': the record is a golden record

• 'False': the record is not a golden record

Storage procedure

Any resulting value is stored

Reporting table

'Flat data report' - One record is saved in the Flat data report table.

Input parameters

Void

Computation frequency

'Real-time'

Periodicity of control and watchdog

Periodicity of control=no

Minimum threshold=no

Maximum threshold=no

Tolerance number=no

Probe

Trigger (on create only)

Save last value only

Configuration

Email alert

Configuration

Data view

ONRecordCreationDQState

Code

[ON] T-I13

Name

Record modification matching state

Use other indicators

N/A

Specification

Every time a record is modified it saves if it is a golden or not. The Matching state meta-data is used to know if the state is golden or not.

Table filter predicate

No

Linked records

No

Applied to D.E.C. type

Table

Output parameters

''Golden record': Boolean

• 'True': the record is a golden record

• 'False': the record is not a golden record

Storage procedure

Any resulting value is stored

Reporting table

'Flat data report' - One record is saved in the Flat data report table.

Input parameters

Void

Computation frequency

'Real-time'

Periodicity of control and watchdog

Periodicity of control=no

Minimum threshold=no

Maximum threshold=no

Tolerance number=no

Probe

Trigger (on modify only)

Save last value only

Configuration

Email alert

Configuration

Data view

ONRecordModificationDQState

Code

[ON] T-I15

Name

Number of operation by the most and least active user on a table

Use other indicators

N/A

Specification

This indicator uses the data history.

Only the create, update and delete operations are taking into consideration. The number of read operations is unknown.

The maximum of output for both most and least active users is configured through an input parameter.

Only the maximum highest active users and the maximum lowest active users are saved for the all known data history. There is no periodicity of control to apply.

Example: If the value of the input parameter "Maximum of output for most/least least active users" is "3" then the three highest active users and the three lowest active users are saved.

Table filter predicate

Yes

Linked records

No

Applied to D.E.C. type

Table

Output parameters

'Most active user': String

'Nb. operations': Integer

'Least active user': String

'Nb. operations': Integer

Storage procedure

Any resulting value is stored

Reporting table

'Flat data report' - Multiple records are saved in the Flat data report table depending on the configured maximum output number.

Input parameters

"Maximum of output for most /least active users": Integer, default value is 3, the maximum is 20.

Computation frequency

'On demand'

Periodicity of control and watchdog

Periodicity of control=no

Minimum threshold=no

Maximum threshold=no

Tolerance number=no

Probe

No

Save last value only

Configuration

Email alert

Configuration

Data view

ONNbOperationByMostAndLeastActiveUserOnTable

Code

[ON] T-I16

Name

Number of missing values in a table

Use other indicators

N/A

Specification

This indicator computes a string missing value pattern. This pattern is based on one boolean figure per field. If the value is "1" then at least one record in the table has a missing value for this field. If the value is "0" then there is no record in the table with a missing value for this field. The position in the string pattern gives the field that is considered in the table. Only terminal fields appear in the string pattern, not the group fields names.

The number of occurrences of each pattern is computed.

Example: for a Table T1(F1, F2, F3, F4) here are the existing records:

- 2 records have F4 with missing value

- 5 records have F1 with missing value

- 3 records have F3 with missing value

Then the output is: "1,0,1,1-10"

Input parameter are used to give the values string that are considered as missing values (E.g. Unknown, Not available, etc.).

Table filter predicate

Yes

Linked records

Records with missing values will be stored.

Applied to D.E.C. type

Table

Output parameters

"Missing pattern value": String

"Nb. of records: Integer

Storage procedure

Any resulting value is stored

Reporting table

'Flat data report' - One record is saved in the Flat data report table.

Input parameters

"Value similar to empty": String[*]

"Is empty value": Boolean

• "True": accept case empty value

• "False": do not accept case empty

Computation frequency

"On demand"

Periodicity of control and watchdog

Periodicity of control=no

Minimum threshold=no

Maximum threshold=no

Tolerance number=no

Probe

No

Save last value only

Configuration

Email alert

Configuration

Data view

ONNbMissingValuesInTable

Code

[ON] T-I17

Name

Number of validation items (error, warning)

Use other indicators

N/A

Specification

This indicator executes the validation report on a table and saves the number of errors and the number of warnings.

Table filter predicate

Yes

Linked records

Records belonging to one of these states will be stored:

• Records with errors (default option)

• Records with warnings

• Records with either errors or warnings

Applied to D.E.C. type

Table

Output parameters

"Number of error': Integer

"Number of warning': Integer

Storage procedure

Any resulting value is stored

Reporting table

'Flat data report' - One record is saved in the Flat data report table.

Input parameters

Void

Computation frequency

"On demand"

Periodicity of control and watchdog

Periodicity of control=no

Minimum threshold=no

Maximum threshold=no

Tolerance number=no

Probe

No

Save last value only

Configuration

Email alert

Configuration

Data view

ONNumberOfValidationItemsErrorWarning