Monday, June 30, 2014

Signs your Data Warehouse is Heading for the Boneyard

I have come across too many data warehouse projects that have obvious signs of failing with project sponsors in denial.  Here are a few signs that you might look out for and take seriously.

  1. You've spent one million dollars and haven't got one million dollars of value. Data warehouse (DW) projects are never really finished, as there are continual changes in business practices, business software, competition, business strategy, regulation, etc. DWs need to be flexible so that they can continually be updated/enhanced to adapt to these changes in the business. If you've spent one million dollars and not got value from that, you are probably developing something that is inflexible. Typically, I would be encouraging, even very large companies not to spend that much money without showing benefit. The vision for a DW might be very large, but start with the trickiest, largest, and/or most crucial data. The DW should manage that data and provide value to the business before more work is commissioned.
  2. Your project is 3 months overdue and the development manager is telling you that more resources are required. There are probably fundamental design issues that will continue to cause issues. Such a long development cycle will probably lead to an inflexible DW.
  3. Your user community do not trust the output. The DW is producing reports, dashboards, alerts, etc., but the user community do not trust the data and aren't using it to run the business.
  4. The same question can be asked to different parts of the warehouse with different answers. One transaction detail should, ideally, be stored in only one place in the DW. If it is stored in mulitple places, the design is probably not optimal, and if they are providing different answers, there is definitely a problem.
  5. It's almost impossible to reconcile the data warehouse with your OLTP systems. The ETL (extract, transform and load) processes are so complex and involve so much custom code that no one can reconcile DW facts with OLTP systems. The ETL processes are probably too complex.
  6. Your developers are telling you that they are spending a significant amount of time developing functionality that will make the data warehouse more flexible. This is all too common in my view. Developers like to develop, and they can create large generic applications to schedule jobs, "clean data", manage file transfers etc. Most generic DW software is already available in Microsoft's SQL Server and related technologies. The role of the developers is to use this software to create a bespoke application that is as simple as possible. Try and stick to existing vendor software. They will maintain it and provide future releases. You will always have the option to move platforms in the future having spent much less money.
  7. The data warehouse requires a full-time person to manage the jobs and exceptions. DW schedules should be reliable enough to manage themselves, simply providing alerts for late data etc. Intervention should only be required where there is a defect in the DW and a diagnosis and change is required. Even when there is a catastrophic failure, the DW should start up and simply continue from where it was.
  8. Some of the development team have left before value is delivered. DW projects should be delivered in small releases that provide value. If the release cycle is so long that significant team members are leaving, there is probably an underlying design issue.
  9. There are performance issues that are so severe that it is looking difficult to process one day's data in a day. This is a bright red flag.  DW will only want to grow with more feed systems and more data. Such a large performance issue should be seen as a huge question mark over the DW.
  10. The development team are telling you that they need to spend a lot of time up front to develop software that's going to make the DW more flexible! It's almost a certainty that their software will make the DW more inflexible. Think of it this way, if it takes 50 dev days to create a DW, it will take a proportion of that 50 dev days to make a change.  If the first deliverable takes 1000 dev days, enhancements will tend to be a proportion of 1000 days.


Paul te Braak said...

Just to be a bit cheeky – how about the fact that you need to spend millions of dollars to get any value?

Richard Lees said...

Ha - that is a bit cheeky, but you make a valid point. Often, enormous value can be gained with a lot less money. My point was, rather, that if you have spent $1M, then you should obviously have $1M+ value. I have seen many DW that have spent this sort of money and not seen value yet.