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.