Plan your Data Warehouse from Top Down

by Mark Kromer

Several years ago I started employing a systematic technique on business intelligence and data warehouse projects that we felt did a good job of capturing business requirements and feeding those into the entire solution from the data warehouse to the BI dashboards & reports.

Many times I come across data warehouses that will include dimensions and measures that are never used in any reports or are missing critical aspects of business required reports. In many cases, this is because the BI solution may be been designed from the bottom-up. The data modelers built a data warehouse based on transactional source system models or without a proper understanding of the most basic meaning and business value that a BI solution provides: a way for the business to get quick answers to business questions to make better business decisions.

The technique that I am referring to is called the Fact Qualifier Matrix (FQM). I’ll point you to a deeper discussion on the Fact Qualifier Matrix that I did for B-eye Blogs here. In the comments section you’ll see a link to a template Excel spreadsheet that I included that you are welcome to download and use in your projects. This is not anything that is specific to SQL Server, but I thought I would take a slight diversion by mentioning this tool.

Why I like to talk about FQMs to BI & DW practitioners is because it can be a struggle on BI projects to properly collect business requirements. And your business analysts and data modelers cannot just start by building models based on assumptions. Particularly with enterprise data warehouses, that is a perfect recipe for disaster and failed projects.

Take a look at the FQM and let me know if you find it helpful. The primary idea is to use the spreadsheet as a tool to capture from the business users, what questions that they need answered from your BI solution. You’ll list the questions in the sheet with referencing numbers and then line that up with the qualifiers such as time, location, product, etc. This matrix can then get directly translated into facts & dimensions in your data warehouse. When you build the corresponding dashboards, you’ll reference back to the matrix to ensure that the questions can indeed have answered directly in your reports.