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
and so on.
Now if i just had this by Stocks and not traders - the Total_Qty should adjust
i.e
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.
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.
- SAP Managed Tags:
