Monthly Archives: June, 2010

8 Core Principles of Data Warehouse Design – #1 Autonomous Design

This is an easy one to state. Design the Data Warehouse based on the requirements of the business. It sounds so obvious. The requirements should be driven by the Reporting needs, Analytics, Dashboards, Scorecards, Ad-Hoc Analysis and Data Mining. Why state this obvious principle? In all failed DW efforts, this principle is ignored.

A better way to understand Autonomous Design is by stating what not to do. Don’t design the DW by using a relational database as the source data model. Don’t make the DW look like a denormalized relational OLTP database. Don’t allow the source data models to determine the DW data model.

This is what I mean. An OLTP application has a Customer, Product and Order table. The star schema has 3 Dimension tables, Customer_Dimension, Product_Dimension and Order_Dimension. These Dimension tables have the same data elements as their source tables, except that codes are decodes. A fact table is added called Order_Fact that has 3 foreign keys, one to each Dimension table. Not much was done to create a good data model for Business Intelligence functions. The data model should be conceived from the ground up and would support the required BI functions. This is autonomous design.

Data Warehouse Design Fundamentals

I was asked to review an initial design for a data warehouse a few weeks ago for a state government agency. It was in the early stages of design so it was nothing more than a data mart, but it was going to go through a formal review by a big name outside consulting company, so they wanted my feedback before they got hammered by this company.

It was in sad shape, to put it mildly. The design was very basic and not really ready for review. It had a single fact table, surrounded by 7 or 8 dimension tables. These tables were basically a copy of the same tables that existed in a source application, with the main change being that they appended the word ‘dimension’ or ‘fact’ at the end of each table. Even though they weren’t far along, it looked to me that they were definitely going in the wrong direction. And there’s no way they were ready for a review by the big name consulting company.

As flawed as this design was, it’s not unique. So many times when I review a design or an existing warehouse, it’s not even close to the true architecture of what one should be. I touched on this problem in an earlier post titled Data Warehouse Renovations. Over the next several days I will describe 8 core principles to follow when creating your data warehouse. Abiding by these principles will make your warehouse a powerful tool for measuring, analyzing and reporting on your company’s performance.