Skip to content
  • There are no suggestions because the search field is empty.

Effective Timestamp mapped (Business Rule)

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

This business rule warns when a model object has a relationship to a historized model object but does not map the Default Term BG_EffectiveTimestamp. It raises a warning, and generation continues. The rule ID is ID-10028.

Message

The client shows a warning with this text on the model object. <model object name> and <type> stand for its name and type.

Model object '<model object name>' of type '<type>' has a relationship
to a historized model object, but the effective timestamp default term
(BG_EffectiveTimestamp) not mapped. Relations will be resolved to the
latest version in related model object.

On the model object card, point to the warning icon to see this message with the rule ID: Warning ID-10028. The Generate Artifacts page lists the message without the ID.

Warning popup of a Fact card with Warning ID-10028 and the message details

Cause

The model object has a relationship to a model object with SCD2 terms, and the term mapping of BG_EffectiveTimestamp has neither an expression nor a default value. The rule checks Entity, Fact and Mart Fact.

Fix

The model object is generated as configured. As the message states, the relationship then resolves to the latest version of the related model object. Decide which case applies:

  • The relationship must find the version that was valid at a point in time: map BG_EffectiveTimestamp in Manage Term Mappings, see BG_EffectiveTimestamp. The warning disappears.
  • The latest version is what you need: keep the term unmapped. The warning stays.

Check the result

After the mapping, the warning icon is gone from the model object.

Example

In a Dimensional project on Microsoft SQL Server, the Entity scd_mixed_with_scd_1_relationship_to_scd_2_without_effective_timestamp has a relationship to the Entity scd_2, which has SCD2 terms. Its BG_EffectiveTimestamp is not mapped. The client shows:

Model object
'scd_mixed_with_scd_1_relationship_to_scd_2_without_effective_timestamp'
of type 'Entity' has a relationship to a historized model object, but
the effective timestamp default term (BG_EffectiveTimestamp) not mapped.
Relations will be resolved to the latest version in related model
object.

Related articles