Using Temporal Join in Composite Provider in BW/4HANA

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.

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

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. 

◉ 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