Using Temporal Join to fetch the result set within the Time interval in SAP HANA

[Former Member]

Hello All,

In this blog, i am going to explain how to use Temporal Join feature available in HANA to fetch the Records from transaction table based on Time interval fields of Master Table.

Problem:

Sometime we come across scenario where in we need to analyse the data by joining Transaction table and Master table on key column and fetch the data for a particular time period as speicified in From and To dates of Master table. In this case we need to go for a Non Equi join. This can be easily achieved through Script based Views where in you use either BETWEEN operator or > and < operator. How do we achieve the same using Analytical view in HANA?

Solution:

To achieve the above stated problem in Analytical view, we have to go for a Temporal join. You create a temporal join using the temporal column that specifies the time interval with the start and the end date. The result set is fetched based on the time interval mapped using the temporal column. That means a record is only included in the result set if the time interval lies within the valid time interval. This feature is available in SAP HANA starting from SP05.

I have created 2 tables Product_master and Product_sales table and loaded with sample data as shown below,

Product Master:

Product_sales:

Now lets create an attribute view containing master table Product_master.

Also we will create Analytical view with Product_sales table in data foundation layer and lets try to join above created attribute view in Logical join node.

In the logical join, lets create a temporal join between the date field of the fact table to an interval (to and from) field of the attribute view. The supported data types are: timestamp, date, and integer.

Now select Temporal column that indicates time column in fact table, from and to column from attribute view which specifies time interval and Temporal condition on whether to include or exclude Dates. The various options available for Temporal condition are, Include to and Exclude from, Exclude both, Include to. We will keep Exclude to and Include from option.

Activate and run the Analytical view and you can see the output.

We can see that P001 product value for date 2014-03-10, P003 product value for date 2014-04-10 and 2014-06-28 has been filtered since they fall beyond the time interval. Also you can notice P002 product value for date 2014-04-26 has been filtered since we have kept Exclude to and Include from option.

Thus we are able to achieve the intended result through Analytical view.

Thanks for reading this blog, Your Suggestions and feedbacks are most welcome :smile:

Regards,

Akshay