1. Analytical Application Infrastructure User Guide
  2. Data Management Framework
  3. Data Quality Framework
  4. Data Quality Rules
  5. Control Total Check

Control Total Check

Using Control Total Check, you can compare a constant reference value or reference entity against single or multiple values obtained by applying aggregate functions on the columns of a master/main table, with supporting dimensional filters. The dimensional filters can be time, currency, geography or so on.

There is no data correction configurable for the Control Total Check. This check provides summary level information on the entity used, attributes used, aggregate function applied, dimension-filters, group-by columns/predicates selected, number of records subject to the check and so on.

Example of Control Total Check based on Constant/Direct Value

Consider an example where you want to check the sum of loan amount for currency code ‘INR’ is greater than or equal to a Constant Value. In the LHS, select Table as “stg_loan_transactions”, Dimensional Filter as “dim_currency.n_currency_code=‘INR’“ and Group By as “dim_legal_entities.le_code, lob.lob_code, dim_branch.branch_code, dim_prodcut.product_id”. In this case, the query for LHS Criteria will be

Select sum(end_of_period_balance) from stg_loan_transactions SLT, dim_currency DC where SLT.n_currency_skey=DC.n_currency_skey and DC.n_currency_code = ‘INR’ and fic_mis_date = ‘12/12/2015’ group by dim_legal_entities.le_code, lob.lob_code, dim_branch.branch_code, dim_prodcut.product_id”

If the result of the aggregate function is greater than or equal to the specified constant value, it will be marked as Success, else Failure. After execution, the results can be viewed in DQ Reports.

Example of Control Total Check based on Reference Entity

Consider an example where you want to compare the sum of loan amount for currency code ‘INR’ with the sum of transaction amount for currency code ‘INR’ for a period with MIS DATE as 12/12/2015. In the LHS, select table as “stg_loan_transactions”, Dimensional Filter as “dim_currency.n_currency_code=‘INR’“ and Group By as “dim_legal_entities.le_code, lob.lob_code, dim_branch.branch_code, dim_prodcut.product_id”. In the RHS, select Table as “gl_master”, Dimensional Filters as “dim_currency.n_currency_code=‘INR’“ and fic_mis_date = 12/12/2015, and Group By as “dim_legal_entities.le_code, lob.lob_code, dim_branch.branch_code, dim_prodcut.product_id”. In this case, the query for LHS criteria will be same as given in the previous example and the query for RHS criteria will be:

select sum(end_of_period_balance)

from gl_master GM, dim_currency DC, dim_time_date DTD

where GM.n_currency_skey = DC.n_currency_skey and GM.gl_code = ‘LES_001’ and DTD.fic_mis_date = ‘12/12/2015’ and DC.n_currency_skey = ‘INR’

group by dim_legal_entities.le_code, dim_lob.lob_code, dim_branch.branch_code, dim_prodcut.product_id

Consider you have selected the Operator as “>=”. Then, if the result of the aggregate function in the LHS is greater than or equal to the result of the aggregate function in the RHS, it will be marked as Success, else Failure. After execution, the results can be viewed in DQ Reports.

If Control Total Check is selected, do the following:

Figure 7-45 Control Total Check pane


This image displays the Control Total Check pane.

  1. Select Table Name from the drop-down list. The list displays all the tables which are marked for Data Quality Rule in a data model; that is, based on ENABLE_CLASSIFICATION parameter. For more information, see Table Classification section.
  2. Click and select the Identifier Columns from the Column Selection window. The list displays all PK columns of the selected base table. This feature allows you to view the DQ results report based on the selected identifier columns apart from the PK columns. You can select up to 8 Identifier columns including the PK columns. It is mandatory to select the PK columns.
  3. Click and define the Filter condition using the Specify Expression window. For more information, see Define Expression.
  4. Select the Severity as Error, Warning or Information.
  5. Enter the details in the LHS grid as tabulated:

    Table 7-17 Fields in the LHS pane and their Descriptions

    Field Description
    Aggregate Expression Click and define the Aggregate Expression using the Specify Expression window. For more information, see Define Expression.
    Additional Entities Click and add additional entities if required from the Additional Entities Selection window. This is optional.
    ANSI Join Condition

    Specify ANSI Join condition if you have added Additional Entities.

    For DQ rules defined on source, prefix the table names with “$SOURCE$” if you are directly entering the ANSI Join Condition in the Expression editor.
    Join Condition Specify Join condition if you have added Additional Entities.
    Additional Condition Specify additional condition if any.
    Group By

    Specify the group by predicates/ columns by clicking and selecting Table and Column from the respective drop-down lists.

    Note:

    The group-by columns need not match the filter criteria columns in the where clause of LHS. If Group By columns are not selected on LHS and RHS, a single row on LHS will be compared with a single row on RHS.
    Group By Join Condition

    Specify the Group By Join condition in the form LHS.GRPBY_COL1 = RHS.GRPBY_COL1 AND LHS.GRPBY_COL2 = RHS.GRPBY_COL2 and so on. LHS and RHS will be joined based on this.

    If the number of Group By columns on LHS does not match with the number of Group By columns on RHS, it is mandatory to enter Group By Join Condition.

    If Group By Join Condition is not specified and the number of Group By columns on LHS and RHS are equal, Group By Join Condition will be automatically generated in the form “LHS.GRPBY_COL1 = RHS.GRPBY_COL1 AND LHS.GRPBY_COL2 = RHS.GRPBY_COL2”.

    If Group By columns are present only on LHS, every row on LHS will be compared against the single row on RHS. Group By Join Condition will be generated in the form “RHS.R_ID=1”.

    If Group By columns are present only on RHS, the single row in LHS will be compared against every row on RHS. Group By Join Condition will be generated in the form “LHS.L_ID=1”.

  6. Select the appropriate Operator from the drop-down list. The available operators are >, <, =, <>, <=, and >=. Evaluation is done based on the selected numeric operator.
  7. Select the Reference Type as:
    • Direct Value - Enter the reference value in the Value field.
    • Another Entity - This is used when you want to compare LHS with a different entity with its set of attributes. Enter the details as follows:
      • Reference Base Table - Select the reference table from the drop-down list.
      • Specify Aggregate Expression, Additional Entities, ANSI Join Condition, Join Condition, Additional Condition, and Group By in the respective fields. For more information, see the preceding table.
    • Relative Reference - Here Reference value is the same aggregate function on the subject entity itself, but dimensional filters can vary. Reference Base Table and Aggregate Expression are pre-seeded as in the LHS grid. You cannot modify them. Enter the other details as follows:
      • Specify Additional Entities, ANSI Join Condition, Join Condition, Additional Condition and Group By in the respective fields.

        For more information, see the preceding Specify Additional Entities, ANSI Join Condition, Join Condition, Additional Condition and Group By in the respective fields. table.

      Note:

      Control Total Check is allowed only on numeric columns.

      Group By clauses on LHS and RHS should be defined in such a way that output of RHS and LHS are semantically correct to be compared. That is, RHS and LHS should not result in two different sets that cannot be compared against. Hence, ensure the rule definitions are technically validated to meet this.

  8. Click Generate Query.

    The details are validated and the validated query along with the status is displayed in the Generated Query section.

  9. Click Save.

    The defined Data Quality Rule definition is displayed in the Data Quality Rule Summary window with the Status as “Saved” and Active as "N". After it is approved, it becomes active. If you are mapped to the DQAUTOAUTHR role, the definition is automatically authorized and it becomes active.

    Note:

    No corrections or assignments are done by the framework for Control Total Check.