Dynamic Join in SAP HANA. Clear Explanation.

[narasingha]

Hello All,

I am writing this blog post about Dynamic Joins in HANA. I found some blogs on Dynamic Join in HANA but they are confusing and did not give clear understanding what is exactly happening when we select Join as Inner join and then we make Dynamic Join as True.

As of now just see the Picture

 

Definition –

Whenever a join is defined as dynamic, then the modeler dynamically defines the join condition columns based on the columns requested by Dynamic joins improves the join execution process and helps reduce the number of records that join node process at run-time. Dynamic joins reduce the number of records processed by the join view node at run-time, which helps improve the join execution process.

Dynamic Joins VS Static Join

Dynamic Joins	Static Join
The join condition changes with fields requested in query. The query gives run-time error if the client query to the join doesn’t request a join column.	The join condition doesn’t change with fields requested in query.
Dynamic join enforces aggregation before executing the join. This means that, if a join column is not requested by the client query, its value is first aggregated, and later the join condition is executed based on columns requested in the client query.	Static joins the aggregation happens after the join.

Prerequisite

At least one of the fields involved in the join condition is part of the client query. If you define a join as dynamic, the engine dynamically defines the join fields based on the fields requested by the client query. But, if the field is not part of the client query, it results in query run-time error. We can use dynamic join when we have a composite join(More than one field in join condition).

Business Scenario –

We have Global system in HANA where we have multiple region.e.g-APAC ,EMEA, LAO and NA.

We have a requirement where we have to generate a report which will give the revenue numbers as per region,company and product . But the business does not want multiple HANA VDM created for this.

In the below steps we will be creating one single VDM where we will use the dynamic join to accomplish business requirement which is to show the gross revenue and revenue ratio of the product across the region.

We basically do 2 type of reporting for the same query.

1-Revenue ratio based on Region, Product and Company

2- Revenue ratio only based on Product and Company

If we take static join the Gross Revenue doesn’t show correct value.

The below example is part of my experience working with HANA in my current assignment.

Let us go to the demo here. The above scenario you can find in mant blog posts but none of the blogs describes what ia happening in the back ground.

so Let us see :

First create a table

Create Table "SYS_BIC.”REVENUE_MARKET”( “REGION” VARCHAR(10), “PRODUCT” VARCHAR(15),”COMPANY” VARCHAR(10),”SALES” INT);

Below is table created and below is the data.

Now let us go and see the CV I created :-

The same table is used in 2 aggregation Node.

Now let us go and see what is there in Aggregation_1

we can do a data preview and drag and drop dimensions as per our choice and can see how the data is aggregated for SALES  based on dimensions we select.

Now let us go to Aggregation_2 node and see what I have done there :-

Now on Aggregation_2 node you can see the Sales I meant GROSS_REVENUE column is populated based in Region and Product.

Now let us join these 2 aggregation Nodes on Product and Region.

Now let us see the data for what we have in our base tables and what data is present for

Sales ( which I renamed to Revenue and GROSS_REVENUE based on selected Columns) and we also calculated a column called REVENUE_RATIO we will see it's data also we select join as Inner and Dynamic Join as False.

Please see the revenue_ratio. Even if the column is not requested in the select query by the client still the join is applied on region which is causing the gross revenue and revenue ratio column to appear incorrect. The gross revenue need to be constant across companies that is it must sum based on product and must be constant for all companies which is not happening here! Hope you got the issue here. 🙂

Now we will apply the dynamic join as True and we will see the results

See the result :

In this way by using “dynamic join” we can achieve the required result. Now we can report the Gross Revenue across the region e.g ( APAC and EMEA).

Think of a scenario where you are creating a SAP Analytics cloud story by doing a live connection to a single VDM i.e, a Single CV and the end user may drag Region column to the report along with product and company or may be they want to view the data across companies for all regions. In those scenario you can implement dynamic join.

Expect this blog post helped you to understand on Dynamic join implementation with clear steps mentioned.

Regards,

Narasingha Prasad Patro.

SAP HANA XSA Developer