My next tip is to test your BI solution with the volume of data that you are expecting.  This is paramount to building a successful system.  You need to ensure that you can not only report on your data in a timely manner but also be able to load the volumes and complexity of data...Read More

In this article I’ll discuss making your data warehouse better.  I’ll admit that this is a pretty obvious aspiration, but data warehouses evolve.  If they are going to be a success and have some sort of longevity they have to.   The focus of this article is to ensure that when you make changes that they...Read More

In this article I’m going to show you how I build my time dimensions.  There are lots of different ways to create time dimensions, and there are lots of different requirements, from the attributes in the dimension to the granularity of the data.  You can use these scripts to build an entire time dimension, and...Read More

In this article I’ll be discussing the benefits of using a consistent unknown member key across your data warehouse.  And I’ll show you a couple of ways of going about implementing a consistent approach. Each of your dimensions should have an unknown member key.  The aim of which is to ensure all source data flows...Read More

My fifth data warehousing tip is to always use surrogate keys for your dimension and fact tables. Surrogate keys are keys that are generated internally by the data warehouse.  They have nothing to do with the business keys.  The business keys are the unique row identifiers from your source systems.  You definitely want these too,...Read More

My fourth data warehousing tip is to trust your instinct when it comes to building solutions, and if to you the solution seems too complex then maybe it is. Sometimes things are just complex.  It’s the way that it is.  However, sometimes things are more complex than they need to be. Now when it comes...Read More

My third data warehousing tip is to build thin slice.  Deliver subsets of valuable functionality. Get feedback and buy in, and use this to drive the next release. Thin slice development is the process of incremental delivery.  Where you build only the functionality necessary to deliver the one requirement that you’re currently focused on, but...Read More

I’ve seen data warehouses that nobody trusts, or are just too complex for end users to report on.  Users will voice their concerns (usually quite loudly).  Even worse, they will go back to using the relational source to get “accurate” data or because “it’s easier” to get data from there.  The data warehouse could become...Read More

My first data warehousing tip is to store fact data at the leaf level. If you aggregate your fact data as part of your ETL/ELT then you are painting yourself into a corner. Here’s why: Leaf level data with an associated business key allows you to map your fact data back to your source data,...Read More