Debugging SQL-Script based Planning Functions in BPC 10.1 Embedded/PAK

[gerd_schoeffl]

Remark: This blog is still valid in such respect that you can still debug SQL-script based planning functions in the way described here. However, when you are on BW 7.50 and later there is a much easier way to do so. Provided that you have implemented the planning function in an AMDP you can simply open your class in the eclispe based ABAP management tools, navigate to your AMDP and set a break point in there. Now run your (test) application - the system will stop automatically at the break point in the SQL-script debugger. Thus you do not need to generate a test environment anymore for debugging the SQL-script planning function but debugging is pretty much as easy as it used to be with ABAP exits. Have a look at the followingBPC 10.1 Embedded – New Features for Debugging a Planning Function type SQL-script


 

Original Blog:

 



 

In PAK (Planning Applications Kit) customer logic in planning functions can now be implemented as SQL-script procedures. Here in SDN we already have a number of How to papers on how this can be done. Unfortunately analyzing SQL-Script coding is a little more complicated than just debugging ABAP as during the execution of a planning function using an SQL-script procedure we are using multiple technologies.

 

In this video we give an example how an ABAP debugging script can be used to create an environment that can be used to easily debug your SQL-script coding. The idea is to start the planning function you want to analyze with a meaningful data selection, save a snapshot of this data to some new table on the database, and later run and debug the SQL-script planning logic using this database table. Thus the algorithm can be tested with correct data. You will find more information on this technique in the How to Paper How To...Use a Debugging Script to Easily Creat... | SCN.

 

In our example we use an AMDP (ABAP managed database procedure) to implement the SQL-script logic. You can find more information on using AMDPs in planning functions in the How to Paper  How To...Use Data from Another Aggregation Leve... | SCN.

 

If you want to generally learn about using SQL-script in planning functions please have a look at our How to paper How to... Use SQLScript for Planning Functions ... | SCN.

 

 

1^st Step: Check the planning function logic and set a break-point (0s-39s)

 

First we want to have a look at the definition of the planning function logic. We start the transaction rsplan and display all the available planning function types. In order to do so choose ‘Goto’ and select ‘Maintain Planning Function Types’.

 

From the list of the planning functions select the planning function type you want to analyze. In our case it is TE_DEMO_SCRIPT_2 (note that this not a standard planning function type).

 

Display the definition of the planning function type. You will see the name of the ABAP class implementing the planning function type. Now we display the ABAP class.

 

The ABAP class implements the orchestration logic for the planning function. As we use an AMDP in this example you can find a method (MY_HANA_PROCEDURE) that contains the actual SQL-script logic. We open this method to have a look at the SQL-script coding. In the SQL-script we are calculating the sales revenue (in key figure 0AMOUNT) from a sales quantity (0QUANTITY) and some prices stored in a HANA table.

 

We now want to prepare the generation of our test scenario. As this is done by using an ABAP debugging script we have to set a break-point at the right place. We open the method IF_RSPLFA_SRVTYPE_TREX_EXEC~TREX_EXECUTE and set a break-point before the execution of the SQL-script procedure.

 

 

2^nd Step:   Run the planning function and create the testing environment (40s-1:31s)

 

We open the Data Warehousing Workbench (rsa1) and start the planning function from a planning
sequence. You could do this as well using the planning workbench (rsplan).

 

The planning function stops at the break-point in the ABAP debugger. Now we change to the ‘Script’ tab in the debugger. We create the environment by using a debugging script. We click on the button ‘Load Script’ and load the script with the name ‘RSR_DEBUGGING_SCRIPT_STORE’.

 

Note: in the screen cam we use another script. This script is a copy of the original script. It was only used as some corrections were missing in the system used for capturing the recording. You should use the original script.

 

We now press ‘Start Script’. On the popup we choose the option ‘Save of View for SQLScript Debugging’ and continue. In order to more easily find the new tables in the database we can enter a prefix that will be used for the generated table(s) and for the created debugging procedure. We press ‘ok’ and leave the
ABAP debugger by pressing ‘F8’. The data table (and if necessary also a table for reference data) and a debugging procedure have been created in the database.

 

 

3^rd Step: Prepare the Debugging of the procedure (1:32s-2:40s)

 

We switch the HANA Studio, if necessary choose the correct system and expand the ‘Catalog’.
We search for the standard BW database scheme (often the name is ‘SAP’ + the system name).

 

We expand the folder for the procedures and look for the debugging procedure that has just been generated. The name uses the specified prefix and a timestamp as suffix. The debug procedure can be used to direct start the actual SQL-script containing the planning logic with the correct tables. We do not have to create any further procedure in order to debug our scenario.

 

A double click displays the content of the debugging procedure. We can see the name of the generated table with the transaction data and the actual call of our procedure that contains the planning logic. We have a look at this procedure and set a break-point in there (2:11s). Please note that any procedure that is
created as an AMDP will also be available in HANA as soon as you run it once.

 

We switch back to our debug procedure and set some additional break-points.

 

We also have a look at the table. As there are many tables in the default BW schema we
filter the tables by our prefix.

 

 

4^th Step: Debug the procedure (2:41s-end)

 

Now we change to the debug perspective in HANA Studio. In order to execute the debugging we need a debug configuration where we specify which procedure should be debugged. We select our debug procedure in the configuration. Finally we press ‘Debug’.

 

The system executes the procedure and stops at the first break-point. We can see the variables and tables used in the procedure on the upper right hand corner. By opening the data preview we can see the content of the tables.

 

As we continue (F8) we stop in the procedure containing the planning function logic. In there we calculate the revenue (field AMOUNT) as a product of the sales quantity (QUANTITY) and a price in an external table.

 

When stopping at the end of this procedure we can see that the result table (e_view) is now filled and contains the newly calculated delta(!) records. We continue with pressing ‘F8’ until our debugging session is terminated.