Automating Execution of Multiple BW Queries using ABAP

[Former Member]

Use/Purpose:

This exercise will help in running multiple BW queries in background using ABAP program so that execution is automated and manual efforts are minimal.

When there are hundreds of BW queries to be checked after BW upgrade or due to any issues, the query automation program can be used to run the queries without manual efforts by running it in background.

For example, if a user wants to check whether 100 queries are running successful, manually executing each query would be time consuming and would require lot of human efforts. Instead, query checker program automates the process in such a way that all the queries are executed in background and result can be seen upon checking SM37 log.

Below is a sample screenshot:

Pre-requisites

• Create a table with maintenance which holds all the details of Query parameters so that the program to be created for running multiple queries in backend will fetch details from this ZTABLE.

• The maintenance table should have following fields:

• Info provider – Z_INFOPVD
• Query Technical name – Z_QUERY
• Parameter/Variable sequence number – Z_PARNUM

• This is to sequence the order in which the prompt values are given in the selection screen, If there are variables that are input ready for the queries to be checked then Prompt values

• Parameter name – Z_PARNAM

• Add the technical name of the variables used as input ready for the corresponding query.

• Parameter type –Z_PARTYPE

• S (single value), I (Interval), O (operator)

• Parameter Option – Z_OPTION

• EQ (EQUAL) , BT (Between)

• parameter sign – Z_SIGN

• I (INCLUDE)

• Parameter low Value – Z_LOW

• Prompt values to be given in the query selection screen

• Parameter high vaue – Z_HIGH

• BLANK. High value is blank for Parameter type = Single value. This will have value only when parameter type = Interval

A Z table ZPARAM_MAINT is created with the above fields as shown below:

Proposed Solution:

ABAP program ZFXX _QUERYCHECK is to be created to run multiple queries at backend fetching details from the maintenance table (ZTABLE).

Procedure:

• This program checks the infoprovider, technical name of the query and parameter details row by row from the above Ztable.
• Once all the information/parameters are fetched for a particular query, i.e if it finds a different query name (while fetching the details row by row),  it executes the query for previously fetched parameters.

For Example, Take Query, GFR2_001.  In Ztable, these queries have been maintained with 3 prompt parameters and corresponding prompt values are updated (highlighted in yellow in below screenshot).

     The query checker program runs in such a way that once the sequence maintained in field Z_PARNUM for GFR2_001 is checked and in the next row it finds a different query (GFR2_006) then the Query checker program will execute GFR2_001 with the details fetched from row 1 to 3 and saves the result in backend.

• Similarly it will execute all the queries till the last row of the table and result will be displayed once all queries maintained in the Ztable are executed.

ABAP logic:

REPORT ZFXX_QUERYCHECK.

Internal table variable and work area declaration

DATA :  E_AXIS_INFO TYPE  RRWS_THX_AXIS_INFO ,
E_CELL_DATA TYPE  RRWS_T_CELL ,
E_AXIS_DATA TYPE  RRWS_THX_AXIS_DATA ,
E_TXT_SYMBOLS TYPE  RRWS_T_TEXT_SYMBOLS .

FIELD-SYMBOLS :  <FS_E_AXIS_DATA> TYPE  RRWS_SX_AXIS_DATA ,
<FS_PARAMETERS> TYPE  RRXW3TQUERY ,
<FS_SET> TYPE RRWS_SX_TUPLE ,
<FS_CELLDATA> TYPE RRWS_S_CELL .

DATA :  T_AXIS TYPE STANDARD TABLE OF RRWS_SX_TUPLE ,
T_PARAMETERS TYPE STANDARD TABLE OF W3QUERY ,
T_CELL_DATA TYPE STANDARD TABLE OF RRWS_S_CELL ,
L_INDEX TYPE I VALUE 0 ,
L_NOOFCOLS TYPE I VALUE 0 ,
L_CELLDATAINDEX TYPE I VALUE 0 ,
L_POSITION TYPE I VALUE 0,
L_KEYFIGINDEX TYPE I VALUE 2 ,
W_OPEN TYPE  RLGRAP-FILENAME ,
it_parameters TYPE rrxw3tquery,
P_INFPRV TYPE RSINFOPROV ,
P_QUERY TYPE RSZCOMPID,
P_FNAME TYPE STRING,
is_parameters TYPE LINE OF rrxw3tquery,
it_tabvar TYPE STANDARD TABLE OF ZPARAM_MAINT,
is_tabvar TYPE ZPARAM_MAINT,
is_tabvar1 TYPE ZPARAM_MAINT.

TYPES: BEGIN OF TY_OP ,
FIELD1(20) ,
FIELD2(20) ,
FIELD3(20) ,
FIELD4(20) ,
FIELD5(20) ,
END OF TY_OP .

DATA T_OPITAB TYPE STANDARD TABLE OF TY_OP .
FIELD-SYMBOLS : <FS_OPITAB> TYPE  TY_OP ,
<DYN_FIELD> .
TYPES: BEGIN OF TY_RESULT ,
TY_INFPRV(20) ,
TY_QUERY(20) ,
TY_RES(250) ,
END OF TY_RESULT.

DATA WA_RESULT TYPE TY_RESULT.
DATA IT_RESULT TYPE STANDARD TABLE OF TY_RESULT.

START-OF-SELECTION .

Copies all data from table Ztable for query execution

SELECT Z_INFOPVD Z_QUERY Z_PARNUM Z_PARNAM Z_PARTYPE Z_OPTION Z_SIGN Z_LOW Z_HIGH FROM ZFXX_FMR_QMR_CHK
INTO TABLE it_tabvar.

IF sy-subrc = 0.

Sort it_tabvar by Z_QUERY Z_PARNUM.


LOOP AT it_tabvar INTO is_tabvar.

When the program finds a different query name ON CHANGE OF command is executed which will execute the query for which the details were previously fetched

On CHANGE OF is_tabvar-Z_QUERY.

PERFORM Query.

REFRESH it_parameters.
CLEAR: is_parameters.

ENDON.

PERFORM prepare_parameters.

This command gets executed when last row detail is fetched from the Ztable
At LAST.

PERFORM Query.

REFRESH it_parameters.
CLEAR: is_parameters.

ENDAT.

CLEAR is_tabvar.

ENDLOOP.

ENDIF.

loop at it_result into wa_result.

write : /10 wa_result-ty_INFPRV , 25  wa_result-TY_QUERY, 45 wa_result-TY_RES.

endloop.

Query checks the below parameter conditions during execution

FORM PREPARE_PARAMETERS .
Move is_tabvar to is_tabvar1.

Process each selection line and create query parameters based on it

CONCATENATE 'VAR_NAME_' is_tabvar-z_parnum INTO is_parameters-name.
is_parameters-value = is_tabvar-Z_PARNAM.
APPEND is_parameters TO it_parameters.

CASE is_tabvar-Z_PARTYPE.
WHEN 'S'. "Single value
CONCATENATE 'VAR_VALUE_EXT_' is_tabvar-z_parnum INTO is_parameters-name.
is_parameters-value = is_tabvar-z_low.
APPEND is_parameters TO it_parameters.
WHEN 'I'. "Interval
CONCATENATE 'VAR_VALUE_LOW_EXT_' is_tabvar-z_parnum INTO is_parameters-name.
is_parameters-value = is_tabvar-z_low.
APPEND is_parameters TO it_parameters.
CONCATENATE 'VAR_VALUE_HIGH_EXT_' is_tabvar-z_parnum INTO is_parameters-name.
is_parameters-value = is_tabvar-z_high.
APPEND is_parameters TO it_parameters.
WHEN 'O'. "Select option
CONCATENATE 'VAR_OPERATOR_' is_tabvar-z_parnum INTO is_parameters-name.
is_parameters-value = is_tabvar-Z_OPTION.
APPEND is_parameters TO it_parameters.
CONCATENATE 'VAR_VALUE_LOW_EXT_' is_tabvar-z_parnum INTO is_parameters-name.
is_parameters-value = is_tabvar-z_low.
APPEND is_parameters TO it_parameters.
CONCATENATE 'VAR_VALUE_HIGH_EXT_' is_tabvar-z_parnum INTO is_parameters-name.
is_parameters-value = is_tabvar-z_high.
APPEND is_parameters TO it_parameters.
CONCATENATE 'VAR_SIGN_' is_tabvar-z_parnum INTO is_parameters-name.
is_parameters-value = is_tabvar-z_sign.
APPEND is_parameters TO it_parameters.
WHEN 'V'. "Precalculated value set
CONCATENATE 'VAR_VALUE_EXT_' is_tabvar-z_parnum INTO is_parameters-name.
is_parameters-value = is_tabvar-z_low.
APPEND is_parameters TO it_parameters.
WHEN 'H'. "Hierarchy node
CONCATENATE 'VAR_VALUE_EXT_' is_tabvar-z_parnum INTO is_parameters-name.
is_parameters-value = is_tabvar-z_low.
APPEND is_parameters TO it_parameters.
CONCATENATE 'VAR_NODE_IOBJNM_' is_tabvar-z_parnum INTO is_parameters-name.
is_parameters-value = is_tabvar-z_high.
APPEND is_parameters TO it_parameters.
WHEN OTHERS. "Hierarchy, Formula, Text Variable.
CONCATENATE 'VAR_VALUE_EXT_' is_tabvar-z_parnum INTO is_parameters-name.
is_parameters-value = is_tabvar-z_low.
APPEND is_parameters TO it_parameters.
ENDCASE.
ENDFORM.                 

                                     PREPARE_PARAMETERS

Query Execution

FORM QUERY .

if NOT it_parameters is INITIAL.

CLEAR: P_QUERY,P_INFPRV.

Move is_tabvar1-Z_QUERY to P_QUERY.
MOVE is_tabvar1-Z_INFOPVD to P_INFPRV.

Clear is_tabvar1.

Fetches data from the Infoprovider and displays exception

CALL FUNCTION 'RRW3_GET_QUERY_VIEW_DATA'
EXPORTING
I_INFOPROVIDER                = P_INFPRV
I_QUERY                       = P_QUERY
  I_VIEW_ID                     =
I_T_PARAMETER                 = IT_PARAMETERS
IMPORTING
   E_AXIS_INFO                   = E_AXIS_INFO
E_CELL_DATA                   = T_CELL_DATA
E_AXIS_DATA                   = E_AXIS_DATA
  E_TXT_SYMBOLS                 = E_TXT_SYMBOLS
EXCEPTIONS
NO_APPLICABLE_DATA            = 1
INVALID_VARIABLE_VALUES       = 2
NO_AUTHORITY                  = 3
ABORT                         = 4
INVALID_INPUT                 = 5
INVALID_VIEW                  = 6
OTHERS                        = 7
.

IF SY-SUBRC <> 0.

Displays one of the below case after execution

      CASE SY-SUBRC.

If there is no data available for the query executed

WHEN 1. 

          Move P_INFPRV to WA_RESULT-TY_INFPRV.
Move P_QUERY to WA_RESULT-TY_QUERY.
Move 'NO_APPLICABLE_DATA' to WA_RESULT-TY_RES.
Append wa_result to it_result.
Clear wa_result.

If incorrect prompt/selection values are maintained for a  particular query in Ztable

WHEN 2.
Move P_INFPRV to WA_RESULT-TY_INFPRV.
Move P_QUERY to WA_RESULT-TY_QUERY.
Move 'INVALID_VARIABLE_VALUES' to WA_RESULT-TY_RES.
Append wa_result to it_result.
Clear wa_result.

Insufficient authorization

WHEN 3.
Move P_INFPRV to WA_RESULT-TY_INFPRV.
Move P_QUERY to WA_RESULT-TY_QUERY.
Move 'NO_AUTHORITY' to WA_RESULT-TY_RES.
Append wa_result to it_result.
Clear wa_result.

If query goes in ABAP debugger mode

WHEN 4.
Move P_INFPRV to WA_RESULT-TY_INFPRV.
Move P_QUERY to WA_RESULT-TY_QUERY.
Move 'ABORT' to WA_RESULT-TY_RES.
Append wa_result to it_result.
Clear wa_result.

If incorrect input values are maintained for a  particular query in Ztable

WHEN 5.
Move P_INFPRV to WA_RESULT-TY_INFPRV.
Move P_QUERY to WA_RESULT-TY_QUERY.
Move 'INVALID_INPUT' to WA_RESULT-TY_RES.
Append wa_result to it_result.
Clear wa_result.

If incorrect view is maintained

WHEN 6.
Move P_INFPRV to WA_RESULT-TY_INFPRV.
Move P_QUERY to WA_RESULT-TY_QUERY.
Move 'INVALID_VIEW' to WA_RESULT-TY_RES.
Append wa_result to it_result.
Clear wa_result.

  ENDCASE.

Upon successful execution of query

else.
Move P_INFPRV to WA_RESULT-TY_INFPRV.
Move P_QUERY to WA_RESULT-TY_QUERY.
MOVE 'Query is successful' to WA_RESULT-TY_RES.
Append wa_result to it_result.
Clear wa_result.
ENDIF.

ENDIF.

ENDFORM.

Program Execution:

The execution time of program depends on the number of queries defined in the Ztable. The time out setting for ABAP program is around 30 to 60 mins. If the number of queries are more, time taken will be more. So to avoid the program to time out during execution of reports its better to execute the program in background.

• Go to SE38 and give program name ZFXX _QUERYCHECK.
• Click on program -->execute-->background.

      

Query Checker Result:

Once the program is executed, it will take some time for the execution to complete as it depends on the number of queries and parameters that is been maintained in the Ztable.

You can check the final result of the program in SM37 job log once the job is complete as shown in below screenshot:

Conclusion:

This is an automation program which would be very helpful if there is a need of executing large number of queries to check if those had run successfully.