Make your data warehouse built to last


John Evans

When you hear something is built to last, what do you think? You might think of it as sturdy, solid and holds up to wear and tear. Those are great attributes for a building or a truck. But are those the attributes you want to think about when you want a data warehouse that is built to last?

My colleague Stephen Pace draws a great analogy which I will borrow and extend. Think about how a house is built. You want it to meet your needs, and you want it to be sturdy and solid to hold up against the weather and wear and tear from use. So a small army of construction workers set up forms to make the foundation, pour the concrete, build the walls, the roof, and finish the inside with quality materials so you are ready to live there. All this takes months of significant manual labor and expense. Once it is complete, you move in with all your belongings.

Traditional data warehouses are developed in the same way – built on a foundation of a data model and ETL programming. Think of this as the concrete foundation of your warehouse. You set up a data model (the forms laid out in the shape of the house) which is laid out to support the business requirements (the rooms and fixtures) at the time of construction. Then you build the ETL jobs and other programming (the frame and walls and roof) above the foundation and complete the house.  When it is done you load your data (you and all your stuff) and begin using (living in) it.

But then what happens? You acquire more stuff; you have a family; maybe your mother-in-law moves in (you add more data and more sources!). Suddenly your house (data warehouse) can’t accommodate your needs because your needs (business requirements) have changed. So you either have to build another one, or remodel the one you are in. If you build a new one you are basically going through the same cycle of pouring a concrete foundation and building a structure that hopefully is built to last for your situation.

But let’s assume you want to remodel your house. What do you do? Lots of destruction ensues. Workers come in and break walls; they jackhammer at the foundation and pour a new one to support the expansion. Sometimes you must move out while work is completed. All this takes time, money and can be incredibly inconvenient to your life while you wait for it to complete. And unfortunately, it can’t be automated.

Handling change in a traditionally developed data warehouse entails many of the same tasks. Depending on the nature of the change, you may have to change your data model foundation, ETL programs, data sources, load routines, your BI metadata and so on. This can require touching a lot of moving parts and manually editing a lot of existing ETL and SQL code. If the business users who requested the change are getting impatient from waiting months, you might even create a shadow system or Excel files to get them some data very quickly so they leave you alone to finish re-architecting the data warehouse.

Using the traditional method of building a data warehouse in the same way you might build a house will significantly reduce your ability to be agile. In my last post, I talked about how agility equals speed. As Stephen pointed out, it is hard to be agile if you must constantly jackhammer the old concrete structures and build new ones each time there is a business change that impacts the structure of the warehouse. And just as adding random quick-fix additions and repairs might weaken the structural integrity and overall functionality of your house, all these intermediate changes can make your data warehouse lumbering and unresponsive. Eventually, you may find that you need to bulldoze the entire structure and start again from scratch because too much has changed.

Building your data warehouse to last means architecting it from the start to handle change. Major changes to your house happen at most once every few years, but to stay competitive, businesses need to adapt and respond to market demands in days or weeks. Change happens all the time in business. A business-model-driven data warehouse environment is designed to make handling change easy and routine. Automating as many steps as possible goes a long way in speeding implementation.

For your data warehouse to last and be relevant, you need to enable rapid delivery cycles – through an agile methodology – and empower decision-making faster than ever. With a data warehouse that’s built to last, you’ll put your organization in a better position to move more quickly than your competitors, and IT will be delivering better service to internal customers.

Tags: , , ,

Trackbacks/Pingbacks

  1. Model-driven agility | Kalido Conversations - September 24, 2010

    [...] can still be made easy to understand and navigate. Even better, when you need to make a change, no jackhammers are [...]

  2. Kill the mouse, Daddy! | Kalido Conversations - April 11, 2011

    [...] For another blog that talks about data and foundations, check out John Evans’ “Make Your Data Warehouse Built to Last.” [...]

Leave a Reply