This chapter provides information about Data Quality Rules Execution in the Oracle Financial Services Data Foundation application and step-by-step instructions to use this section.
Topics:
Data Quality Framework consists of a scalable rule-based engine which uses a single-pass integration process to standardize, match, and duplicate information across global data. Data Quality Framework within the Infrastructure system facilitates you to define rules and execute them to query, validate, and correct the transformed data existing in an Information Domain.
You can access Data Quality Framework by expanding the Data Integrator Framework within the Unified Metadata Manager section in tree structure of LHS menu.
Data Quality Framework consists of the following sections:
· Data Quality Summary
· Data Quality Group Summary
Topics:
· Approve/Reject Data Quality Rule
Data Quality Summary within the Data Integrator framework of Infrastructure system facilitates you to create a DQ (Data Quality) definition and define nine specific validation checks based on Range, Data Length, Column Reference/Specific Value, List of Value/Code, Null Value, Blank Value, Referential Integrity, Duplicity, and Custom Check/Business. You can also correct data for range, column reference, list of values, null value, and blank value parameters.
The defined Data Quality Rule checks can be logically grouped and executed together. You (Business Analysts) need to have ETL Analyst function role mapped to access the Data Quality Summary framework within the Infrastructure system.
You can access Data Quality Summary by expanding the Data Quality framework within the Unified Metadata Manager section in tree structure of LHS menu.
Figure 109: Data Quality Rule Summary page
The Data Quality Rule Summary page displays the list of pre-defined Data Quality Rules with the other details such as DQ Name, Table Name, Access Type, Check Type, Folder, Creation Date, Created By, Last Modification Date, and Status of the Rule. A defined rule is displayed in Saved status, until it is Approved/Rejected by the approver. An Approved rule can be grouped in order for execution and a Rejected rule is sent back to the user with the Approver comments.
You can add, view, modify, copy, approve/reject, or delete Data Quality Rules within the Data Quality Rule Summary screen. You can also make use of Search and Pagination options to search for a Data Quality Rule based on DQ Name, Table Name, Folder, or Check Type and view the existing Data Quality Rules within the system.
For more information, refer to Oracle Financial Services Analytical Applications Infrastructure User Manual.
You can create a Data Quality Rule definition by specifying the DQ Definition details along with the type of validation check on the required table and defining the required validation conditions to query and correct the transformed data.
To create Data Quality Rule in the Data Quality Rule Summary page, follow these steps:
1. Click Add in the Data Quality Rules tool bar. Add button is disabled if you have selected any check box in the grid. The Data Quality Definition page is displayed.
Figure 110: DQ Definition page
2. In the DQ definition section, perform the following actions:
§ Enter the Name by which you can identify the DQ definition.
§ Enter a Description or related information about the definition.
§ Select the Folder (available for selected Information Domain) from the drop down list.
§ Select the Access Type as either Read Only or Read/Write.
3. Select the Check Type from the drop down list. You can mouse‐over i icon for information.
§ Select Specific Check, if the defined conditions are based on individual checks on a single column.
§ Select Generic Check, if the defined conditions are based on multiple columns of a single base table. These checks are not pre-defined and can be specified (user-defined) as required.
If Specific Check is selected, perform the following:
· Select Table Name and Base Column Name from the drop down list. The list displays all the tables which are marked for Data Quality Rule in a data model, which has the table classification property code set to 340.
· (Optional) If you have selected Base Column of type Varchar/Char, select the Substring check box, enter numeric values in Parameters Position and Length fields.
· Click 
and define
the Filter condition using the Specify
Expression page.
· Define the required Validation Checks by selecting the appropriate grid and specify the details. You can define nine specific validation checks based on Range, Data Length, Column Reference/Specific Value, List of Value/Code, Null Value, Blank Value, Referential Integrity, Duplicity, and Custom Check/Business.
For more information, see the Oracle Financial Services Advanced Analytical Applications Infrastructure User Guide Release 8.1.0.0.0.
NOTE |
A minimum of one Validation check must be defined to generate a query. |
· Click Generate Query. The details are validated and the validated query along with the status is displayed in the Generated Query section.
If Generic Check is selected, perform the following actions:
· 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, which has the table classification property code set to 340.
· Click 
and define
the Filter condition using the Specify
Expression page.
· Click Add in the Condition grid. The Specify Expression page is displayed. Define the Condition expression.
The Expression is displayed with the "IF" and "Else" conditions along with the Severity status as either Error or
NOTE |
You can change the Severity by selecting from the drop down list. |
NOTE |
You can add an Assignment only when the Severity is selected as Warning. Assignments are added when you want to correct or update record(s) in base column data / selected column data. There can be one or more assignments tagged to a single condition. However, selecting severity as Error indicates there are no corrections and only facilitates in reporting the quantity of bad records. |
· Select the check box adjacent to the required Condition expression and click Add in the Assignment grid. The assignment details are populated.
NOTE |
You can add an Assignment only if the Severity is Warning. There can be one or more assignments tagged to a single condition. |
· Specify the Assignment details as tabulated.
Table 61: Assignment Details
Field |
Description |
Column Name |
Select the Column Name from the drop down list. |
Assignment Type |
Select the Assignment Type as one of the following: · No Assignment is the default selected assignment which does not have any target column update, but the message details are pushed. · Direct Value - enter the Assigned Value · Another Column - select the required Column as Assigned Value from the drop down list. · Code - select the required Code as Assigned Value from the drop down list if any code / leaf values exist for the selected base column. · If not, you are alerted with a message indicating that No Code values exists for the selected base column. |
Assignment Value |
Select the Assignment Value from the drop‐down list according to the Assignment Type selected. |
Message Severity |
Select the Message Severity as either 1 or 2 from the drop down list. |
Message |
Select the required Message for the Severity from the drop down list. |
You can also add multiple assignments by clicking Add in Assignment grid.
NOTE |
Minimum of one condition needs to be defined to save the Rule. |
Click Save. The defined Data Quality Rule definition is displayed in the Data Quality Rule Summary page with the status as ʺSavedʺ.
You can view individual Data Quality Rule definition details at any given point.
To view the existing Data Quality Rule definition in the Data Quality Rule Summary page, follow these steps:
1. Select the check box adjacent to the required DQ Name.
2. Click View from the Data Quality Rules tool bar.
The DQ Definition page displays the details of the selected Data Quality definition. The Audit Trail section at the bottom of DQ Definition page displays metadata information about the Data Quality Rule defined.
You can update the existing Data Quality Rule definition details except for the Definition Name, Table, and Base Column selected.
To update the required Data Quality Rule definition details in the Data Quality Rule Summary screen:
1. Select the check box adjacent to the required DQ Name.
NOTE |
You can only edit those rules which have status either as Saved or as Rejected. |
2. Click Edit from the Data Quality Rules tool bar. The Edit button is disabled if you have selected multiple DQ Names. The DQ Definition page is displayed. Update the details as required. For more information, see Create Data Quality Rule section.
3. Click Save to update the changes.
You can copy the existing Data Quality Rule to quickly create a new DQ definition based on the existing rule details or by updating the required parameters.
To copy an existing Data Quality Rule definition in the Data Quality Rule Summary page, follow these steps:
1. Select the check box adjacent to the required DQ Name in the list whose details are to be duplicated.
2. Click Copy from the Data Quality Rules tool bar. Copy button is disabled if you have selected multiple check boxes. The DQ Definition page is displayed.
3. Edit the DQ definition Name and other details as required. For more information, see Create Data Quality Rule section.
4. Click Save. The defined Data Quality Rule definition is displayed in the Data Quality Rule Summary page with the status as ʺSavedʺ.
You (Authorizer) can Approve a pre-defined Data Quality Rule definition for further execution or Reject an inappropriate DQ definition listed within the Data Quality Rule Summary page. User needs to be mapped to DQ Authorizer function role to Approve or Reject a DQ definition.
To Approve/Reject Data Quality Rule in the Data Quality Rule Summary page, follow these steps:
1. Select the checkbox adjacent to the required DQ Name. Ensure that you select the ʺSavedʺ DQ definition based on the Status indicated in the Data Quality Rules grid.
2. Perform one of the following actions:
§ To Approve the DQ definition, click Approve. The User Comments page is displayed. Enter the notes or additional information to the user and click OK. The selected DQ definition is approved and a confirmation dialog is displayed.
§ To Reject the DQ definition, click Reject. The User Comments page is displayed. Enter the notes or additional information to the user and click OK.
The selected DQ definition is rejected and a confirmation dialog is displayed.
NOTE |
The authorizer can approve or reject only one definition at a time. |
The Approved/Rejected status of the DQ definition is indicated in the Status column of the Data Quality Rule Summary page. You can mouse‐over i button to view the Approver comments.
You can remove Data Quality Rule definition(s) which are created by you and which are no longer required in the system by deleting from Data Quality Rule Summary page.
1. Select the check box adjacent to the required DQ Name whose details are to be removed.
2. Click Delete from the Data Quality Rules tool bar.
3. Click OK to confirm deletion.
Data Quality Groups Summary within the Data Integrator framework of Infrastructure system facilitates you to logically group the define DQ definitions and schedule for execution. DQ definitions can be executed either through Data Quality Groups Summary screen of Data Integrator framework or in Batch Execution screen of Operations module.
When a Data Quality Group is executed for processing, the details of the execution are captured in a log file. You (Business Analysts) need to have ETL Analyst function role mapped to access the Data Quality Summary framework within the Infrastructure system. You can access Data Quality Groups Summary by expanding the Data Quality framework within the Unified Metadata Manager section in tree structure of LHS menu.
Figure 111: Data Quality Groups Summary page
The Data Quality Groups Summary screen displays the list of pre‐defined Data Quality Groups with the other details such as Group Name, Description, Creation Date, Created By, Last Modification Date, and Last Modified By. You can Create and Execute DQ
Group definitions and view, modify, copy, or delete DQ Group definitions within the Data Quality Groups Summary screen.
You can also make use of Search and Pagination options to search for a DQ Group definition based on Group Name, Description, or Rule Name and view the existing DQ Group definitions within the system.
Topics:
· View Data Quality Group Summary Log
· Data Quality Rules For Staging Tables
· Data Quality Groups for Staging Tables
You can create a DQ Group definition by defining the DQ Definition details and mapping the required DQ Rules which are authorized and approved within the system.
To create DQ Group in the Data Quality Groups Summary page, follow these steps:
1. Click Add in the Data Quality Groups tool bar. Add button is disabled if you have selected any check box in the grid. The Data Quality Group Definition page is displayed.
Figure 112: Data Quality Group Definition
2. In the Data Quality Group Definition section, perform the following actions:
§ Enter the Group Name by which you can identify the DQ Group.
§ Enter a Description or related information about the DQ Group.
§ Select the Folder (available for selected Information Domain) from the drop down list.
3. In the Map DQ Rules section, perform the following actions:
§ Select the required DQ Rule from the Available Rules list and click Select. You can also search to select a specific DQ Rule by entering the required keyword and clicking Find button.
§ To select all the listed DQ Rules, click Select All.
You can also deselect a DQ Rule by selecting from the Mapped Rules list and clicking Deselect or clicking Deselect All to deselect all the mapped rules. You can search to deselect a specific DQ Rule by entering the keyword and clicking button. You can search to deselect a specific DQ Rule by entering the keyword and clicking Find button.
4. Click Save. The defined DQ group is listed in the Data Quality Rule Summary page and can be executed for processing.
You can execute a defined DQ Group Definitions along with the mapped Rules and validation checks in the Data Quality Rule Summary page. You can also execute a DQ Group in the Batch Execution page of the Operations module.
To execute a DQ Group in the Data Quality Rule Summary page, follow these steps:
1. Select the checkbox adjacent to the required Group Name.
2. Click Execute from the Data Quality Groups tool bar. Execute button is disabled if you have selected multiple check boxes. The Group Execution page is displayed.
3. In the Batch Details section, perform the following actions:
§ Select the MIS Date using the Calendar. MIS Date refers to the date with which the data for the execution is filtered. In case MIS date is not present in the target table, execution happens ignoring the date parameter.
NOTE |
The DQ Batch ID is auto populated and is not editable. |
4. Specify the percentage of Threshold (%) limit in numeric value. This refers to the maximum percentage of records that can be rejected in a job. If the percentage of failed records exceeds the Rejection Threshold, the job will fail. If the field is left blank, the default the value is set to 100%.
5. Click Execute. A confirmation message is displayed and the DQ Group is scheduled for execution. After the DQ Group is executed, you can view the details of the execution along with the log information in the View Logs page. For more information, see View Data Quality Group Summary Log section.
You can view individual Data Quality Group definition details at any given point.
To view the existing DQ Group definition in the Data Quality Group Summary page, follow these steps:
1. Select the check box adjacent to the required Group Name. The mapped DQ Rules are displayed in the Data Quality Rules section.
2. Click View button from the Data Quality Groups tool bar. The Data Quality Group Definition page displays the DQ definition details.
You can update the existing DQ Group definition details except for the Group Name.
To updated the required DQ Group definition details in the Data Quality Groups Summary page, follow these steps:
1. Select the check box adjacent to the required Group Name.
2. Click Edit from the Data Quality Groups tool bar. The Data Quality Group Definition page is displayed.
3. Update the details and click Save to update the changes.
You can copy the existing DQ Group details to quickly create a new DQ definition based on the existing details or by updating the required parameters.
To copy an existing DQ Group definition in the Data Quality Groups Summary page, follow these steps:
1. Select the check box adjacent to the required Group Name in the list whose details are to be duplicated.
2. Click Copy from the Data Quality Groups tool bar. Copy button is disabled if you have selected multiple check boxes. The Data Quality Group Definition page is displayed.
3. Edit the DQ Group Name and other details as required.
4. Click Save. The new DQ Group definition is displayed in the Data Quality Groups Summary page.
You can view the execution log details of Data Quality Rules in the View Logs screen. The View Logs screen displays the details such as Check Name, Log Message, Message Date, Message Time, Total Rows, Rows Impacted, Assignment Type, Assignment Severity, and Severity Message of the executed Data Quality Rules.
To view the Data Quality Rule execution log details in the Data Quality Groups Summary page, follow these steps:
1. Select the check box adjacent to the Group Name in the Data Quality Groups grid. The Data Quality Rules associated with the selected Group are displayed in the Data Quality Rules grid.
2. Select the check box adjacent to the DQ Name in the Data Quality Rules grid.
3. Click View Logs. The View Logs page is displayed.
4. In the View Logs page, select the Information Date from the drop down list. Based on the selection, you can select the Group Run ID and Iteration ID from the corresponding drop-down list.
5. Click the below button from the Group Execution details tool bar.
The Data Quality Rule Logs grid displays the execution log details of the selected Data Quality Rule. You can also click Reset in the Group Execution details tool bar to reset the selection.
You can remove the DQ Group definition(s) which are created by you and which are no longer required in the system by deleting from Data Quality Groups Summary page.
To delete, follow these steps:
1. Select the check box adjacent to the required Group Name whose details are to be removed.
2. Click Delete from the Data Quality Groups tool bar.
3. Click OK in the information dialog to confirm deletion.
Data Quality (DQ) Rules are framed and created based on Staging Tables. Each rule is based on specified staging table column Specific Check or table Generic Check. The rules created for each of the tables are detailed in the DQ Check Rules sheet of the Changelog Summary document of this release.
In Specific Check, a particular column is checked based on rule's predefined checks, where as in Generic Check any columns are not specified. Generic Check is useful if you have a check which is not Specific or you use IF-ELSE conditions or CASE statements.
To execute any DQ rule, a Group must be created and the rules for execution has to be mapped with this group. For each staging table, a group has to be created and rules for these particular table have to be mapped under this Group.
For more information regarding Group Execution, refer to the section Execute Data Quality Group.
The DQ Group Mapping sheet of the Changelog Summary document of this release displays the total groups and corresponding Rules mapped to that Group.
You can execute the DQ Rules either from Data Quality Group Summary page or via a Batch execution.
To execute the Data Quality Groups from the Data Quality Groups Summary page, follow these steps:
1. Select the check box adjacent to the Group, you want to execute. The corresponding DQ Rules are displayed in Data Quality Rules grid.
Figure 113: Data Quality Groups Summary page
2. Click Execute button. The Group Execution page is displayed.
Figure 114: Group Execution page
3. Enter the MIS Date.
4. Click Execute button from the Group Execution page. The execution is triggered and the following message is displayed: Batch Triggered Sucessfully.
After the execution is complete, you can view the details from the Data Quality Rules Summary page.
To view the execution logs, follow these steps:
1. Select the check box adjacent to the rule, of which you want to see the execution log.
Figure 115: Data Quality Groups Summary page
2. Click View Logs button. The View Logs page is displayed.
Figure 116: View Logs page
3. Select the Information Date from the drop down list.
4. Select the Group Run ID from the drop down list.
5. Select the Iteration ID from the drop down list.
6. Click View Logs. The details of the selected Group Execution are displayed.
Figure 117: View Logs page with the details of the selected Group Execution
Create a Batch for executing DQ Rules and add a Task to the selected Batch. Add component as RUN DQ RULE and in Dynamic Parameter List, add a DQ Group.
A single Batch can have multiple number of Tasks and each Task is executing the DQ Group. Batch execution facilitates the execution of multiple Groups simultaneously.
You can also use the Include or Exclude functionality to determine which all groups have to be executed. The following batches need to be executed in OFSDF.
Table 62: OFSDF Batches that needs to be executed
V_BATCH_ID |
V_BATCH_DESCRIPTION |
<Infodom>_ALM |
Data Quality batch for ALM tables |
<Infodom>_CAMPAIGN |
Data Quality batch for CAMPAIGN tables |
<Infodom>_COLLATERAL |
Data Quality batch for Collateral tables |
<Infodom>_COLLEC‐ TION_AND_RECOVERY |
Data Quality batch for Collection and Recovery tables |
<Infodom>_CRM |
Data Quality batch for CRM tables |
<Infodom>_CUSTOMER |
Data Quality batch for Customer tables |
<Infodom>_EXPOSURE |
Data Quality batch for EXPOSURE tables |
<Infodom>_GL_AND_AC‐ COUNTING |
Data Quality batch for GL and Accounting group |
<Infodom>_LRM |
Data Quality batch for LRM tables |
<Infodom>_MAR‐ KET_RISK |
Data Quality batch for MARKET RISK tables |
<Infodom>_MASTER |
Data Quality batch for master tables |
<Infodom>_MISCELLA‐ NEOUS |
Data Quality batch for Miscellaneous tables |
<Infodom>_OP_RISK |
Data Quality batch for OP RISK tables |
<Infodom>_ORIGINATION |
Data Quality batch for Origination tables |
<Infodom>_PRO‐ DUCT_PROCESSORS |
Data Quality batch for Product Processors group |
<Infodom>_RATES |
Data Quality batch for Rates tables |
<Infodom>_RATING |
Data Quality batch for RATING tables |
<Infodom>_SECURITIZA‐ TION |
Data Quality batch for Securitization tables |
<Infodom>_TRANSAC‐ TIONS |
Data Quality batch for Transactions tables |
<Infodom>_TRANSAC‐ TION_SUMMARY |
Data Quality batch for Transaction Summary tables |
The DQ Batches and corresponding groups are detailed in the RUN_CHART_SUMMARY sheet of the Runchart document of this release.
For more information regarding Batch Execution, refer to Operations module of Oracle Financial Services Analytical Applications Infrastructure User Manual.