A dimension of a star schema contains categorical information like one record per customer (‘Joe’) with all his address details. Now let us say that it is important for the business analyst to analyse sales over time in certain regions to various types of customers. A number of customers will have changed address over the year and could fall into entirely different regions over the range of the analysis time period. Should the analysis take this into account, and if so, how?
Well the slowly changing dimension (SCD) is the answer. When Joe’s address changes, a historical record is maintained of the original entry and a new record is created with the changed address. A date range of when the record was active is updated and therefore it is possible to look back in time over specific periods. Note that the analyst has the choice here. She can either treat the customer’s current record as the truth in the past, or look at the previous record for that time frame.
SQL Server 2005 understands changing dimensions and it is implemented in the SCD wizard of the SSIS data flow object. We have found that the wizard will produce a standard design which needs tailoring if you maintain extra key relations to the fact tables…but that is another blog.