3 Analyzing and debugging HANA based BW Transformations
This blog is part of the blog series HANA based BW Transformation
In this part I’ll describe how a BW transformation can be analyzed and debugged if they would be executed in the database. Debugging refers primarily BW transformation with a SQL Scripts but in this part I’ll also describe the way to get the INSERT / SELECT statement.
3.1 Analyzing HANA based BW transformation
When a DTP with the option “HANA Execution mode” is executed the runtime framework will generate a SQL statement. The SQL statement will select the data from the source and insert the data directly into the target in one step. Therefore the generated SQL statement (INSERT AS SELECT) will be used in the ColumnView which is based on the CalculationScenario (SAP HANA Transformation), see blog HANA based Transformation (deep dive).
The DTP runtime framework provides the option to generate various SQL statements in simulation mode without executing them, see Figure 3.1. The SQL statements can then be used for further analyses purposes.
A SQL query based on a CalculationScenario (encapsulated in a ColumnView) is executed by the calculation engine (CalcEngine). Variables of a CalculationScenario are passed on by PLACEHOLDER to the CalcEngine, i.e. the DTP filter values would be handover by one or more filter PLACEHOLDER , depending on the designed data flow. Therefore we will not find the DTP filter directly in the WHERE condition.
In addition to DTP filter values there are more technical filters passed on by PLACEHOLDER . For example, one placeholder is used to read data in packages. Another is used to specify which source table will be used to read the data.
In addition to filtering a SQL statement based on a CalculationScenario in the form of PLACEHOLDER values it is also possible to use an additional WHERE condition. This is, for example, necessary if no variables are defined inside the CalculationScenario for these field, see WHERE condition of INSERT AS SELECT statement in Figure 3.1.
Figure 3.1: Generated SQL Segments based on the CalculationView”
In case ABAP logic is used to specify the DTP filter values, the DTP framework will execute the ABAP logic in a pre-step before generating the SQL statement. The resulting DTP filter values from the ABAP logic will be integrated into the SQL statement. The following filter placeholder (TR_3UFAFGH3GRHWINN06BPB0WEA91.$$filter$$) includes the technical filter condition based on the fields REQUEST and DATAPAKID to build the packages additionally a DTP filter for the field COSTCENTER:
'PLACEHOLDER'=('TR_3UFAFGH3GRHWINN06BPB0WEA91.$$filter$$',
'( "COSTCENTER"=''0000001000'' ) AND
( ( "REQUEST" = ''REQU_CY3V82RJ5IBTOIYX0XK0OJTD5'' AND
( "DATAPAKID" >= ''000001'' AND "DATAPAKID" <= ''000009'' ) ) ) '),
In this sample the COSTCENTER value comes from an ABAP based filter (Selection type 6 (ABAP Routine)).
The DTP simulation mode, see Figure 3.1, could be used to generate the SQL statements to analyze the data read requests. The simulation mode does not execute the generated SQL statements, neither the SELECT nor the INSERT statement. In simulation mode both statements are only generated for analyses purposes.
The first step to analyze a BW transformation with a SAP HANA Expert Script is to check the procedure import and export data. In many cases the issue can be identified by checking the result data. For example for initial or NULL values, or too many rows, etc.
Next I’ll describe the steps to check the input and / or the output data of a SAP HANA Expert Script.
Figure 3.2 shows in (1) a data flow with a BW transformation with a SAP HANA Expert Script (named as SQL).
(2) shows the CalculationViews from the corresponding CalculationScenario for the BW transformation.
| CalculationScenario in a stacked data flow |
|---|
| Keep in mind that the data flow is a stacked one as explained in the blog »HANA based Transformation (deep dive)« the CalculationScenario from the BW transformation will be reused in case of a stacked data flow! |
We need the three marked CalculationViews to investigate the procedure behavior:
- OPERATION.FUNCTION_CALL.INPUT
- OPERATION.FUNCTION_CALL
- OPERATION.FUNCTION_CALL.OUTPUT
Figure 3.2: Analyzing a SAP HANA Expert Script
To analyze each view it is necessary to capture the SQL statement from the DTP simulation mode and change some small parts, see (3) in Figure 3.2 :
- Change the named field list to »*«. This is necessary because the list could differ from the overall view
- Add the digit 1 at the end of the ColumnView name. This step is necessary because our SAP HANA Expert Script is embedded BW transformation of a stacked data flow.
- Add the CalculationView name separated by a dot to the ColumnView name
The CalculationView OPERATION.FUNCTION_CALL.INPUT can be used to check the procedure input parameter inTab. Both CalculationViews, OPERATION.FUNCTION_CALL and OPERATION.FUNCTION_CALL.OUTPUT, deliver the same data content. The column naming could be different but the content is the same. That means both views can be used to analyze the procedure result table outTab.
If the input and output data of the procedure does not help to identify the unexpected procedure behavior it is necessary to go deeper into the procedure analyses. It is possible to debug the corresponding database procedure for the SAP HANA Expert Script.
A new AMDP debugger for AMDP procedures is available with SAP NetWeaver 7.50 (SAP HANA Revision 97 or higher required).
3.1.1 Temporary Storage
Similar to the ABAP mode it is possible to keep the result after a simulation in a temporary storage. For a BW transformation running in the SAP HANA mode it is only possible to keep the result data after the whole transformation. It is not possible to keep intermediate result as in the ABAP mode. The reason is the runtime behavior. A SAP HANA executed BW transformation processes the data in chunks, see INSERT AS SELECT in the first blog of the series HANA based BW Transformation.
Figure 3.3 shows the necessary steps to keep the result data available if the DTP is run in simulation mode. To achieve this enable Expert Mode, see (1) and ensure that the flag After Transformation is set, see (2).
Figure 3.3: Temporary Storage – HANA
In the DTP monitor you can check the result if you expand the Data Package node, see (3). The icon can be used to inspect the data in detail, see (4).
3.1.2 Debugging in BW 7.40 and SAP HANA < SP09
To enable customers on an older release and / or older revision, the BW transformation framework provides an option to generate a prepared database debug procedure to run the SAP HANA Expert Script in a native SAP HANA runtime environment.
| Feature availability |
|---|
| The feature described here to create a debug procedure should only be used in a BW 7.40 system. In a release higher than BW 7.40 it is recommended to use directly the AMPD debugger, see paragraph »AMDP Debugger (BW 7.50 and HANA SP09 or higher)«. The feature will be disabled in BW 7.50. To use the debug feature in BW 7.40 it is necessary to add the RSADMIN parameter DTP_HANA_DEBUG_SCRIPT and set it to ‘X’. |
To check if the AMDP debugger is available on your system just try to add a break point in your AMDP method PROCEDURE, see Figure 3.4. Just double click in the field to the left of the line number to add an AMDP break point. If the AMDP debugger is available the line will be marked with a dot. If the debugger is not available you will get a message like in figure 3.4. If the AMDP debugger is available it is not necessary to generate a debugger procedure and you can go forward to paragraph »AMDP Debugger (BW 7.50 and HANA SP09 or higher)«.
Figure 3.4: AMDP Debugger in BW 7.40
If no AMDP debugger is available we can create a debug procedure to analyze the SAP HANA Expert Script behavior. Below I will describe the steps to analyze an SAP HANA Expert Script.
The AMDP debug procedure can be generated in the DTP simulation mode. Switch the processing mode of the corresponding DTP to Serially in the Dialog Process (for Debugging), see (1) in Figure 3.5 and enable the Expert Mode and execute the simulation. Switch to the tab Script Generation In the upcoming dialog Debug Request, see (2) and mark the SAP HANA Expert Script below your BW transformation. In case of a stacked data flow with more than one SAP HANA Expert Script (and more BW transformations), the list Generate Procedure for will offer one checkbox for each SAP HANA Expert Script per BW transformation. If you’ve marked the procedure you want to create, execute the debug request. In the result monitor of the simulation request is a new tab called Script Generation, see (3). Copy the entire procedure name into your clipboard (CTRL + C) and do not close the request monitor.
Figure 3.5: Steps to create an AMDP debug procedure
Next we will prepare for debugging. To start, it is necessary to create a debug configuration. Before we start to create the debug configuration we will add a filter on the procedure folder to reduce the list of available procedures in your system, see (1) in Figure 3.6. The debug configuration dialog does not offer any search functionalities. If the filter is applied you may notice some additional procedures. These are used internally and can be disregarded.
| AMDP database procedure |
|---|
Sometime, in BW 7.40, it could be happen that the corresponding database procedure is not generated. The report RSDBGEN_AMDP can be used to generate the database procedures for a given AMDP class. |
Do not remove the filter on the procedure folder. We need the filter later on if we create the debug configuration.
Figure 3.6: Prepare database procedure debugging
Open the <CLASSNAME>=>DEBUG_PROCEDURE and add a breakpoint (2) on each step where you want to stop to inspect your code during the execution. Keep in mind that the SQL Script debug only has the continue button to execute the code until the next breakpoint or the end. There is no option like Step In or Step Over as in the ABAP Debugger. As a consequence it is necessary to add a breakpoint on each code position before you start the process.
| Structure of the debug procedure |
|---|
The debug procedure is divided in two parts. The first part of the procedure is to create the input table (inTab) for the SAP HANA Expert Script procedure, see Figure 3.6 line 11 to line 45. To get the correct input data for the procedure the corresponding CalculationView from the CalculationScenario will be selected. Therefore the view name OPERATION.FUNCTION_CALL.INPUT is added to the ColumnView name in the select statement. Below the first part we’ll find the copied source code from the SAP HANA Expert Script. |
| Table for temp placeholder values during debugging |
|---|
The placeholder values will be read from the table /1BCAMDP/0BW:DAP:PLACEHOLDER_TABLE to keep the debug procedure as generic as possible. Do not use the table inside customer coding! |
Next we will create a debug configuration. Therefore we switch to the debug perspective (Window => Perspective => Open Perspective => Other… => Debug).
To create a new debug configuration open the Debug Configurations dialog (Run => Debug Configurations…), see Figure 3.7. For reuse purposes you can name your new debug configuration (1). Ensure that the option Procedure to Debug is selected and choose Catalog schema (2). Use the Browse… button (3) to select your debug procedure. Select your debug procedure in the upcoming dialog from the Procedure folder in your database schema, see (4).
Figure 3.7: Create Debug Configuration
The button Debug will store the debug configuration and starts the debugging process immediately. The debug perspective provides several views to get information about the procedure execution. Figure 3.8 shows the most important views in the context of SAP HANA database procedure debugging.
The Debug View (1) displays the stack frame for the suspended threads for the procedure you are debugging. On the lowest level you can see the number of the current processing lines.
The SQLScript view (2) shows the database procedure source code. On the left side next to the line number you can see a pointer indicating the currently processing line number (here line 72 at the end of the procedure).
Figure 3.8: Database procedure debugging
The Variable view (3) shows all defined variables. For a scalar variable, the value is displayed directly within the view (see column value). For variables based on a table type only the number of rows will be displayed in the view. For further detail of a table type variable opens the data preview (context menu of the table type variable => Open Data Preview). The data preview can be used to inspect the result, see (4). Further on the data preview provides the option to inspect the data types of each column, see (5). This feature is quite interesting in case of dynamic generated columns based on constant selection or calculations.
3.1.3 AMDP Debugger (BW 7.50 and HANA SP09 or higher)
The blog How to debug ABAP Managed Database Procedures using ADT - Basics provides good basic information around AMDP and the new AMDP Debugger.
To check if the AMDP debugger is available in your release just try to create an AMDP breakpoint. The procedure is described in paragraph »Debugging in BW 7.40 and SAP HANA < SP09«, see above.
In this paragraph I’ll provide a step by step description to debug a SAP HANA Expert Script by using the AMDP debugger.
To use the AMDP debugger open the BW transformation with the SAP HANA Expert Script. It is necessary to use the embed version of the Data Warehousing Workbench (RSA1) within the Modeling Tools for SAP BW powered by SAP HANA.
Use the button Expert Routine in the BW transformation UI, see (1) in Figure 3.9, to open the corresponding AMDP class with the implemented AMDP procedure (2).
Figure 3.9: Prepare AMDP debugger
An AMDP breakpoint can added by double clicking to the left of the line number. For each AMDP breakpoint a dot will be created. In case the AMDP debugger is active the dot will be green and the tooltip also provides information about the AMDP debugger status. In case the AMDP debugger is not active the dot is gray and the AMDP debugger can be activated by double clicking the gray dot.
The breakpoint context menu provides further AMDP debugger options.
To start the AMDP debugger it is necessary to execute the DTP in the processing mode Parallel SAP HANA Execution, see (1) in Figure 3.10. Do not run the DTP in the simulation mode! The DTP simulation mode for a BW transformation will not execute the generated SQL statements.
Figure 3.10: Start the AMDP debugging process
If the process is caught by the AMDP debugger the popup dialog, see (2) in Figure 3.10, Confirm Perspective Switch will be appear. If you confirm the dialog the IDE switches to the debug perspective, see Figure 3.11.
The debugging process now is similar to the database procedure debugging in paragraph »Debugging in BW 7.40 and SAP HANA < SP09«. (1) shows the call stack, (2) shows the procedure source code, (3) provides an overview of the available variables from the procedure and (4) shows a data preview.
Figure 3.11: Debugging an AMDP method / procedure
- SAP Managed Tags:
24 Comments
