Skip to Content

Modelling slowly changing dimension with Temporal Join in Left Outer Join scenario

Background:

Temporal Join feature was introduced in HANA 1.0 SP05 (revision 45) to model slowly changing (time dependent) attributes in the context of an Analytic View.

  • The Fact table (data foundation) must have a timestamp/date value field (e.g. transaction date)
  • The Attribute View must have valid_from and valid_to columns to determine the validity of an attribute for a certain time interval. These two fields must be of type timestamp/date.
  • The join types supported are referential and cardinality is n:n

Below is the documentation from the HANA developer’s guide.

Problem Statement:

We have business scenarios, where we have transaction data coming to the system, for which master data is not available (has not passed through the global master data). This scenario is common in retail business

So report against all transactions, we need a left outer join between the fact table (transaction) and the attribute views (master data)? We also need to model slowly changing dimension.

Let me explain with an example.

However, since only referential join type is supported in Temporal Join, the row highlighted in yellow will be dropped from the resultset.

Solution Options:

We have the following three solution options to meet the business requirement.

Option A: Enhancing/Fixing the master data:

The issue at hand is because transaction data is available for missing master data records. In a standard star-schema (like BW), master data must be available for all transaction records.

So in the above example, we’ll set-up at ETL process (SQL Script or BODS) which will compare the Fact table and the master data and insert the missing master data into the master data table

Advantage:

  • Fixing master data may resolve other data-quality issue.
  • If left-outer join is possible for referential join (in future), these ETL-jobs can be stopped in future (if required), with minor changes to the models.

Disadvantage

  • Additional effort to create ETL-jobs to fix/enhance master data.
  • Time window required to execute these jobs after transaction data is loaded.

Option B: Alternate models using Analytic & Calculation View:

An alternate modeling using Analytic & Calculation View

  • Create an Analytic View with left outer join (m:n) to Attribute View
  • Create a Calculation view with a projection step filter, from date <= IP_DATE and to date >= IP_DATE. (where IP_DATE is input parameter i.e. report date)

Advantage:

  • No need to create the ETL-jobs to fix master data

Disadvantage:

  • Input parameter required.
  • The join between the Fact table and the attribute view will always be executed (even if no attribute is selected from the attribute view). It may have performance impact in certain scenarios.

Option C: Re-develop (part of) the models using SQL-Script: Re-develop part of the models using SQL-Script.

Advantage:

  • No need to create the ETL-jobs to fix master data

Disadvantage

  • Significant development effort
  • In some cases, lower performance since no filter push down
Tags: