Type II
This method tracks historical data by creating multiple records for a given natural key in the dimensional tables with separate surrogate keys and/or different version numbers. Unlimited history is preserved for each insert.
For example, if the supplier relocates to Illinois the version numbers will be incremented sequentially:
Supplier_Key | Supplier_Code | Supplier_Name | Supplier_State | Version. |
---|---|---|---|---|
123 | ABC | Acme Supply Co | CA | 0 |
124 | ABC | Acme Supply Co | IL | 1 |
Another method is to add 'effective date' columns.
Supplier_Key | Supplier_Code | Supplier_Name | Supplier_State | Start_Date | End_Date |
---|---|---|---|---|---|
123 | ABC | Acme Supply Co | CA | 01-Jan-2000 | 21-Dec-2004 |
124 | ABC | Acme Supply Co | IL | 22-Dec-2004 |
The null End_Date in row two indicates the current tuple version. In some cases, a standardized surrogate high date (e.g. 9999-12-31) may be used as an end date, so that the field can be included in an index, and so that null-value substitution is not required when querying.
Transactions that reference a particular surrogate key (Supplier_Key) are then permanently bound to the time slices defined by that row of the slowly changing dimension table. An aggregate table summarizing facts by state continues to reflect the historical state, i.e. the state the supplier was in at the time of the transaction; no update is needed.
If there are retrospective changes made to the contents of the dimension, or if new attributes are added to the dimension (for example a Sales_Rep column) which have different effective dates from those already defined, then this can result in the existing transactions needing to be updated to reflect the new situation. This can be an expensive database operation, so Type 2 SCDs are not a good choice if the dimensional model is subject to change.
Read more about this topic: Slowly Changing Dimension
Famous quotes containing the word type:
“We have two kinds of conference. One is that to which the office boy refers when he tells the applicant for a job that Mr. Blevitch is in conference. This means that Mr. Blevitch is in good health and reading the paper, but otherwise unoccupied. The other type of conference is bona fide in so far as it implies that three or four men are talking together in one room, and dont want to be disturbed.”
—Robert Benchley (18891945)