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

Continue reading

How Classify The Classes based on Percentage in SAP HANA

In this blog I am classifying the Student classes based on there percentage. If Student get above 60 percentage then they will get “FIRST CLASS” or If Student get between 60 to 50 percentage then they will get “SECOND CLASS” or If Student get below 50 percentage they will get “THIRD CLASS”.

For Implementing this scenario I took two calculated columns one for calculating Percentage other one for Specifying Class.

Below are the steps to implement “How Classify The Classes based on Percentage in SAP HANA”.

Step 1: Create one Table with the name “STUDENT_DETAILS” in our schema with following structure

Insert Below Values into our Table

Step 2: Create Calculation view in our package with the name “STUDENT_CLASS”

Select STUDENT_DETAILS table in projection and Select the all columns.

Create One calculated column for Calculating Percentage.

Create another calculated column for calculated for class classification.

Use the following logic to classifying the classes.

if(“MARKS_PERCENTAGE”>=60,‘FIRST CLASS’,if(“MARKS_PERCENTAGE”>=50 and “MARKS_PERCENTAGE”<60,‘SECOND CLASS’,if(“MARKS_PERCENTAGE”<50,‘THIRD CLASS’,‘FAIL’)))

Add projection to aggregation and select the calculated columns MARKS_PERCENTAGE and CLASS.

Save and Activate the View.

select the data preview.

Output:

Above output we can see the CLASSES Classification in last column.

Continue reading