none
Updating old records with a value : SCD type

    Question

  • Hello Everyone,

    I need an expert advice on a tough discussion going on:

    I have a dim with 3 columns:

    key (SK)

    Code (Unique)

    Status

    I have SCD 2 implemented for status ex:

    Key  Code Status To Date From Date
    1 A 1 D1 D2
    2 A 2 D2 D3

    Now I want to add one more attribute as owner in the list which will be a simple update for all the current and historical data for A

    Ex:

    Key  Code Status To Date From Date Owner
    1 A 1 D1 D2 X
    2 A 2 D2 D3 X

    If Next time Owner changes to Y it will update all the current and previous records available in dim for A.

    Can anyone tell me which SCD type is this?

    Thanks in advance!!

    Friday, September 27, 2013 12:00 PM

All replies

  • Hi Sonia,

    SCD have three types

    • Type 1: New record replaces the older record on basis of a key column. Thus no trace of older records.
    • Type 2: A new record is added to the dimension, thus for one key now you have two records and two different information available.
    • Type 3: All the Original records are modified and now they looks similar <according to the modification> and now reflect the same.

    In your scenario, you are modifying all the older records and thus reflecting new information.

    So in my experience this scenario would lie under Type 3.


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • Proposed as answer by HimanshuSharma Friday, September 27, 2013 12:30 PM
    Friday, September 27, 2013 12:08 PM
  • Hey Himanshu,

    As per my understading in SCD 3 we maintain the history of an attribute in columns.

    Here in my case i am not worring about the history of owner.


    • Edited by Sonia Sharma Friday, September 27, 2013 12:15 PM typo
    Friday, September 27, 2013 12:14 PM
  • Sonia,

    In Type 3 we can not maintain all the historical information about a changed record. We only maintain some part of the information. You should only use T3 when the number of changes are finite and it is a must situation for you to track the changes.


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.


    Friday, September 27, 2013 12:18 PM
  • Sonia,

    I hope I was able to answer your issue.


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Friday, September 27, 2013 12:53 PM
  • Hey Himanshu,

    Sorry but I am not satisfied with the answere :(!!!!

    Can you please help me to get some good links that can point on this ???

    Apology in delay

    Thanks!!!!!

    Monday, September 30, 2013 5:12 AM
  • Its SCD Type 1:

    In SCD 1 it will update historical records with the current value

    Monday, September 30, 2013 6:37 AM
  • Please refer this link

    http://en.wikipedia.org/wiki/Slowly_changing_dimension

    in this please refer Type1

    Monday, September 30, 2013 6:40 AM
  • Sonia,

    Deekshith B, is right. But only when you don't want to preserve history. But as I can see in your example you want to maintain the check, whether the record was updated or not.

    That's TYPE III


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Monday, September 30, 2013 9:39 AM