Calculate Effectivity End From Begin (Model Object)

Applies to
Platform: Microsoft SQL Server · Snowflake · Snowflake dbt · Databricks · Databricks dbt · Fabric Lakehouse · Fabric Warehouse · Oracle
Architecture: Data Vault

Calculate Effectivity End From Begin (CalculateEffectivityEndFromEffectivityBegin) derives BG_Effectivity_End from the next BG_Effectivity_Begin. The result view takes the next begin of the same key instead of the stored end.

Where to set it

Set the property when you edit a model object of one of these types:

Effectivity Satellite
Effectivity Link Satellite
Effectivity Business Satellite
Effectivity Business Link Satellite

Values and default

false (default): the result view shows the stored BG_Effectivity_End.
true: the result view calculates BG_Effectivity_End.

Effect on generated output

With true, BG_Effectivity_End of a row is the BG_Effectivity_Begin of the next row of the same Hub or Link key, ordered by BG_Effectivity_Begin. The last row gets the End of Universe. The table still stores the BG_Effectivity_End that the loader receives from the mapping.

Validation reports warning ID-20004 for a model object with true and a mapped BG_Effectivity_End, because the result view replaces the mapped value. The example below gets this warning; warnings do not block generation.

Example

The Effectivity Satellite load_loyaltycard_esat on Microsoft SQL Server maps BG_Effectivity_Begin and BG_Effectivity_End and has true. Its result view contains:

ISNULL(LEAD([BG_Effectivity_Begin]) OVER (PARTITION BY [Hub_HK]
    ORDER BY [BG_Effectivity_Begin]), N'99991231')
    AS [BG_Effectivity_End]

With false, the same line is [BG_Effectivity_End] AS [BG_Effectivity_End].

Reference: BG_Effectivity_Begin, BG_Effectivity_End, End of Universe (Project)
See also: Effectivity Satellite, Effectivity Link Satellite