Audit Layer :- Helps to create a bridge for seamless integration and interaction within different teams in a project (Profiling and cleansing) to keep track of the Audit activity for Data Quality in a project.
Business Requirement
To create a frame work for capturing the data profiling and the data cleansing activity over the period of time and thereby helping the different teams to integrate and work as a team.
AIM
The purpose is to keep track of the cleansing activity undertaken on the faulty data been captured through the profiling team. This can be achieved by creating the Audit report for the Cleansing activity and the profiling activity in line with the business requirement.
PROCESS
STEP 1:- To Create a Universe on top of the Audit Table
Universe For Audit Table
STEP 2:- To Create a Webi report on top of the Audit Universe
Queries at Report Level
The report will have 4 customised query been designed
1) The Query for the Current Run
2) The Query for the Last Run
3) The Query for the Comparison
4) The Query for the Summary result of the Comparison.
STEP 3:- To Create a Query/Webi report For Current Run
Query For the Current Run Profiling Results
The Current run query will capture the customer records having in valid Post codes .This can be achieved by taking into consideration the Latest Flag value field maintained at the Audit Table (Failed Record Table) with status equal to C
Status C will hold all the failed records for the current run of Customer Table.
Report For the Current Run Profiling Results
STEP 4:- To Create a Query/Webi report For Last Run
The Last run query will capture the customer records having in valid Post codes .This can be achieved by taking into consideration the Latest Flag value field maintained at the Audit Table (Failed Record Table) with status equal to H along with the Effective End Date Field.
Status H will hold all the failed records for all the Historical run of Customer Table.
Effective End Date will capture the end date of the profiling session for which the rule was run and failed records were captured against the same.
We would be required to set the Effective End Date as the Max (Effective End Date) along with the status flag =H to get the failed records corresponding to the last run of the rule and data in the Audit Tables.
Query For the Last Run Profiling Results
Report For the Last Run Profiling Results
STEP 5 :- To Create a Query/Webi report For Comparison Report
The comparison report will capture the results for the value comparison between the Last and the current run based on below business factors (Note :- We will maintain the Business factors in an additional field Status to have below logic incorporated)
A) Value N: - The Business Logic for Status N will have the all the records that are present in the current run but not there in the last run data set. (Current Run – Last Run)
B) Value NA:-The Business Logic for the Status NA will have all the records that are present in the Last Run but not there in the current run data set. (Last Run – Current Run )
C) Value Y: - The Business Logic for the Status Y will have all the records that are present in both the runs current as well as last run. (Last Run Intersect Current Run).
Final set will contain the Union set of all the above results incorporated under one umbrella.
Query For the Comparison Profiling Results
Report For the Comparison Profiling Results
STEP 6:- To Create a Query/Webi report For Summary -Comparison Report
The Summary comparison report will capture the total counts for all the individual set of Business Factors (Status Codes)
N – New Customers
NA – Fixed Customers
Y-Failed Customers
Query For the Summary- Comparison Profiling Results
Report For the Summary -Comparison Profiling Results
Result:-
The creation and distribution of the above Audit reports to the Governance Team which will give the high level summary of profiled/cleansed record matrix will effective help the governance team to track the activity rate of cleansing and profiling by comparing the Profile run and cleansing run on weekly basis for the source system data which is likely to be refreshed on weekly basis.
- SAP Managed Tags:
