Many companies are interested in the net difference of a financial statement of different categories or of different periods, exclusive of exchange rate fluctuations over categories and time. The way to do this is to apply the same exchange rate to data of different categories or periods.

While each category is associated with the specific exchange rate of the category and the period, if you want to run a currency conversion of one category with another exchange rate of a different period or category, you have to create a few interim sets of data to save the result and compare for differences.

With the new concept that I describe, you can dynamically change on the fly the assignment of an exchange rate to a category or period. Therefore, you can continue to run a currency conversion after an exchange rate assignment is updated in memory and calculate the difference on the fly. This design saves you disk space for interim data storage and improves the performance by reducing database query time and database write time.

The Concept of Net Difference Excluding Exchange Rate Fluctuation Over Time

Consider the following scenario: You want to see a net difference of actual of 2015.07 versus budget of 2014.07. The report difference is actual (2015.07)|exchange rate (actual, 2015.07) – budget (2014.07)|exchange rate (budget, 2014.07). However, the difference is constituted with two pieces — one from the exchange rate difference R (actual, 2015.07) - R (budget, 2014.07). The other is truly the difference between actual (2015.07) and budget (2014.07). After you retrieve actual (2015.07) and budget (2014.07) from the database, you can let the SAP Enterprise Performance Management (EPM) function or local function compare actual (2015.07) and budget (2014.07) as an alternative to the ABAP solution. From a performance standpoint, a good ABAP solution is better than a good use of EPM functions. However, the difference, even if it is significant, is still minimal — approximately a few seconds.

However, the EPM currency conversion is so tightly integrated with dimension attributes that a category can only be calculated at one type of rate for the same period. Data in category A of period T of local currency can only be converted to target currency for exchange rate X of period T. You cannot run this conversion using an alternative exchange rate (e.g., exchange rate Y of period T+12). This is not feasible in EPM functions or any Excel functions. For example, in Figure 1, EPM functions cannot run currency for combination C1R2 or C2R1.

Figure 1

The combination of currency and exchange rate

In the matrix shown in Figure 1, C1 stands for budget category, C2 stands for actual category, R1 stands for budget exchange rate at 2014.07, and R2 stands for actual exchange rate at 2015.07.

The report difference is C2R2 – C1R1, but the net difference between the two categories is C2R1 – C1R1. Here you are interested in the comparison of two different categories, using the same exchange rate to net out the impact of exchange rate fluctuations.

The Traditional Solution Design and the Problems

A traditional solution design is to write in script logic that runs on ABAP. Figure 2 illustrates the steps you need to take if you follow the traditional design. The time-consuming steps are explicit queries in steps 6 and 7, as well as implicit queries in currency conversion steps 2 and 4.

Figure 2

The traditional design process involves multiple interim categories and multiple queries to a database

New Solution Design Emphasizing In-Memory Calculation

In the new design I propose, you can use objected-oriented ABAP to complete the eight steps shown in the diagram in Figure 3.

Figure 3

The new design process calculation on the fly

In an ABAP program, querying usually accounts for 60 to 80 percent of total run time of ABAP programs, calculation usually accounts for up to 5 percent of total run time, and writing results to a database accounts for 5 percent to 10 percent of the total run time. If you compare the two solutions, the traditional design can only run currency conversion with one rate for one category. To run conversion with a different exchange rate, it has to create a new interim category. First, this increases disk space by having one more copy of data under this interim category. In this example, to run actual at budget exchange rate, it has to create a new interim category ACT_BUD to do this currency conversion because actual is fixed to run on actual exchange rate only. Second, after you save ACT_BUD to a database, in order to compare you have to query ACTUAL@ACTUAL exchange rate and query ACT_BUD@ budget exchange rate. That means two queries. This is the biggest factor impacting performance. After comparison, you save the comparison result to a new category ACT_VS_BUD.

With the new design, you run ACTUAL@ACTUAL exchange rate and hold it in memory. You can run ACTUAL@budget rate and hold it in memory as well. You then run comparison in memory without querying DB. This new design dramatically improves the performance.

This solution is independent of SAP HANA because the design saves query time by eliminating two queries. SAP HANA improves performance of both new design and traditional design because SAP HANA improves performance of SAP standard currency conversion and both the new and traditional design use SAP standard currency conversion.

Here are the critical points if you compare the new design against the traditional design. The new design can run on the fly a currency version of a category of data at different exchange rates, whereas the traditional design cannot.

Critical point 1. Change dimension master data on the fly (steps 1 and 4 in the new design). Figure 4 shows the default structure of the category dimension. Figure 4 shows the dimension master maintenance in the SAP Business Planning and Consolidation (BPC) admin console on a web browser. The default URL to display this screen is https://[server]:8000/sap/epm/bpc/web/ where [server] is the FQDN (fully qualified domain name) of the application server.

Figure 4

The dimension attributes structure

If you switch to the dimension master data view of the category dimension, Figure 5 shows the the original setting.

Figure 5

The original attribute values of category member Actual

Figure 6 shows what I plan to change to at run time. To display the screen shown in Figure 6, use the default URL https://[server]:8000/sap/epm/bpc/web/ where [server] is the FQDN (fully qualified domain name) of the application server.

Figure 6

The planned changes to attribute values of category member Actual

In Figure 6, the FX_SOURCE_CATEGORY (the column named FX_SOURCE_C…) stands for the category of the source data. If it is blank, this defaults to the ID of this record. RATE_CATEGORY is the category of the exchange rate that you use for currency conversion. If this is blank, it defaults to the ID of this record. RATE_PERIOD and RATE_YEAR are the period offset and year offset to the current period or year.

To be able to change or update dimension attributes FX_SOURCE_CATEGORY, RATE_CATEGORY, RATE_PERIOD, and RATE_YEAR of category dimension, you need to complete the following steps in ABAP:

1. Invoke if_uja_member_manager from CL_UJA_BPC_ADMIN_FACTORY.
2. Call get_attr_names_and_members() to get all members and their attributes of category dimension.
3. Loop through the member/attribute internal table to update the four attributes of the target category (in my example, member ACTUAL).
4. Send the changed record back to method save_attributes( ).
5. Call the method process( ) to process the dimension category.

Save the initial attribute values for this member ACTUAL in order to restore it after you run currency conversion. The sample code is shown in Figure 7.

field-symbols:
   <ls_tab>     type uj0_s_itab,
   <lt_content> type standard table,
   <ls_content> type any,
   <ls_id>      type any,
   <ls_attr>    type any,
   <ls_rowflag> type any.
  try.
     data(lo_member_manager) = cl_uja_bpc_admin_factory=>get_member_manager
(exporting i_appset_id = 'ENVIRONMENTSHELL' i_dimension_id = 'CATEGORY' ).
    catch cx_uj_no_auth cx_uj_static_check.
   endtry.

   try.
      data(lr_tab) = lo_member_manager->get_attr_names_and_members( ).
      catch cx_uj_gen_ddic_error .
      catch cx_uja_admin_error .
      catch cx_uj_obj_not_found .
      catch cx_uj_no_auth .
      catch cx_uj_static_check .
    endtry.
    assign lr_tab->* to <ls_tab>.
    assign <ls_tab>-content->* to <lt_content>.
    loop at <lt_content> assigning <ls_content> .
     assign component 'ID' of structure <ls_content> to <ls_id>.
     if <ls_id> = 'ACTUAL'.
         assign component 'FX_SOURCE_CATEGORY' of structure <ls_content> to <ls_attr>.          
         <ls_attr> = 'ACTUAL'.
         assign component 'RATE_CATEGORY' of structure <ls_content> to <ls_attr>.          
         <ls_attr> = 'BUDGET'.
                   assign component 'RATE_PERIOD' of structure <ls_content> to <ls_attr>.          
          <ls_attr> = '7'.
                    assign component 'RATE_YEAR' of structure <ls_content> to <ls_attr>.          
          <ls_attr> = '-1'.
                    assign component 'ROWFLAG' of structure <ls_content> to <ls_rowflag>.
                    <ls_rowflag> = 'M'.
      else.
        delete <lt_content>.
      endif.
    endloop.

    try.
        lo_member_manager->save_attributes(
          exporting
            it_data          = <lt_content>
          importing
            ef_success       = data(l_success)
            et_messages      = data(lt_message)
               ).
      catch cx_uja_admin_error .
      catch cx_uj_static_check .
    endtry.
    data: lt_dim          type uja_t_dim_name.
    try.
        lo_member_manager->process( exporting it_dim_list = value lt_dim( dimension = 'CATEGORY') if_set_offline = abap_false importing et_message_lines = lt_message_line ).
      catch cx_uja_admin_error cx_uj_no_auth.
endtry.

So the processing steps in ABAP are:

1. Store the current dimension master data attribute for category member ACTUAL.
2. Change the master data attribute for category member ACTUAL.
3. Run currency conversion of ACTUAL 2015.07 @ exchange rate of Budget of 2014.07.
4. Store result in memory (will cover this in critical point #2 below).
5. Restore saved current dimension master data attribute for category member ACTUAL.
6. Proceed with next current conversion and comparison.

Critical point 2. Hold the currency conversion result in the memory (steps 3 and 6). In the ABAP Business Add-In (BAdI) you can call function module UJK_SCRIPT_LOGIC_EXECUTE to execute a script logic. The currency conversion (FX_TRAN) script logic is generally like the code shown in Figure 8.

*RUN_PROGRAM CURR_CONVERSION 
      CATEGORY = ACTUAL 
 
      GROUPS = [USD]  
 
      TID_RA = 2015.07
 
      RATEENTITY = Global
 
      OTHER = [ENTITY=%ENTITY_SET%]
 
*ENDRUN_PROGRAM

The problem is after running this script logic, you have to save the result to a database. However, for my design, you want to hold the result in memory without saving to a database, waiting for the result of the next currency conversion for BUDGET @BUDGET RATE 2014.07 to compare against.

The design is to interrupt the BPC write-back with the preprocess BAdI. To do this, create an instance public class attribute DR_DATA type ref to data (Figure 9). As the last step in an SAP standard currency conversion, the result is written back to the database. Because you are running currency conversion for comparison on the fly, you don’t want to save it to the database. Instead, you want to hold it in memory. This is to interrupt with the standard write-back, stop writing interim result to the database, and hold the result in memory for next step.

Figure 9

The class attribute definition to hold result

Assume that your UJ_CUSTOM_LOGIC BAdI class name is ZCL_BPC_SCRIPT. This is the class where you run data manager package > script logic > ABAP BAdI class. In the form-based view, it is like the screen shown in Figure 10.

Figure 10

The class attribute definition in form-based view

After you hold data in lc_inst->dr_data in memory, you clear data in line 14 without writing it to the database. This code is shown in Figure 11.

Figure 11

Clear data without writing it to the database

After currency conversion, the SAP standard program writes the result to a database. Therefore, it accesses the UJO_WRITE_BACK interface. You interfere at the preprocess BAdI. In this example, you have ZCL_UJR_WB_BADI as your preprocess WRITEBACK BADI class. You take ct_array and store it to your main process BAdI class for UJ_CUSTOM_LOGIC BADI, where you execute script via UJK_SCRIPT_LOGIC_EXECUTE, as lc_inst->dr_data. Then you clear CT_ARRAY to avoid writing it to a database. This accomplishes the work between steps 3 and 4 of the new design (i.e., store data in memory).

With the two critical points explained above, I now review the new design diagram. Steps 1 and 4 are the same in applying critical point 1 with different input (change or restore attribute at run time).

Steps 3 and 6 are the same applying critical point 2 (store data in memory).

Step 7: compare the result. You can retrieve the stored data and start to compare. The ABAP code detail is not covered here as that is a mechanical piece that is not the focus of this article. To begin the comparison, you can use the code shown in Figure 12.

field-symbols: 
<lt_actual> type standard table,
<lt_budget> typy standard table,
<ls_actual> type any.
assign dr_data-> to <lt_actual>.
assign dr_data2-> to <lt_budget>. "assume I stored budget currency conversion result to attribute dr_data2 in writeback BADI.

loop at <lt_actual> assigning <ls_actual>.
...
endloop.

In this new design you have used two BAdIs to work with an SAP standard function. Look at the new design in a different view as shown in Figure 13.

Figure 13

The new design: ABAP BAdI custom code interacting with the SAP standard currency conversion program

The red numbers and red lines show the sequence of the new design, which is identical to the new design diagram I mentioned earlier. The three containers describe the territories of the BAdIs and SAP standard functions. You started from the main BAdI class ZCL_BPC_SCRIPT and then called the SAP function to run currency conversion. You then called the writeback BAdI to store the result, came back to ZCL_BPC_SCRIPT to proceed with the next run, and ended it by executing the comparison method described in step 7 in mail BAdI class zcl_bpc_script.

George Chen

George Chen is the owner of Lynk, Inc. An SAP consultant, he has worked on SAP Netweaver ABAP/HANA/BPC/BW/IP/TPM/CRM for the past 17 years. He specializes in process integration, performance tuning, and introducing advanced ABAP design patterns in development. He has worked with SAP America many times to conduct ABAP performance tuning and troubleshooting. His focuses are ABAP for HANA and ABAP OO in SAP NetWeaver.

You may contact the author at george.chen@lynksapconsulting.com.

If you have comments about this article or publication, or would like to submit an article idea, please contact the editor.