Skip to Content

In BW Integrated Planning, SAP provides the functionality of defining characteristic relationships on a planning cube. These so-called relationships can be used in 4 different ways – Attribute, Exit, Hierarchy and DSO. More information on this can be found here. Exit based characteristic relationships are used when the derivations are customer specific and cannot be derived directly via the other 3 types.

In order to check if a planning sequence can be executed in-memory, SAP recommends running the program RSPLS_PLANNING_ON_HDB_ANALYSIS. Based on the report results, we can analyze further, as to what is blocking the planning sequence from being executed in-memory. In this case, we figured out that if a characteristic relationship of type ABAP exit class is used, the planning sequence is NOT executed in-memory. In this blog post, I would like to describe how to analyze the results of this program and implement a quick fallback method that SAP recommends.

In transaction SE38, execute program RSPLS_PLANNING_ON_HDB_ANALYSIS. In the selection screen, any one of the options can be selected – Infoproviders, Planning functions or Planning sequence. We have chosen a planning function as an example.

/wp-content/uploads/2016/09/1_1028781.png

After execution, we see that the status is red.

/wp-content/uploads/2016/09/2_1028782.png


When double-clicking on the red status, the program provides additional information as to what is blocking the planning function to execute in-memory./wp-content/uploads/2016/09/3_1028787.png

/wp-content/uploads/2016/09/4_1028788.png

/wp-content/uploads/2016/09/5_1028789.png

AMDP in Exit Based Characteristic Relationships?

Based on the analysis from running the program RSPLS_PLANNING_ON_HDB_ANALYSIS, we checked to see if we could use an AMDP (ABAP managed database procedure) in the exit-class, as it is SQL Script based and will allow the planning cube to be PAK compliant.

When trying to use the AMDP interface, IF_AMDP_MARKER_HDB & implementing an AMDP method using IF_RSPLS_CR_METHODS~CREATE_HDB, the system throws a syntax error, because the importing parameter ‘I_TSX_SELDR’ in the method must be structured and all components of the row-type must be elementary. According to SAP’s AMDP documentation, the parameter interface of an AMDP method has to fulfill specific prerequisites (such as the one we faced). Thus, it can be concluded that the methods available for Characteristic Relationships are not AMDP compatible.

Alternative Approach – ABAP as a Fallback

As an alternative approach, SAP recommends ABAP as a fallback. In this case, the exit-class needs an interface IF_RSPLS_CR_EXIT_HDB to be implemented. The two methods of the interface, IF_RSPLS_CR_EXIT_HDB~GET_SQLSCRIPT_INFO and IF_RSPLS_CR_EXIT_HDB~GET_SQLSCRIPT_PARAMETERS have to be implemented but they can be empty. The characteristic relationship will call the interface methods at run-time and because the returned values for names of SQL-script implementations are empty, the corresponding ABAP-implementation is called as a fallback. The interfaces mentioned above only serve as markers, indicating that PAK deep HANA integration is requested.

/wp-content/uploads/2016/09/6_1028790.png

/wp-content/uploads/2016/09/7_1028791.png

/wp-content/uploads/2016/09/8_1028795.png

After making the above changes in the exit-class, re-run the program RSPLS_PLANNING_ON_HDB_ANALYSIS. It is very evident by the green status, that the planning function is now ready to execute in-memory!

/wp-content/uploads/2016/09/9_1028796.png/wp-content/uploads/2016/09/10_1028797.png

Conclusion and Next Steps

It is evident that AMDP is not ready to be used yet in Exit based characteristic relationships. The ABAP as a fallback approach is a quick and easy win, to make the planning cube PAK compliant. However, there are some limitations on how and when this method can be used and the developer needs to exercise caution on this. Please refer to the SAP note 1956085, for more details.

Based on the above findings we have considered the following next steps to be dealt with:

  • Test the behavior of all planning functions built on the cube, which has has a Characteristic Relationship with the ABAP fallback method implemented. It is possible that the planning function may give undesirable results and the planning function code may need to be revisited.
  • Raise an idea with SAP to have the Characteristic relationship interface be AMDP compliant
  • Check possibilities of using a normal SQL Script procedure in the exit class, instead of the fallback method
To report this post you need to login first.

13 Comments

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

    1. Anisha Lobo Post author

      Hi Gersh,

      Thank you. You could use either SQL Script or ABAP as a fallback, like I have mentioned in the blog post. The ABAP as a fallback method is a quick and easy win.

      Regards,

      Anisha

      (0) 
  1. Gregor Dieckmann

    Hi Anisha,

    it seems that the documentation is not clear. It is possible to use AMDP to implement exit relations or exit data slices, in fact, it is even recommended.

    The link to the documentation shows that there exist a special documentation about SQL Script implementations of exit relations and data slices. You mentioned the interface IF_RSPLS_CR_EXIT_HDB. This is an interface to provide information about the exit implementation:

    • GET_SQLSCRIPT_INFO: no value returned, the ABAP fallback is used
    • GET_SQLSCRIPT_INFO: return a value of procedure name, recommended is the method name of an AMDP. Then the system calls this method at run time.

    Observe that the system does not call the methods CHECK_HDB, DERIVE_HDB, CREATE_HDB contained in interface IF_RSPLS_CR_METHODS. These methods are only used by the system to call the SAP standard characteristic relationships.

    Why is method GET_SQLSCRIPT_INFO used?

    The reason is to allow the usage of the ABAP fallback and another reason is that ABAP allows usage of dynamic/generic data types and SQL Script procedures only allow static types (and don’t support nested tables as ‘I_TSX_SELDR’).

    To use an AMDP in your exit implementation proceed as follows: Assume you have implemented class ZCL_MY_EXIT for an exit relation (no derivation). You always have to implement CHECK and CREATE (in ABAP); in addition create AMDP methods (the names are just examples) AMDP_CHECK, AMDP_CREATE. Then GET_SQLSCRIPT_INFO looks like this:

      METHOD if_rspls_cr_exit_hdb~get_sqlscript_info.

         e_procedure_name_check  = ‘ZCL_ZTSC0_DATA_STORE=>AMDP_CHECK’.

         e_procedure_name_create = ‘ZCL_ZTSC0_DATA_STORE=>AMDP_CREATE’.

       ENDMETHOD.


    The methods signatures are as follows:


    METHODS amdp_check

            importing

              value(i_t_chas) type tn_t_chas “combinations to be checked

            EXPORTING

              value(e_t_chas) type tn_t_chas.”valid combinations

          METHODS amdp_create

            EXPORTING

              value(e_t_chas) type tn_t_chas.”created and valid combinations

    You have to define the (table) type TN_T_CHAS in the PUBLIC section of the class. The field names in the line type have to be the field names used in the relation, cf. FIELDNM in table if_rspls_char_relation~n_ts_chas. If your exit class inherits from CL_RSPLS_CR_EXIT_BASE then FIELDNM is the field name of the characteristic in the master data table of the InfoObject. Assume the InfoObjects (characteristics) used in the relation are ZTSC0OCCA, ZTSC0OCCC, ZTSC0OCCE  then the types may look like this:

      types:

           begin of tn_s_chas,

             /BIC/ZTSC0OCCA type /BIC/OIZTSC0OCCA, “might be cost area

             /BIC/ZTSC0OCCC type /BIC/OIZTSC0OCCC,“might be cost center

             /BIC/ZTSC0OCCE type /BIC/OIZTSC0OCCE, “might be cost element

           end of tn_s_chas.

         types

           tn_t_chas type STANDARD TABLE OF tn_s_chas

                          with default key.

    In the implementation of AMDP_CHECK, AMDP_CREATE you can now use SQL Script syntax to implement the methods.

    Remarks:

    • E_PROCEDURE_NAME_CREATE will only be called in BW 7.50 SP04 or higher
    • Never create DB NULL values in SQL Script exits, this will lead to errors later. Check the DB documentation whether NULL values may be produced; a typical example is a LEFT OUTER JOIN.
    • Be careful with ‘initial’ values, the system expects the correct ABAP initial values, e.g. for a NUMC(3) field the initial value is ‘000’ and not just ”.

    Regards,

    Gregor

    (0) 
    1. Anisha Lobo Post author

      Hi Gregor,


      Thank you for the detailed and very clear explanation on how to use AMDP in exit based relationships. This really helps. Could you please give me a link to this special documentation that you are referring to? Somehow I am not able to locate it. I would like to dig in more.


      Also, based on your remarks in the end, it looks like I will have to wait to try out the AMDP approach, as I need it for the ‘CREATE’ part of the CR. We are unfortunately still on BW 7.4. 🙁


      Regards,

      Anisha

      (0) 
    2. Rajarshi Muhuri

      Do I HAVE to Implement a pure ABAP fall back class if I am implementing the characteristic relationship by HANA Stored Procedure ?

       

      i.e : ABAP fall back method has native ABAP code for fall back , but can it be empty . since the actual logic is implemented by HANA SP

      (0) 
      1. Gregor Dieckmann

        Hi Rajarshi,

        yes, you have to implement the relation also in ABAP as this code will be called e.g. in the result set of an input-ready query. Code near the UI layer is still ABAP based and it would in general be bad to transfer e.g.  the result set (the grid contents) to HANA  and to do the checks there. The again data would be transported to the code and not code to the data.

        And no, you have to write real code, the ABAP implementations should not be empty.

        Don’t confuse this with the ABAP fallback if the HANA implementations are empty.

        Regards,

        Gregor

        (1) 
  2. Thierry Chupin

    Hi Anisha,

    Thanks to your blog and Gregor comments, I succeeded to implement one simple characteristic relationship using a SQL script procedure in a AMDP method.

    I wrote a procedure for this, that I can share if you are interested in.

    Note that I tested it on SAP BW 7.4 SP13.

    Regards,

    Thierry

    (0) 
    1. Anisha Lobo Post author

      Hi Thiery,

      That’s great! Would really be interested in having a look at your procedure. Did you manage to implement a “CREATE” method or did you implement CHECK/DERIVE?

      Regards,

      Anisha

      (0) 
      1. Thierry Chupin

        Hi Anisha,

        I only implemented a DERIVE method.

        I tested also a CHECK method, but it is not called in my case. Only derive is.

        Could you send me your email address by direct message so that I can send you the procedure?

        I tried to attach it to a direct message, but it doesn’t work.

        Thanks and best regards,

        Thierry

        (0) 
  3. Gerd Schoeffl

    Dear All,

    Anisha has nicely described how to use the ABAP fallback for the characteristic relationships. The ABAP fallback can help customers to keep their ABAP coding in many cases and still use the performance advantages of HANA. If the fallback scenario is not suitable you will have to implement the characteristic relationships in SQL-script. We have recently posted a How to paper explaining how this can be done:

    How to…Create a Characteristic Relationship type SQL-script

    Best regards,

    Gerd

     

     

     

    (0) 
  4. Bhasin Naik

    Hi Gregor,

     

    I have CR which Contains AMDP Create, AMDP Check methods. When i execute the planning sequence, the cr is executed fine but when i run the query these methods are not getting called.

    Please help on this.

     

    Thanks & Regards

    Bhasin

    (0) 
  5. Bhasin Naik

    Hi gregor,

     

    I have CR which contains AMDP method for Create and check. The methods are getting called when i execute the planning sequence but it is not getting called when i run the query on same Aggregation level.

     

    Thanks & Regards

    Bhasin

    (0) 

Leave a Reply