Skip to Content

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!

To report this post you need to login first.

11 Comments

You must be Logged on to comment or reply to a post.

  1. Donnie Burhan

    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.

    (0) 
    1. Maksim Alyapyshev Post author

      Hi, Donnie! Thank you for question.

      • In this example you can’t make it directly because of key figures in attributes of Manager and Product. Use can’t make them navigational attributes.
      • The focus of the advanced DataStore Object is the management of transactional data with its own persistence. Enhanced reporting features, especially navigational attributes, are not available in this data model. However, those are to be defined in the CompositeProvider on top of advanced DSO, and are then fully available for reporting. For more details see SAP Note 2185212 – ADSO: Recommendations and restrictions regarding reporting.

       

      (0) 
      1. Former Member

        “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.

         

        (1) 
        1. Maksim Alyapyshev Post author

          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!

          (0) 
  2. Former Member

    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

    (0) 
  3. Former Member

    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.

     

     

    (0) 
  4. Former Member

    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!

     

    (0) 
    1. Maksim Alyapyshev Post author

      Hi, Matti!

      To set key date:

      1. Go to Composite Provider in HANA Studio BWMT
      2. Find aDSO as one of InfoProviders of Composite Provider, right click at it in “Source” area.
      3. Select date from available settings, e.i. fields with type DATS.

      Br, Maksim

      (1) 
  5. Hercar Carvajal

    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

    (0) 
  6. Saurabh Shiral

    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 ?

     

    (0) 

Leave a Reply