Change detection and historization

The generated loaders compare each source row with the current version of its key and decide whether to insert a version, update one or do nothing. This article explains how the comparison works and how historization types, multi-version loads and Implementation Type change it. Read it before you choose Delta Detection, Historization Type or Excluded from Delta Detection.

How a row is compared

Store Entity, Entity and Mart Dimension use the model-object property Delta Detection:

• Hash Value Comparison (default): the loader compares BG_RowHashScd1 and BG_RowHashScd2 of the source row with the stored values.
• Column Comparison: the loader compares every SCD1 and SCD2 term column by column. Nullable columns are compared NULL-safe. The object has no row-hash columns.

Data Vault satellites always compare BG_RowHash. Excluded from Delta Detection removes selected terms from it. How biGENIUS-X builds hash values describes the hash input.

The two methods can disagree. A hash changes with every character, so a trailing space or a change in letter case is a change. A column comparison uses the comparison rules of the platform. On Microsoft SQL Server with a case-insensitive collation, values that differ only in letter case or in a trailing space compare as equal.

What each historization type does

Each term of a Store Entity, Entity or Mart Dimension has a Historization Type:

• SCD2: a changed value closes the current version and starts a new one.
• SCD1: a changed value overwrites the term in every version of the key. It starts no version.
• SCD0: the value of the first version is kept, also when it is NULL and later loads deliver a value.

One object can mix the three types. When SCD1 and SCD2 values change in the same load, the loader starts a new version and overwrites the SCD1 term in all versions.

Several versions in one load

With a multi-version load, a source delivers several versions of a key. The loader merges consecutive versions with equal values into one version. A delivered version that equals the stored current version starts no version.

Unknown-member rows

Deployment inserts one unknown-member row into Data Vault Hubs, Links and Satellites and into Mart Dimensions. A Mart Fact row without a matching dimension row references it. Custom Singleton Values sets its values.

Cached and Permanent

Implementation Type decides whether a comparison happens at all. Permanent compares with the stored rows and keeps the history. Cached empties the table and reloads it on every load, without a comparison. A Cached Mart Dimension therefore keeps only the history its sources deliver, and each load assigns new surrogate keys. Store Entity and Entity allow only Permanent.

Example

A Store Entity on Microsoft SQL Server has the SCD1 term CUSTOMER, the SCD0 term CUSTOMER_TYPE and the SCD2 term ADDRESS_LINE1. It is deployed twice, once with each Delta Detection value. Three loads deliver customer 1:

Load   CUSTOMER       CUSTOMER_TYPE  ADDRESS_LINE1
10:00  Contoso Ltd    Business       Main Street 1
11:00  Contoso Group  Enterprise     Main Street 3
12:00  Contoso Group  Enterprise     Main Street 3 (trailing space)

The result view with Hash Value Comparison shows three versions. The first version of a key starts at the Begin of Universe, here 1900-01-01:

CUSTOMER       TYPE      ADDRESS_LINE1     ValidFrom  ValidTo
Contoso Group  Business  'Main Street 1'   1900-01-01 11:00
Contoso Group  Business  'Main Street 3'   11:00      12:00
Contoso Group  Business  'Main Street 3 '  12:00      9999-12-31

With Column Comparison, the load at 12:00 changes nothing, and the second version stays current:

CUSTOMER       TYPE      ADDRESS_LINE1     ValidFrom  ValidTo
Contoso Group  Business  'Main Street 1'   1900-01-01 11:00
Contoso Group  Business  'Main Street 3'   11:00      9999-12-31

In both, CUSTOMER shows the new name in every version, and CUSTOMER_TYPE keeps Business.

Related articles

• Reference: Delta Detection (Model Object), Historization Type (Term and Relationship), Excluded from Delta Detection (Model Object), Implementation Type (Model Object), Custom Singleton Values (Model Object), BG_ValidToTimestamp
• See also: How biGENIUS-X builds hash values, Configure a multi-version load, Mart Dimension