Data Warehouse Renovations

I’ve run across a number of incomplete data warehouses. The kind where release 1 made it out the door, but wasn’t very successful. The fundamental problem that connected each of these failures is that the data warehouse wasn’t built as a data warehouse. What do I mean by that? The warehouse did not meet most of the accepted patterns that are required of a database for it to be designated a data warehouse. They were not dimensional models. Some were not even close.

In one case the warehouse was a copy of the database used for an OLTP application. The IT group did this to allow users to separate reporting from transactional usage, and then called this the data warehouse. In other cases an ETL process was performed on the OLTP database where a subset of data was moved to a different server. Lookup tables were employed to decode data, and the data tables were reconfigured, to some extent, to support reporting.

In each case, the approach taken was to support a certain number of reports with hard and fast definitions. The schema design was developed as a rigid structure, supporting the reports developed, but little else. In one case I saw, the warehouse could not deliver the reports by itself, so pre-report building stored procedures had to run to populate tables with the report data. This appears to defy the logic and purpose of data warehousing, but sometimes people just want to make things work.

A layer of abstraction would be useful to introduce, but that’s for a subsequent article. The point here is to highlight the difference in implementations in the real world. We often see this in IT. We have short timeframes for delivery, a lack of skills coverage, and limited bandwidth, making it very difficult to implement the right design all of the time, or even some of the time. Learning to work with and adapt to ‘inferior’ designs is a normal part of my job, and as a consultant, one I need to do well. In a project I’m currently working, my job is to turn one of these pseudo-data warehouses into a true data warehouse, and in a short amount of time. It’s a job that is part science and part art, and in a few months I hope to write about the techniques I used to make it a successful implementation.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: