Managing an ETL Project
[The life cycle of an ETL project is no different from the life cycle of other projects. It begins with a vision, mission, objective and goal that translates into a scope of work, which must be executed to create the desired outcomes—in this instance a data warehouse. Managing an ETL project is no different from managing other projects. The level of detail and the skill of execution of the project will determine the success or failure of the project]
ETL Project management is a challenge. It is
the first step in a data warehouse initiative and perhaps
the most important. If ETL project fails the data warehouse
will not deliver the desired outcomes. Since these projects
are high risk, high cost projects, organizations are very
anxious about the success or failure of these projects. Yet,
statistical reports reveal that a large number of data
warehouse projects fail for want of effective management and
imperfect understanding of the needs of the organization.
So what are the factors that must be taken into
consideration while launching ourselves into the management
of a ETL project? The answer is very simple—adopt best
practices in management. However, the application of the
percept is not so simple. Best practices can at best provide
guidance for leveraging technology (such as ETL tools, data
integration software), mitigating risk, and optimizing
resources. It cannot replace the native common sense, the
experience and the leadership drive of a good ETL project
manager.
The ETL project manager is as constrained as the manager of
other projects. He needs to garner the necessary resources
and find the relevant answers to the universal questions of
who, when, why, where, what and how of the ETL project. Why
do we need a data warehouse? What is the data that will be
collected, collated, cleaned and used? What ETL tools are
available for the ETL process? Who will be the beneficiaries
of the ETL process and who will define the process needs of
Extract, Transform, Load? Where will the data be extracted?
Where will it be staged? Where will it be transformed and
loaded? How will the data be used? The replies will give him
a fairly good idea of the need for the ETL process and the
kinds of data that he will need to provide to the end user.
This will set in focus the short term and long term
objectives of the project. The project manager can never
lose sight of the objectives of the exercise and he must
ensure that the project team members also, keep the vision,
mission, goals and objectives of the project in mind at
operational levels and delivery levels. His task will be a
definition of a strategic plan to achieve these objectives.
The first phase of project management is the development and
deployment of the ETL project methodology. Experience will
help him identify potential conflicts, misunderstandings and
to provide for them in order to mitigate project risks and
control costs. This is also the phase in which the project
manager educates his team members and stakeholders on the
project management approach and ensures that complete and
effective communication is established between the different
individuals involved. The ETL project manager also uses this
time for defining the procedures for issue, risk and change
management, project management monitoring and communication;
project management quality assurance and meeting schedules.
He details the strategies for stakeholder acceptance,
support, training and marketing. He focuses on configuration
management, document management, backup and recovery,
creation of archives. He also defines the scope of the ETL
project, the performance parameters, and the measures of
success, ETL project life cycle methodology and the ETL
project plan. The ETL tools, data integration software and
the battery of hardware required for the ETL project are
identified and requisitioned.
The management of an ETL project begins with a Work
Breakdown Structure (WBS). The ETL project manager will have
to ensure that all the phases of the project are listed;
activities and tasks identified and allotted appropriate
slots in the time line. He will have to provide detailed
descriptions of each phase, activity and task required in
order to ensure that all the ETL tasks are carried out as
planned and within the time frame specified. He will also
have to identify the ETL project process dependencies and
list the critical project milestones and deliverables at
each stage.
The next step would involve the identification of the
required resources for the project. These could be generic
resources required for each task such as full time or part
time staff, contractors, equipment and material. Each
resource type will have to be quantified and allocated
against the activities and tasks and scheduled for
availability in accordance with the delivery dates and
timelines. The purpose of this exercise is to ensure that
each resource is used optimally. The required ETL tools and
data integration tools are acquired installed and tested for
high-availability.
Once the above steps are complete, the manager will proceed
to construct an ETL project schedule. This ETL project
schedule will list the phases, activities, task sequences
and tasks. All key internal and external dependencies will
be identified and defined. Time frames will be allocated for
each ETL process phase and additional contingencies required
to mitigate risks will be incorporated into the plan.
Finally resources will be assigned to the ETL task and the
work will begin keeping in mind the critical milestones of
the project. All assumptions and constraints will be
described and inbuilt into the project process definition
for further review and action.
The first proof of concept is created when the data
warehouse architecture and the data warehouse concept is set
up and a small prototype is created for user validation and
testing This validates the ETL process blue print and gets
the buy in of the users and stakeholders of the project. It
mitigates the risk of the project and enables incremental
delivery of the concept until complete production scale is
reached and the data is ready to be loaded into the data
warehouse.
However, the task of the ETL manager does not end here. He
needs to ascertain the refresh rate of the data warehouse
and the flow of data that will have to be extracted, staged,
cleaned, transformed and loaded into the warehouse years
after the data warehouse has been in operation. His role
never ceases to be valued and he never becomes redundant.
