Why use Report Program to generate query output into a flat file and How ?

[Former Member]

Introduction

BI reports designed in query designer can be published in various manners. This data can be used for further processing by different SAP and non-SAP systems.

General approach is to push data from BI to other systems is using APD, but due to limitations of APD in some specific cases we had used report program using standard SAP Function Module RRW3_GET_QUERY_VIEW_DATA to generate query output into a flat file in application server.

This approach having some benefits over APD and we can avoid performance overheads of using APDs .

Why this Approach?

Objective: Our objective is to execute a query for certain set of selections and transfer the query output into a flat file.

Solutions:

• APD:

Most commonly used approach for this kind of requirement is APD. APD involve data source, transformations and data targets. It reads the data from data sources, then performs the transformations and finally stores the result data into the data targets. It is a workbench with an intuitive, GUI for the creation, execution and monitoring of analysis processes. APD is easy to use and efficient in most of the scenarios but have some limitations.

Limitations of APD’s

1. APDs having performance overheads due to intermediate storing of data in a BI database flat table after pulling it from info Sources.
2. In case mass data with millions of records, data needs to be processed in pieces using internal table in the analysis process runtime which holds data. For this, the option "Process data in memory" has to be deactivated; along with this the data will need to be stored in temporary database tables after each "step" of your analysis process.
3. It cannot be used for queries with multiple structures at rows and columns.
4. If a same OHD was required to be run for different queries, we can’t run it parallel for multiple queries because OHD is only capable of handling one query at a time.

• Report Program :

Report program have some benefits over APD and can work in specific scenarios where APD can’t be used.

1. It can be used for queries with multiple structures at rows and columns.
2. No performance overhead because of storing the data in a BI staging server is not in this case as now data is written directly to flat file that can be pushed later on in non-SAP system.
3. The new design has the capability of parallel processing.

Design Process steps for using Report Program

1. A process chain will execute a report program and passes different inputs for selection.
2. Report program will execute the Query for given selection using FM ‘RRW3_GET_QUERY_VIEW_DATA’.
3. Output of FM will be processed internally and generated the output structure dynamically at run time and write it into flat files (SAP Directory / user desktop).
4. After successful execution of report program process chain will execute a FMS step to transfer the file to non-SAP system.

Inputs to report:

Info provider name: optional field of type string (30)

Query name: mandatory field of type string (30)

View name: optional field of type string (30)

Parameters: optional table with two columns [Parameter name, string(30); Parameter value, string(250)]

Stored Procedure name: mandatory field of type string (50)   

Logic Applied

• Selection screen for inputs:

SELECTION-SCREEN BEGIN OF BLOCK B1 WITH FRAME TITLE TEXT-001.

SELECT-OPTIONS:  DEPT_DTE FOR DEPARTURE_DATE.       //for a range of values (multiple) needed for selection

PARAMETERS:

               QUERY TYPE RSZCOMPID DEFAULT 'ZZCRM_U02_Q002_ESM_MS',

               INFOPR TYPE RSINFOPROV DEFAULT 'ZCRM_U02'.

               FISCYR FOR FISCAL_YEAR DEFAULT ‘2011’.        //for single values

SELECTION-SCREEN END OF BLOCK B1.

• Passing selection parameters to function module internal table :

V_EXT_NUM = 1.

CONDENSE V_EXT_NUM.

LOOP AT DEPT_DTE.

  CONCATENATE  C_VAR_NAME V_EXT_NUM INTO T_TABPARAM-NAME.

  T_TABPARAM-VALUE = 'ZDEPT_DTE '. //technical name of query input selection parameter

  APPEND T_TABPARAM.

  CONCATENATE  C_VAR_OPERATOR V_EXT_NUM INTO T_TABPARAM-NAME.

  T_TABPARAM-VALUE = 'EQ'.             //operator

  APPEND T_TABPARAM.

  CONCATENATE C_VAR_VALUE_LOW_EXT V_EXT_NUM INTO T_TABPARAM-NAME.

  T_TABPARAM-VALUE =  DEPT_DTE. //value provided by selection screen

  APPEND T_TABPARAM.

  V_EXT_NUM =  V_EXT_NUM + 1.  //for multiple values this increment is compulsory

  CONDENSE V_EXT_NUM.

  APPEND LINES OF T_TABPARAM[] TO I_TABPARAM[].

• Function module Code:

CALL FUNCTION 'RRW3_GET_QUERY_VIEW_DATA'

    EXPORTING

      I_INFOPROVIDER          = INFOPR

      I_QUERY                     = QUERY

      I_VIEW_ID                  = ''

      I_T_PARAMETER           = I_TABPARAM

    IMPORTING

      E_AXIS_INFO               = I_AXIS_INFO

      E_CELL_DATA               = T_CELL_DATA     //columns data

      E_AXIS_DATA               = T_AXIS_DATA    //rows data

      E_TXT_SYMBOLS           = T_TXT_SYMBOLS

    EXCEPTIONS

NO_APPLICABLE_DATA         = 1

INVALID_VARIABLE_VALUES  = 2

      NO_AUTHORITY                   = 3

      ABORT                               = 4

      INVALID_INPUT                    = 5

      INVALID_VIEW                     = 6

      OTHERS                              = 7.

• Reading characteristics(rows )

Read table i_axis_info for axis 001.This will again be a table having fields

CHANM: Technical name of characteristics

CHAVL_EXT: To read key value of characteristics

CAPTION: To read text value of characteristics

Read all the rows and pass it to an internal table IT_ROW_COLUMN.

• Reading Key Figures

Read table i_axis_info for axis 000 to get number of key figures (columns) .Table T_CELL_DATA contain all the column values.

Read columns and populate them against rows into same internal table IT_ROW_COLUMN.

• Creation of file in Application server:

CONCATENATE '/interfaces/BW/'  ‘FILENAME.CSV' INTO V_FILENAME .

//you can create file at your desktop by using CONCATENATE 'c:/TEMP/'‘FILENAME.CSV' into V_FILENAME or it can be passed as a selection parameter. 

OPEN DATASET V_FILENAME FOR OUTPUT IN TEXT MODE

ENCODING DEFAULT MESSAGE D_MSG_TEXT.

IF SY-SUBRC NE 0.

  WRITE: 'FILE CANNOT BE OPENED. REASON:', D_MSG_TEXT.

  EXIT.

ENDIF.

• Transfer table content to file

LOOP AT IT_ROW_COLUMN ASSIGNING WA_ROW_COLUMN.

  CONCATENATE

          WA_ROW_COL-characteristic1_key

          WA_ROW_COL- characteristic2_key

          WA_ROW_COL- characteristic2_text

          WA_ROW_COL-keyFigure1

          WA_ROW_COL- keyFigure2         

  INTO V_STRING SEPARATED BY '|'.   //Type of separator required in file

  TRANSFER V_STRING TO V_FILENAME.

ENDLOOP.

CLOSE DATASET V_FILENAME.

The file written in application server can be seen after execution of report program by using transaction code AL11 in /interfaces/BW/ directory.

Limitations:

1. Query structure and output required is static and need to be coded in your report program.
2. One report program can be used only with one query so for each new query new report program is required.

Points to Remember: 

1. Selection inputs needed to be provided in correct format otherwise query will not generate error but will not filter the output.
2. If amount of data is too much then selection inputs should be selected properly to avoid dump in program.

Sample Example

Query Details :

Sample Code (Refer attachment)

Sample Output (Refer attachment)