Skip to Content
Technical Articles

Dynamic Join in HANA

Exploring the Dynamic Link option while modelling a scenario led me to document my finds

Raw Data: Trades done by 2 Traders 1 and 2 in Dec. Ignore the SRC column – it represented the data source(brokerage account) for this exercise.

My requirement is to present a Total Quantity Traded irrespective of Date along with the Qty which takes date into consideration.

so for e.g

For total trade by trader and date – i would want

traderid date qty Total_qty
1 12/4/2018 350 750
1 12/3/2018 200 750
1 12/1/2018 200 750
2 12/1/2018 299 899

and so on.

Now if i just had this by Stocks and not traders – the Total_Qty should adjust

i.e

stock_code Date Qty Total_qty
C 12/1/2018 100 400
C 12/3/2018 200 400
C 12/4/2018 100 400

and so on.

____here is where the dynamic join can help____we require the measure to be grouped by different granularity levels

Here is how my view looks. A dynamic join can only be defined on multi-column joins.

 

For comparison purposes – I also have a view without the Dynamic Join being checked.

Correct output with Dynamic join

Running the same by Stocks – without the dynamic join

with dynamic join

Benefits include that you can use the same view to analyze data at different levels. In a dynamic join when a column is not requested by the query – an aggregation is triggered to remove this column and then the join is executed based on the requesting columns

and you need to be careful about your queries – for. e.g a cross where clause will bring the dimension into play and may not give you expected results for e.g. — here we are not changing the data set but just the introduction of the where clause causes both joins to come in play and mess up the results.

 

(This situation can be corrected by using another smart feature – “Transparent Filter”. You can set it to True for Trader_id and the above query will return correct results…separate blog for that)

also if you ran a query without requesting any of the joined columns – that would result in an error

SELECT “Date”,sum(“Qty”) AS “Qty”, sum(“TotQty”) AS “TotQty” FROM “_SYS_BIC”.”XSTR/DYNAMIC_JOIN_EXAMPLE”
GROUP BY “Date

SAP DBTech JDBC: [2048]: column store error: search table error: [34023] Instantiation of calculation model failed;exception 306116: At least one join attribute of the dynamic join should be requested on node ‘Join_1’

hence it is important to test and document method of usage when using these smart features. In an adhoc scenario where query behavior is not predictable I would be tempted to leave this to the front tool. SAP BI tools like WebIntelligence can handle this elegantly.

 

Be the first to leave a comment
You must be Logged on to comment or reply to a post.