Build Data Audit/Balancing Processes

Challenge

Data Migration and Data Integration projects are often challenged to verify that the data in an application is complete. More specifically, to identify that all the appropriate data was extracted from a source system and propagated to its final target. This best practice illustrates how to do this in an efficient and a repeatable fashion for increased productivity and reliability. This is particularly important in businesses that are either highly regulated internally and externally or that have to comply with a host of government compliance regulations such as Sarbanes-Oxley, BASEL II, HIPAA, Patriot Act, and many others.

Description

The common practice for audit and balancing solutions is to produce a set of common tables that can hold various control metrics regarding the data integration process. Ultimately, business intelligence reports provide insight at a glance to verify that the correct data has been pulled from the source and completely loaded to the target. Each control measure that is being tracked will require development of a corresponding PowerCenter process to load the metrics to the Audit/Balancing Detail table. 

To drive out this type of solution execute the following tasks:

1. Work with business users to identify what audit/balancing processes are needed. Some examples of this may be:
   1. Customers – (Number of Customers or Number of Customers by Country)
   2. Orders – (Qty of Units Sold or Net Sales Amount)
   3. Deliveries – (Number of shipments or Qty of units shipped of Value of all shipments)
   4. Accounts Receivable – (Number of Accounts Receivable Shipments or Total Accounts Receivable Outstanding)
2. Define for each process defined in #1 which columns should be used for tracking purposes for both the source and target system.
3. Develop a data integration process that will read from the source system and populate the detail audit/balancing table with the control totals.
4. Develop a data integration process that will read from the target system and populate the detail audit/balancing table with the control totals.
5. Develop a reporting mechanism that will query the audit/balancing table and identify the source and target entries match or if there is a discrepancy.

An example audit/balance table definition looks like this:

Audit/Balancing Details

Control Column Definition by Control Area/Control Sub Area

The following are two Straw-man Examples of an Audit/Balancing Report which is the end-result of this type of process:

In summary, there are two big challenges in building audit/balancing processes:

1. Identifying what the control totals should be
2. Building processes that will collect the correct information at the correct granularity

There are also a set of basic tasks that can be leveraged and shared across any audit/balancing needs. By building a common model for meeting audit/balancing needs, projects can lower the time needed to develop these solutions and still provide risk reductions by having this type of solution in place.