How do i add a column to a fact table 'after' it is already deployed and populated?

Question

I have a SQL Server 2005 data-mart star schema with the usual fact and dimensions tables. This is deployed and being populated via an SSIS based ETL package. All fine so far.

I have just been approached by the customer with a new requirement. Never, I hear you say! This requirement will mean I need to add a new dimension table to the data-mart to measure a new aspect of the incoming facts which happen to be financial.

To be able to 'slice' the facts by this new dimension I need to add a new foreign key column in the fact table linking to the new dimension.

I am unclear on the best way to do this. What should I do with the data that has already been captured? Just make the new column null-able and accept that old fact will have a NULL? Actually, as I am typing, its dawned on me that I could update old facts as well. Or maybe I should create a separate (child?) fact table which would just contain a link to each new (parent) fact and a link to the new dimension.

I've not been able to find any information on a best practice for this type of change.

Any help would be much appreciated.

By the way. No Analysis Services used yet.

Thanks, Martin

Answer 1

1. Add the new dimension table.

2. Populate it.

3. Add the nullable reference on your existing facts.

   Not all facts may be joinable to the new dimension. This is common when you have new information. If you have all of your original files, you may have all the information required to update all facts.

   If all facts cannot be mapped to the new dimension, add a "N/A" row to the dimension. Sometimes there's a good reason to have several N/A rows depending on what you know about your facts.

4. Update your facts so they all reference the new dimension -- either the proper dimension value or the special N/A row.

Ideally, you'll modify your fact table to make the column not-nullable. Sometimes this takes a painfully long time, and it's easier to export the data, redefine the table, and reload the data.

Answer 2

Well S.Lot answered most of it, I would just add that what to do with the old facts is a business decision. Make sure you ask them and get it in writing.