Slowly Changing Dimension - Type I

Type I

This methodology overwrites old with new data, and therefore does not track historical data. Its common uses are for misspelled names. (Assuming you won't need to know how it was misspelled in the past.)

Example of a supplier table:

Supplier_Key Supplier_Code Supplier_Name Supplier_State
123 ABC Acme Supply Co CA

In the above example, Supplier_Code is the natural key and Supplier_Key is a surrogate key. Technically, the surrogate key is not necessary, since the table will be unique by the natural key (Supplier_Code). However, to optimize performance on joins use integer rather than character.

If the supplier relocates the headquarters to Illinois. The updated table would overwrite this record:

Supplier_Key Supplier_Code Supplier_Name Supplier_State
123 ABC Acme Supply Co IL

The disadvantage of this is that there is no history in the data warehouse. Advantages of this type is it's easy to maintain.

If you have calculated an aggregate table summarizing facts by state, it will need to be recalculated when the Supplier_State is changed.

Read more about this topic:  Slowly Changing Dimension

Famous quotes containing the word type:

    “Chicago—is—oh well a façade of skyscrapers facing a lake, and behind the façade every type of dubiousness.”
    —E.M. (Edward Morgan)

    “They had supposed their formula was fixed.
    They had obeyed instructions to devise
    A type of cold, a type of hooded gaze.
    But when the Negroes came they were perplexed.
    These Negroes looked like men....”
    —Gwendolyn Brooks (b. 1917)