Technical Articles
Dynamic Join in SAP HANA. Clear Explanation.
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 –
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
Very well articulated and a good go to guide
Thank You Gopinath Sir.
Thx for sharing!
Very nicely articulated.
Well, is it possible for you to provide this information for HANA SQL scripted views as well....would be looking forward for the same.
Thanks
Sure I will do that.
Amit there are many blogs on scripted HANA CVs. They are clear and understandable. I only create blog posts which are not clear and when we encounter an error while following that blog. Also scripted CVs are obsolete and we have table function and table function node available. You can read them. They are quite clear.
Well explained, Thank you for your efforts.
What are your thoughts on comparing Dynamic join with optimize join technique they both almost do same thing how you can differentiate them in better way?
Dynamic join is applicable with inner join only.
With out inner join you cannot set the dynamic join to true.
Optimize join also more or less can do the same thing as is possible for Left outer join too.
U can read this blog
https://blogs.sap.com/2018/08/10/example-optimize-join-columns/