Skip to Content

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

/wp-content/uploads/2013/04/1_205278.jpg

STEP 2:- To Create a Webi report on top of the Audit Universe

Queries at Report Level

/wp-content/uploads/2013/04/2_205279.jpg

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.

/wp-content/uploads/2013/04/3_205289.jpg

Report For the Current Run Profiling Results

/wp-content/uploads/2013/04/4_205290.jpg

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

/wp-content/uploads/2013/04/5_205291.jpg

Report For the Last Run Profiling Results

/wp-content/uploads/2013/04/6_205292.jpg

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

/wp-content/uploads/2013/04/7_205294.jpg

Report For the Comparison Profiling Results

/wp-content/uploads/2013/04/8_205295.jpg

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

/wp-content/uploads/2013/04/9_205296.jpg

Report For the Summary -Comparison Profiling Results

/wp-content/uploads/2013/04/10_205297.jpg

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.

To report this post you need to login first.

Be the first to leave a comment

You must be Logged on to comment or reply to a post.

Leave a Reply