A project manager asked me the following question – are there templates available to size the various phases of a DW/BI project? I tried to come up with one, but realized its futility. There are too many variables. I have listed the phases and some guidance to scoping each of them, but assigning even rough estimates to any of them is going to be too much of a SWAG even for someone as dangerously eager to incur the risk of random flak from strangers as myself. I did it anyway. I’ll regret this.
Her question pertained to an Agile methodology (repeated iterations) but I think that aspect is immaterial to this conversation.
The phases are –
Requirements Gathering, of course
Design – Data Model, ETL Design, Report Design, OLAP Design
Development – ETL, Reporting, OLAP
Release – UAT if needed
Typically, and I offer this with a BIG pinch of salt, requirements gathering should take the least amount of time, followed by design and testing. Development should be the most time consuming activity. Having worked on a variety of platforms and designs, I have found, generally speaking and in broad terms, that ETL development seems to be, most of the time, more effort intensive than Report development. However, again, it depends on many factors, such as how well the design aligns with the requirements. If the data warehouse design directly supported the requirements by having the appropriate metrics and dimensions in place, report development should be more straightforward, but, again, that is a very generalized statement.
Having made the appropriate CYAs, I hesitantly offer the following rampant generalizations for a breakdown.
Requirements – 10%
Design – 10%
ETL Development – 30%
Report Development – 20%
Testing – 20%
Release – 10%
In a 10 day Agile cycle, this translates to 1 day of requirements, 1 day of design, 3 days of ETL development, 2 days of report development, 2 days of testing overall, and 1 day for packaging and releasing the deliverable.
Other factors that influence the variability of estimates are the exact requirements, of course, the technology platform used (some make it harder to write reports or easier to write ETL), the experience level of resources, etc.
That is the best I could do. I think probably the best advice is to have someone in the room who has gone through the process a few times.