SCD component in SQL2005

SCD component in SQL2005

• MarkusEilers

  Hanna
  Which step you think SCD wizard was not fully functioning
  I see no problem for your first&second load; For third load, I am not sure what you did - if you give SCD a  incoming row with type2 change, you should see the existing matching record in dimension table expired and a new record with a new start date generated there. 
  You can find BOL with more details on this - 
  ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/extran9/html/f8849151-c171-4725-bd25-f2c33a40f4fe.htm.
  
  Thanks
  Wenyang

  
  
  

• Loane Sharp

  Hello Wenyang,
  
  
  Yes I am using the Wizard.
  
  In my table in Stage I have 6 colums, the table in DW is an exact replica. I have Date fields and I have text fields.
  In the wizard i connect to the DW point out the table, specify my Bs Key. The fixed attribute and the Changing attribute is fully functioning, I can specify and all. If i choose a historical attribute i get stuck on the Historical attribute options:
  
  If I choose use single column....there are no columns to choose from nor can I write anything into it. I do not want to write into it- I want to select from a list.
  
  If I choose Start and End dates.... There are no columns to coose from there either.
  
  and so the next button is inactive and I get an alert saying:
  You must select a value from the "Column to indicate current record" list.  -But I cant!
  
  Could it perhaps  have something to do with me using a client version
  
  Sincerely,
  
  

• Thomas Lau

  Goodmorning Wenyang,
  
  It behaves as you say but also sets the end date on the new record...
  
  If I choose to have dates (start and end date) to identify wether or not the record is valid the behviour is as follows:
  
  SCD identifies that there is an existing record updates the existing record with a date to mark it as expired (end date is updated from being NULL to todays date). Then the SCD will add a new record (the same Bs. Key as previous) with the new updated data, it will then set the start date to today and also set the end date to today! The new record with the same Bs Key should have NULL in end date but instead it has the same date as start date!
  This means that the SCD will interpret the newly created record as a new one as far as I can see...
  
  I think this is a strange behaviour as I expected, as well as my collegues, that the end date would be NULL until the record is once again is updated. What do you think
  
  
  Sincerely,
  
  
  
  

  
  
  

• PerryTsai

  Hanna, 
  The scenario works for me and it should also work for you on your RTM build(1399). I am not sure at this point why you saw date got set at the enddate column, when inserting a new record for your historical change. If you still get this, please contact me with your repro data so I can find out why. 
  Thanks
  wenyang
  
  

• Scott Burkland

  Good evening Wenyang,
  
  I am not sure which information to supply so here goes:
  
  VisualStudio 2005: 8.0.50727.42 (RTM.050727-4200)
  
  Microsoft .NET Framework: 2.0.50727
  
  SQL Server Integration Services: 9.00.1399.00
  
  Using XP Professional 2002 sp2
  
  
  Sincerely,
  
  
  
  
  

• baka_deshi

  Hanna, thanks for the additional info.
  
  The "single column to indicate current record" cannot be 1)the business key columns 2)the Fixed/Changing/Historical dimension columns you set in your previous step. So, is it possible you already used out all your 6 columns in your previous steps  Also, pls be aware the "start and end dates to identify current and expired records" list will only show date type columns.
  
  The client version should not matter.
  
  Pls let me know if this still does not help you to figure out your problem.
  Thanks
  Wenyang
  
  

• CzarOfKatmandu

  Morning Hanna,
  If start/end dates were chosen to identify current and expired records, then whenever a record is expired, we mark it by putting today's date at its end date column.
  So, if the incoming record has a Type2 change, the existing matching record at the dimension table will be expired(end date set to today), and a new record with today's date as its start date will be generated. You are right on the new record's end date should be null - it works for me on a Yukon RTM build, what build you are on I did not remember we had this issue before but I could be wrong.
  
  Thanks
  Wenyang
  
  
  

• JP 360

  Thank you soo much,
  
  sometimes the obvious is to simple to realize. It is now working. How ever testing it I see that if I choose to use start and end dates the SCD will do the following:
  
  First time load: Add the column set the start date, end date is NULL.
  
  Second load: type 1 change (over write): Overwrites the column, same date in start date, end date is NULL.
  
  Third load: type 2 change (new line): Sets end date in old column. Writes new line with start date AND end date -> Expired.
   if i run the packet a fouth time it will then create an identical line as the third load (start date is different) and end date is NULL...
  
  Perhaps this is not fully functioning...
  
  When I use a single column to set Current and Expired the SCD is working fine
  
  
  Once again thank you for all your help!
  
  Sincerely,
  
  
  

• walker4bc

  Hanna
  I need more scenario details to better assist you.
  Were you using the wizard
  If yes, it should be functioning well since our beta1 release(for general purposes)
     -On which page you couldn't choose a field because it was empty
     -Why/where you wanted to write in the information
  
  Thanks
  Wenyang
  
  
• SCD component in SQL2005