title: How do i add a column to a fact table 'after' it is already deployed and populated?
body: <p>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.</p>
  
  <p>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.</p>
  
  <p>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. </p>
  
  <p>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.</p>
  
  <p>I've not been able to find any information on a best practice for this type of change.</p>
  
  <p>Any help would be much appreciated.</p>
  
  <p>By the way. No Analysis Services used yet.</p>
  
  <p>Thanks,
  Martin</p>