Databases Reference
In-Depth Information
table. The basic testing may also ensure that each customer is assigned a credit
profile and that each product has a package type.
Testing that the programs and processes run correctly is only part of the
overall testing and quality checks that must be done. Additional work is
needed to ensure that the data itself is accurate. The ETL system may have
correctly processed those 100,000 transactions, but if all of the sales were off by
a decimal point, then the tests previously mentioned would not detect it. The
business must be an integral part of the testing and quality assurance process
to ensure that the database is correct.
A great deal of work is required to validate the database when it is first
loaded. There will also be ongoing work to ensure that each time the ETL
system runs, accurate data is loaded. Who decides if the data is right? The
comprehensive test plan developed earlier by the business provides the basis to
assess the data. Representatives from the business community may be involved
in running the test cases, but their involvement is critical in evaluating the
results. This needs to be someone who has the knowledge to recognize that an
increase in market share of 14% is not possible, as changes in market share are
usually measured in tenths of a percent.
TIP
The ETL system should include many validation steps to check for data
problems before the data is loaded. Leverage your data access or business
intelligence tool to help with data validation. These tools are designed to identify
exceptions, compare results with previous periods, and compare results to
thresholds. Develop a set of queries or reports that can be run after the data has
been loaded as a final data quality check.
Why Does It Take So Long and Cost So Much?
If all of the systems in the organization contained clean, reliable data with
consistent production identifiers, then developing a robust ETL system would
not take so long. However, it is rare to find an organization that has such clean,
integrated systems already in place. Therefore, with the reality that the source
systems were designed without the requirement to integrate easily with one
another, the data warehouse must overcome these problems.
A variety of factors contribute to the time it takes to build the ETL system.
Several of the biggest contributors include the following:
Long decision cycle:
Many choices need to be made throughout the
design and development of the ETL system. Often, decisions can be
made based upon the requirements, or a single person or area needs
to be consulted first. However, in many instances the decision impacts
multiple areas. It can take a long time to get the appropriate people
