6 Nov 2013

6 Nov 2013

Consistency and accuracy challenge for multiple subsidiaries data consolidation

By:

Category: case study, New Feature, Uncategorized

0 Comment

Consistency and accuracy challenge for multiple subsidiaries data consolidation

Consolidation Challenge

In many organizations, the data warehouse is used to consolidate local subsidiaries data into one central location. The data warehouse can then be used by the top management to have a global overview and take decisions in the group level.
The amount of subsidiaries and source to consolidate can vary from a few subsidiaries up to hundreds of them. This makes the task of monitoring data accuracy and consistency a real challenge.

In many cases we experience the following problems when dealing with multiple subsidiaries loading:

  • Some of the subsidiaries did not provide data
  • There was a communication problem preventing to access to a specific subsidiary
  • Some of the subsidiaries do not respect data entry validation, which makes their data not reliable

The reality is that we cannot always assure that data will be completely loaded from all sources and will be flawless. This often causes confusion and reduces reliability on the decision making level. What we can assure is the awareness of decision makers of which subsidiaries data is reliable and which ones are not.

Often the subsidiaries are using the same source system, and therefore the structure of data is identical. The verification then, should be identical for each subsidiary against the data warehouse.

Quality-Gates solution

By using Quality Gates, subsidiaries validation is done automatically without manual interference, saving hours of tedious search and verification. Moreover, the maintenance is done only on one set of validation which will directly apply for all subsidiaries.

Single set of validation
This is how it is done:

  • Set of tests are created for a specific subsidiary data and the data warehouse is filtered to check only the relevant records.
  • Iteration over all the subsidiaries data sources is done:

    • Updating the data source connection string;
    • Filtering the relevant source value in the data warehouse;
    • Providing a source identification in the email alert.

Executing Quality Gates in iteration mode, allows the organization to save a lot of time executing the tests and to enhance  data reliability at the decision making level.