• Success
    Manage your Success Plans and Engagements, gain key insights into your implementation journey, and collaborate with your CSMs
    Success
    Accelerate your Purchase to Value engaging with Informatica Architects for Customer Success
  • Communities
    A collaborative platform to connect and grow with like-minded Informaticans across the globe
    Communities
    Connect and collaborate with Informatica experts and champions
    Have a question? Start a Discussion and get immediate answers you are looking for
    Customer-organized groups that meet online and in-person. Join today to network, share ideas, and get tips on how to get the most out of Informatica
  • Knowledge Center
    Troubleshooting documents, product guides, how to videos, best practices, and more
    Knowledge Center
    One-stop self-service portal for solutions, FAQs, Whitepapers, How Tos, Videos, and more
    Video channel for step-by-step instructions to use our products, best practices, troubleshooting tips, and much more
    Information library of the latest product documents
    Best practices and use cases from the Implementation team
  • Learn
    Rich resources to help you leverage full capabilities of our products
    Learn
    Role-based training programs for the best ROI
    Get certified on Informatica products. Free, Foundation, or Professional
    Free and unlimited modules based on your expertise level and journey
    Self-guided, intuitive experience platform for outcome-focused product capabilities and use cases
  • Resources
    Library of content to help you leverage the best of Informatica products
    Resources
    Most popular webinars on product architecture, best practices, and more
    Product Availability Matrix statements of Informatica products
    Monthly support newsletter
    Informatica Support Guide and Statements, Quick Start Guides, and Cloud Product Description Schedule
    End of Life statements of Informatica products
Last Updated Date May 25, 2021 |

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

Column Name

Data Type

Size

AUDIT_KEY

NUMBER

10

CONTROL_AREA

VARCHAR2

50

CONTROL_SUB_AREA

VARCHAR2

50

CONTROL_COUNT_1

NUMBER

10

CONTROL_COUNT_2

NUMBER

10

CONTROL_COUNT_3

NUMBER

10

CONTROL_COUNT_4

NUMBER

10

CONTROL_COUNT_5

NUMBER

10

CONTROL_SUM_1

NUMBER (p,s)

10,2

CONTROL_SUM_2

NUMBER (p,s)

10,2

CONTROL_SUM_3

NUMBER (p,s)

10,2

CONTROL_SUM_4

NUMBER (p,s)

10,2

CONTROL_SUM_5

NUMBER (p,s)

10,2

UPDATE_TIMESTAMP

TIMESTAMP

 

UPDATE_PROCESS

VARCHAR2

50

Control Column Definition by Control Area/Control Sub Area

Column Name

Data Type

Size

CONTROL_AREA

VARCHAR2

50

CONTROL_SUB_AREA

VARCHAR2

50

CONTROL_COUNT_1

VARCHAR2

50

CONTROL_COUNT_2

VARCHAR2

50

CONTROL_COUNT_3

VARCHAR2

50

CONTROL_COUNT_4

VARCHAR2

50

CONTROL_COUNT_5

VARCHAR2

50

CONTROL_SUM_1

VARCHAR2

50

CONTROL_SUM_2

VARCHAR2

50

CONTROL_SUM_3

VARCHAR2

50

CONTROL_SUM_4

VARCHAR2

50

CONTROL_SUM_5

VARCHAR2

50

UPDATE_TIMESTAMP

TIMESTAMP

 

UPDATE_PROCESS

VARCHAR2

50

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

Data Area 

Leg count 

TT count 

Diff 

Leg amt

TT amt

 

Customer

11000

10099

1

 

 

 

 

0

Orders

9827

9827

0

11230.21

11230.21

0

Deliveries

1298

1288

0

21294.22

21011.21

283.01

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.

 

 

Table of Contents

Success

Link Copied to Clipboard