Slowly Changing Dimension - Type III

Type III

This method tracks changes using separate columns and preserves limited history. The Type II preserves unlimited history as it's limited to the number of columns designated for storing historical data. The original table structure in Type I and Type II is the same but Type III adds additional columns. In the following example, an additional column has been added to the to record the supplier's original state - only the previous history is stored.

Supplier_Key Supplier_Code Supplier_Name Original_Supplier_State Effective_Date Current_Supplier_State
123 ABC Acme Supply Co CA 22-Dec-2004 IL

This record contains a column for the original state and current state—cannot track the changes if the supplier relocates a second time.

One variation of this is to create the field Previous_Supplier_State instead of Original_Supplier_State which would track only the most recent historical change.

Read more about this topic:  Slowly Changing Dimension

Famous quotes containing the words type and/or iii:

    Time has an undertaking establishment on every block and drives his coffin nails faster than the steam riveters rivet or the stenographers type or the tickers tick out fours and eights and dollar signs and ciphers.
    John Dos Passos (1896–1970)

    Napoleon wanted to turn Paris into Rome under the Caesars, only with louder music and more marble. And it was done. His architects gave him the Arc de Triomphe and the Madeleine. His nephew Napoleon III wanted to turn Paris into Rome with Versailles piled on top, and it was done. His architects gave him the Paris Opera, an addition to the Louvre, and miles of new boulevards.
    Tom Wolfe (b. 1931)