BPC 10.1 Embedded – New Features for Debugging a Planning Function type SQL-script
With BPC Embedded and PAK customers have the possibility to create their own HANA enabled planning functions by using SQL-script. The possibilities for testing and debugging SQL-script procedures were not as sophisticated and easy to use as with ABAP. In order to be able to easily test and analyze the planning function code we have offered different possibilities to create a test environment for SQL-script planning functions.
With the test environment we provided the following features:
- Get a snapshot of the selected plan data and store it in a table in HANA
- Create a debugging procedure that starts the actual SQL-script (planning function) with the HANA tables containing the snapshot
In order to debug the SQL-script you had to open the HANA modeler, start a debug perspective, set a break-point, and run the debugging procedure. For each SQL-script you wanted to debug you had to create a new test environment with a new debugging procedure. It was not possible to debug entire planning sequences in one execution but you had to test each planning function individually.
We have described this methods in an older blog (with video):
Debugging SQL-Script based Planning Functions in BPC 10.1 Embedded/PAK
The good news is that now debugging SQL-script planning functions is basically as easy as debugging ABAP planning functions. Briefly spoken you just set a break-point in the coding you want to debug and start the planning function. But well – you probably want a slightly more detailed version of the story…
First of all, in order to use this new feature, you have to be on the correct software level (or higher):
- HANA DB SPS9
- SAP NetWeaver 7.50
- ABAP Development Tools 2.51.
Then you should implement you planning function as an AMDP (ABAP Managed Database Procedure). That means you create the ABAP class that acts as a wrapper for the planning function and in the same class you create an AMDP method that contains the SQL-script coding. In order to create this class, you use the ABAP development tool.
Once you are done with the coding create a planning function based on the new planning function type and either put it into a planning sequence or use it in any front end application (Analysis for Office or Design Studio).
Now set an SQL-script break-point by simply clicking next to one of the SQL-script statements.
As soon as you execute the planning function the system will stop in the SQL-script. Thus the break-point works in a similar way as an external ABAP break-point. You can now go ahead and analyze your coding. You can also display the content of each of the used tables (incoming data, outgoing data, intermediate views,…) and execute the SQL-script coding step by step. As you can set break-point in different SQL-script procedures you can also debug though several planning functions within one planning sequence.
This technique is also very important when you want to analyze your SQL-script coding when you have implemented characteristic relationships or data slices in SQL-scipt.
Also please note that it is not necessary anymore to create a special debug perspective. If you want to get more detailed information about the back-ground on the new AMDP debugger, please have a look at:
How to debug ABAP Managed Database Procedures using ADT – Basics
When I debugged my SQL script planning function with a simple code like e_view := select * from :i_view, I noticed that this code has been called 3 times, is there a reason for this? This will have a significant negative impact on the performance, right?