HomeArticlesSlideshowsAsk BobLogin or Register
NewsCustom SearchContactLinks
Ageing of data with band validity (SCD, History tables)
Data in slowly changing dimension and history tables are usually organized as set of records with band of validity (VALID_FROM, VALID_THRU). All new records are then inserted with default - maximal date VALID_THRU. The date will be modified when new different values come.

Fact tables based on time snapshot are usualy partitioned based on the snapshot time, so the basic prerequisity of ageing is fulfilled. Other for history tables. Basic problem is, that significant date for ageing of history tables is not a VALID_FROM but VALID_THRU date. And VALID_THRU date is changed during the lifetime of record.

Despite it sounds crazy the partitioning by range of VALID_THRU is reasonable solution. There are two ways how to manage changing of value used as a partitioning key:

  • Allow migration of records. All closed data will be migrated from one partition to another.
  • Ex-post partition split. Only read-write partition is the last one. Partition with that data will be split just after the period when data can be changed (eg. one month safety period). Ex-post partition split method is for 40 % more efficient than the record migration one.

See: Ageing of data - avoid flat consideration of data in time
Ludek Bob Jankovsky
All Right Reserved © 2007, Designed by Bob Jankovsky