I would like to know how to make effort estimations for ETL, query and reporting, and data mining. Are there any standard templates for computing effort estimates? Please also suggest some references Web sites and publications.


Sid Adelman’s Answer: The following situation and the answers are taken from Impossible Data Warehouse Situations: Solutions from the Experts by Sid Adelman, Joyce Bischoff, Jill Dyche, Douglas Hackney, Sean Ivoghli, Chuck Kelley, David Marco, Larissa Moss and Clay Rehm:

Management Requires Substantiation of Estimates

Management does not understand why the data warehouse project will take so long. The executives are unimpressed with the project plan, which shows task durations and work efforts. Believing these estimates are inflated to protect the project manager and the team, the executives now demand substantiation of the estimates. If there are no standards the project manager can use to back up his estimates, he knows that management will impose standards. What should the project manager do?

Sid Adelman

Other organizations have been down this path before. The project manager should connect with people who have implemented analogous data warehouse projects, even if those projects are only somewhat similar to the one proposed. He should ask these folks for their project plans, especially if those plans have been updated to reflect the reality of how long the tasks actually took. The project manager should be sure to ask about the skill levels of those who performed the tasks. If his people are not as skilled, the data warehouse tasks will obviously take longer. Then the message to management is, "We aren’t smarter or better organized than those folks who actually implemented other data warehouses; therefore, based on their experiences, we have a pretty good idea of how long it will take." Although the most believable people to talk with are project managers from organizations that implemented data warehouses, consultants can also provide input; however, they don’t have the same level of credibility.

Joyce Bischoff

The project manager should ask an experienced data warehouse expert to review the project plan and its estimates. If the consultant agrees with the estimates, this would provide evidence regarding the accuracy of the project plan. If management insists on an earlier delivery date, it may be necessary to argue for a reduced project scope. The consultant could also assist with this.

Douglas Hackney

The project manager should leverage case studies and expert testimony of other data warehousing projects. There’s a plethora of information available about data warehousing projects on the Web and from data warehouse associations.

Sean Ivoghli

The project manager should research other similar projects and use those numbers to justify his. He could also hire a well-known consultant to review the project plan and bless it (assuming it is good).

Larissa Moss

Substantiating estimates is not difficult if the project manager has been diligent in tracking time on previous projects. Task estimates should be calculated based on a variety of variables, not on guessing and padding. Assuming the project manager has calculated and tracked actual time for each of these variables in the past, he should present the actuals as empirical evidence to management to back up his current estimates. The variables he should use in his calculations are listed below.

  • Average effort: Based on activities performed on previous projects, the project manager can calculate an average amount of time per task.
  • Skill level: The documentation for the project management methodology or tool used should state the recommended adjustment percentage for skill level. The employees’ past training record and performance evaluations should substantiate the skill level the project manager chose for them.
  • Additional (unscheduled) project- related activities: In addition to assigned activities, many interruptions occur during a work day. Some of these "interruptions" are project-related, such as brainstorming sessions, review meetings, and unanticipated research.
  • Company-related activities: Many other "interruptions" are not project-related but work-related, such as attending department meetings, troubleshooting another system, helping a coworker, and processing e- mails.
  • Personal activities: During the duration of a project, team members frequently need some personal time: they get sick, go on vacation, tend to an ill parent or child, have a dental problem, or need to stay home for repair of a maintenance problem (for example, a broken water pipe).

The project manager’s responsibility is to take all these variables into account when producing the final task estimates. It is also his responsibility to track actual time and compare those numbers against the estimates. If the project manager did not do his job right in the past, he may have nothing to back up his estimates except his gut feeling, which is (justifiably) being disputed.
Clay Rehm

In this case, an experienced consultant who is a good motivational speaker is needed to provide the required support. If the project manager has time, he could hire the consultant to do an independent evaluation to provide the substantiation. This consultant or a very small team of consultants must prepare an assessment within days. The project manager shouldn’t hire someone who wants to take over the project – he just wants someone to back him up, someone who has years of experience and can substantiate the estimates.

When considering a consultant, the project manager should locate someone who has designed, implemented, and sustained multiple successful data warehouses. The project manager needs a credible consultant to bring to bear best practices, industry standards, and experiences to show the typical time required to develop a data warehouse. The moral of the story: At times it is appropriate to call in the cavalry.

Clay Rehm’s Answer: The location of the data warehouse only matters if your users are experiencing performance problems. If you are experiencing performance problems, what kinds of problems are you facing? Is it with query response time or with data load time? Being on the same machine may improve the ETL process, but it most likely will slow down the query and usage. You may have to experiment with using the different approaches and go with what works best for your situation.

Register or login for access to this item and much more

All Information Management content is archived after seven days.

Community members receive:
  • All recent and archived articles
  • Conference offers and updates
  • A full menu of enewsletter options
  • Web seminars, white papers, ebooks

Don't have an account? Register for Free Unlimited Access