Tuesday, August 21, 2018

No Excuses for Failed Data Warehouse Projects

I really struggle to understand why so many Data Warehouse (DW) projects fail. In 2005, Gartner reported that 50% of DW projects fail or have limited success. From 1997 thru to now, 2018, I have had 100% success rate with numerous DWs and Business Intelligence solutions. It's not that hard.

I recently helped with performance issues on a DW that was designed 8 years ago by a reputable vendor and built/deployed more recently by a second vendor. I have always said there is scope to improve database performance and this system was no exception. In a few weeks we identified fixes for the slow ETL process and query fixes so that all reports rendered in a few seconds. However, I was shocked by the poor design/implementation. This customer had a detailed full design before outsourcing the development. Designing a big DW, like this, and implementing the design over many years is a recipe for failure. Design flaws, requirement ambiguities etc. only surface once the DW has been built and populated. It's very hard to redesign later. My rule of thumb is that if a DW takes 12 developers 3 years to develop, significant design changes will take a proportion of the 3 years for 12 developers to change. Whereas an agile development that takes information all the way through to end users in stages will take a fraction of the effort to fix a design/requirement ambiguity flaw.

The best insurance to avoid this sort of DW monster is to develop in short phases that take on the biggest risks and most important aspects first. For example, if there are large and volatile source tables that need to be close to real-time in the DW, some of these tables should be implemented first, to test and prove the efficiency and accuracy of the ETL. Once visualised by users and proved to scale, the design can be used as a template for the other large source tables. If there are performance/locking/interpretation issues, they can be corrected with relative ease. Only continue with DW development once the user representatives are happy with the reports/dashboards/analysis etc this far. It's unfair to ask users to sign off on hundreds of pages of "specifications" before a big waterfall implementation. Note, just because there are daily stand-ups does not mean the project is agile.

For a full list of my DW design principles see Guiding Principles of Data Warehousing.

No comments: