Using Temporal Join in Composite Provider in BW/4HANA
In the post I would like to talk about usage of Temporal Join in Composite Providers (HCPR).
I used BW/4HANA SP04 for demonstration.
Introduction
- From SAP BW 7.4 and in BW/4HANA new Composite Providers are the main objects for define unions / joins of existing persistent or virtual data models.
- Composite Providers are successors of MultiProviders and BW InfoSets. In classic BW Warehouse only BW InfoSet were responsible for SQL Join between InfoProviders.
- From SAP BW 7.5 SP04 and in SAP BW/4HANA Composite Providers also support modeling of temporal joins in order to show time flows (see SAP Documentation).
Demo Data Model
Let’s consider simple sales data model to demonstrate work of temporal join in HCPR.
Advanced DSO and InfoObjects in BWMT
Transaction sales data were loaded in aDSO ZAD_SALES.
Master data were loaded to time-dependent attributes of characterictics ZMANAGER and ZPRODUCT.
Composite Provider in BWMT
The aim of temporal join usage is to analyze sales volume with attribute values at date of actual sale transaction occurred, not at current date for example.
First we joined ZAD_SALES with ZMANAGER, don’t forget to select Key date ZDATE in aDSO. We had to add another time characteristic, because characteristic 0DATE weren’t allowed for key date selection.
Next step we joined result of first join J1 with ZPRODUCT:
As a result output we had:
Query in BWMT
We created a simple query for analyzing if temporal join is working correctly. Query definition is very simple.
Query Monitor
First of all we started query in RSRT for analyzing join SQL statement. Temporal join restrictions were placed at WHERE.
SELECT "J1ADSO2"."/BIC/ZSALESID" AS "K____5031",
"J1IOBJ1"."/BIC/ZGRADE" AS "K____5032",
"J1ADSO2"."/BIC/ZMANAGER" AS "K____5034",
"J2IOBJ1"."/BIC/ZPRODMN" AS "K____5042",
"J1ADSO2"."/BIC/ZPRODUCT" AS "K____5043",
"J1ADSO2"."/BIC/ZDATE" AS "K____5065",
"J1IOBJ1"."DATEFROM" AS "K____5077",
"J1IOBJ1"."DATETO" AS "K____5078",
"J2IOBJ1"."DATEFROM" AS "K____5086",
"J2IOBJ1"."DATETO" AS "K____5087",
SUM ("J1IOBJ1"."/BIC/ZBONUS") AS "Z____5033_SUM",
SUM ("J2IOBJ1"."/BIC/ZPRICE") AS "Z____5035_SUM",
SUM ("J1ADSO2"."/BIC/ZVOLUME") AS "Z____5054_SUM",
COUNT(*) AS "Z____1160_SUM"
FROM "/BIC/AZAD_SALES7" "J1ADSO2"
JOIN "/BIC/MZMANAGER" "J1IOBJ1" ON "J1ADSO2" . "/BIC/ZMANAGER" = "J1IOBJ1" . "/BIC/ZMANAGER"
JOIN "/BIC/MZPRODUCT" "J2IOBJ1" ON "J1ADSO2" . "/BIC/ZPRODUCT" = "J2IOBJ1" . "/BIC/ZPRODUCT"
WHERE "J1IOBJ1"."OBJVERS" = 'A'
AND "J2IOBJ1"."OBJVERS" = 'A'
AND "J1IOBJ1"."DATEFROM" <= "J2IOBJ1"."DATETO"
AND "J2IOBJ1"."DATEFROM" <= "J1IOBJ1"."DATETO"
AND "J1IOBJ1"."DATEFROM" <= "J1ADSO2"."/BIC/ZDATE"
AND "J1ADSO2"."/BIC/ZDATE" <= "J1IOBJ1"."DATETO"
AND "J2IOBJ1"."DATEFROM" <= "J1ADSO2"."/BIC/ZDATE"
AND "J1ADSO2"."/BIC/ZDATE" <= "J2IOBJ1"."DATETO"
GROUP BY "J1ADSO2"."/BIC/ZSALESID",
"J1IOBJ1"."/BIC/ZGRADE",
"J1ADSO2"."/BIC/ZMANAGER",
"J2IOBJ1"."/BIC/ZPRODMN",
"J1ADSO2"."/BIC/ZPRODUCT",
"J1ADSO2"."/BIC/ZDATE",
"J1IOBJ1"."DATEFROM",
"J1IOBJ1"."DATETO",
"J2IOBJ1"."DATEFROM",
"J2IOBJ1"."DATETO"
ORDER BY "K____5031" ASC,
"K____5032" ASC,
"K____5034" ASC,
"K____5042" ASC,
"K____5043" ASC,
"K____5065" ASC,
"K____5077" ASC,
"K____5078" ASC,
"K____5086" ASC,
"K____5087" ASCIf you are new with query Monitor – there is a good blog about it and BW Query push-down to HANA check.
Analyze Data Result
We opened query in Analysis for Excel, resulted data showed that temporal join were performed correctly, e.i.:
- First manager in October had GRADE_05 and in November – GRADE_15.
- Price of products showed also different in October and in November.
Even if we exclude almost all characteristics, show only sales volumes by sales managers grades and product names. History perspective is still correct.
Performance and Notifications
- Unfortunately processing of Temporal Join in HCPR is not currently pushed-down to HANA. It is “under discussion” status. For getting more information see 2063449 – Push down of BW OLAP functionalities to SAP HANA
- It means that it is working like old style BW InfoSet and performance are expected the same.
- During activation of HCPR with temporal join we had a reminder:
- In Query Monitor we didn’t get additional “HANA Calculation Engine Layer” Tab.
- In BWMT properties of HCPR and properties of BW Query weren’t changed
Conclusion
We considered new Composite Provider with Temporal join:
- Functionality is working correctly
- Logic not pushed-down to HANA
- 2063449 – Push down of BW OLAP functionalities to SAP HANA
Thank you for attention!
Hi,
I wonder can't we just activate the navigation attribute of Manager and Product on the aDSO to achieve the same result?
Requiring to create more joins on the CompositeProvider for Manager and Product master data to activate the temporal operands have the potential to make things very messy once the modeling gets more complicated.
Hi, Donnie! Thank you for question.
"However, those are to be defined in the CompositeProvider on top of advanced DSO, and are then fully available for reporting." - Exactly.
Why do you need to create new joins in composite provider? I suppose turning on key figures as display attributes and characteristics as navigation attributes would do. Right attribute values will anyway be picked up as per key date of query. This is the way we have been doing in traditional BW scenarios and I am sure we can achieve this in new world as well.
In fact, I would go a step further and say that given so many limitations with composite providers, I would not use it for purposes beyond wrapping up source info-providers in a presentable form. Is there anything a composite provider can do but a calculation view cannot? In my opinion, easiest way to achieve all virtualization scenarios is to expose BW objects like info-objects, ADSO as external HANA view, use them to create calculation views and wrap the calculation view in a composite provider (by assigning master data semantics like info-object attributes etc.).
In this way, I can achieve gauranteed execution in HANA database.
Hi,
"Right attribute values will anyway be picked up as per key date of query. This is the way we have been doing in traditional BW scenarios and I am sure we can achieve this in new world as well." - you are right, but i need to show attributes not at key date, but at date from transactional data (temporal join behavior).
"Is there anything a composite provider can do but a calculation view cannot?" - I agree that CV is much more agile, that current functionality of HCPR, but thema in the post about HCPR. You approach is good and due to such restrictions of HCPR may be more useful in some cases.
Thank you for participation!
Thanks Maksim for your feedback! That really helped. Sorry, I did not notice that you are picking attributes as on transaction date.
this is very informative blog.
i have a dilemma perhaps not related to temporal join. We are trying to achieve ‘provide’ joins in HANA graphical views (preferably). Anyone has any idea how to achieve this? We need to join few HR PA* tables to constrcut the employee master.
any leads are deeply appreciated.
cheers
Hi,
FYI.
in BW on HANA and BW/4HANA SP01 was the problem with reuse Composite Provider with temporal join in another CP.
Navigation attributes are available in Composite Provider based on temporal join. But Navigation attributes from CP with temporal join aren’t available to use in Other Composite Provider.
Hi Maksim,
Thank you very much for sharing this Information.
Although the description is very good, I am struggling with the declaration of the key date ("don’t forget to select Key date ZDATE in aDSO").
Could you please explain how this can be done? I could not find any possibility, neither in the aDSO nor in the CompositeProvider.
Thank you in advance!
Hi, Matti!
To set key date:
Br, Maksim
This is a very key component of this solution and needs to be up in the main blog. Thank you.
Dear Maksim
Please correct me if I wrong, but I can see as per the image above that master data TEXT (TXTMSG from ) would be part of ATTRIBUTE in this case. I mean, I wanted to know how can I include my TEXT master data time-dependent in this kind of temporal join configuration??
Thank you in advance
Best regards
Hi Maksim,
Thank you for this informative blog 🙂
I am working on similar scenario, where I try to create left outer join between 2 info-objects in composite provider.
When I try to activate, I get the below error -
I tried replacing AASE_AFAB with itself but doesnt help. Do you know why this error occurs ?
Is it because Key date is not set? What should be this key date and how should it be filled ?