Slowly changing dimension

The term Slowly Changing Dimensions ( German: slowly changing dimensions) methods are combined in order to detect changes in dimension tables and, where appropriate, to document historically in data warehousing. Essentially, there are three methods to Kimball ( Ref: Kimball, 2002) are classified into types. Common to all is that existing records are connected via the primary key with new records to save changes to the table. Technical keys are currently not part of the article.

Type 0

The type 0 is a passive method. On a historicization in the proper sense is omitted. About the technical primary key ( PK for Primary Key ) is looked up in the dimension table if the PK of the new record already exists. If this is the case, no action is taken - the first known values ​​are maintained. Otherwise a new record is added.

Type 1

Type 1 is the trivial method. On a historicization is omitted. About the technical primary key ( PK for Primary Key ) is looked up in the dimension table if the PK of the new record already exists. If this is the case, the appropriate record is overwritten with the new data. Otherwise a new record is added.

Type 2

Type 2 is a complex process dimension tables or individual attributes of the table to historicize, to at any time to determine the then-current characteristics of the table. This is achieved by providing a validity interval is stored for each data record. To ensure the uniqueness of the PK, this is to enhance at least one of the interval attributes. In general, a bottom closed interval is used by the currently valid set is marked as infinite valid. It is based on the comparison of the existing records with the new data sets from a complete and periodic extraction on the technical primary keys without the / the validity of attribute / e Here, three cases can be distinguished:

  • The new record does not exist in the dimension.
  • The known dimension in the data set is not present in the current extraction.
  • The new data set may be associated with a data record from the dimension table of the PC.
  • There are no changes found: The procedure is complete. The next record is processed.
  • It will be noted Changes: The currently valid dimension data set is set to be valid until yesterday. The new record is inserted with valid from now infinitely valid.

Type 3

In type 3, the table is widened. That is, a new attribute is added to it. This method can be used for example in conversions of sales regions and product groups. The use of this method is only recommended for very specific changes.

Examples

For a better understanding is to illustrate the different procedures in the following example. Given a dimension table product. The attributes GueltVon and GueltBis are only relevant for type 2 and at least one also part of the PK.

From the operating system is extracted on 10 August 2005, the following record:

Type 0 the new record is ignored, since the primary key already exists in the target table:

By type 1, the second record is overwritten:

By Type 2, the second record is historicized:

By Type 3, a new attribute neuePGruppe is still:

734500
de