Skip to Content

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:

/wp-content/uploads/2014/09/1_551116.png

Product_sales:

/wp-content/uploads/2014/09/2_551231.png

Now lets create an attribute view containing master table Product_master.

/wp-content/uploads/2014/09/3_551234.png

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.

/wp-content/uploads/2014/09/4_551235.png

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.

/wp-content/uploads/2014/09/5_551236.png

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

/wp-content/uploads/2014/09/6_551237.png

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 🙂

Regards,

Akshay

To report this post you need to login first.

6 Comments

You must be Logged on to comment or reply to a post.

  1. Petr Stastny

    Hi,

    honestly I do not understand the example. The table content you have introduced I would call inconsistent database. How can I have a sales for product which is not valid for that date?

    Petr

    (0) 
    1. MAZIN ALIKARAR

      The blog is explanatory. The main purpose is to explain how Temporal Table and Temporal Joins work within SAP HANA. It succeeded to deliver this message 🙂

      (0) 

Leave a Reply