Technology Blogs by SAP
Learn how to extend and personalize SAP applications. Follow the SAP technology blog for insights into SAP BTP, ABAP, SAP Analytics Cloud, SAP HANA, and more.
cancel
Showing results for 
Search instead for 
Did you mean: 
former_member205880
Active Participant
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" ASC

If 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:

 

Thank you for attention!
12 Comments