Databases Reference
In-Depth Information
around, the goal here is to create a complete system that can be turned over
to the operations group to run, and that is not as simple to accomplish. The
same characteristics of any other production system must apply to the data
warehouse too. The goal is to build a full system that runs without manual
intervention.
CHARACTERISTICS OF A PRODUCTION SYSTEM
A full production system must be able to do the following:
■
Gracefully handle exceptions that are identified in the incoming data.
■
Provide warning and error messages to flag conditions in
the data that require further attention. These may be con-
ditions that can be programmatically addressed in the sys-
tem or they may require human intervention to resolve.
■
Ensure restart and fallback capabilities in case the system is interrupted
due to processing errors or environmental issues (e.g., a computer goes
down).
■
Provide an audit trail to trace how data flows through the
system. This is needed to help track problems back through
the ETL system and/or the underlying source systems.
■
Include backup and recovery of the database itself.
Several major steps are involved in the development of a production ETL
system:
1.
ETL system requirements:
Up to this point, the requirements and design
components of the project have focused on what the end result must look
like. The data model reflects how the data is to be stored. Many detailed
requirements have already been collected during other project activities,
such as individual data element names and definitions. Data profiling
activities should yield insight into what the current data looks like, and
strong data governance may have already determined how each data
element is to be handled. Additional requirements for the ETL system
must also be defined. Examples of these requirements include processing
rules, guidelines for compliance with legal requirements, a processing
window, and what the audit trail must include.
2.
ETL system design:
The dimensional model is the target that the ETL
system will build. The ETL system design provides the details about
how to get from where the data is now to this target dimensional model.
Some organizations require that every little detail be defined, including
all of the specific rules for building the dimension, and that fact tables
