Monthly Archives: February, 2010

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.

Business Intelligence isn’t Funny

I can’t make business intelligence funny. For that matter, data architecture, data mining, and software development. I don’t know how to write about them and make the words catchy or clever. I tried, but I also couldn’t make them come off mysterious, scary, or thrilling. It stopped me from writing sooner, thinking it wouldn’t be interesting. I wanted my writing to have a voice.

I’m writing anyway. I’m no different than other people who do the work I do. To keep up with the ever changing fields of business intelligence, data architecture, software development, and technology in general, I read a number of blogs and articles. Some I read sparingly, but there are a few I keep up with regularly. I like SQL Server Central and enjoy reading what Steve Jones has to say every day, even when it’s not directly related to my work. Chris Adamson has very good, practical advice when it comes to data warehouse design. Glenn Berry is a great source for SQL Server performance and tuning. There many more that are worthy, but time and work get in the way, so I read them once a week, once a month, or just once.

So why write a blog? I run across problems, issues and solutions that I don’t see others writing about. It’s that simple. It makes me think I have something to contribute. As I’ve worked several challenging projects over the last few years, at least once a week I think to myself ‘That’s something I can write about that will help other data architects avoid the same problem!’ I know I’m not the only one who runs into these problems.

I’ve had numerous titles for blog posts running through my head for a long time. I’ll post on Estimating Data Warehouse Development, Authentic Consulting, Modeling Complex Measures, Data Architecture for Multiple Uses, Integrative Data Mining and more. I really want to write about several articles with a title like The Business Process Data Warehouse. It’s been where I’ve spent a lot of my time the last couple of years.

I might not make you laugh, but I hope I can make business a little more intelligent for you.