Export complex Bex query data into DSO

[Former Member]

Business scenario

Sometimes you created a complex Bex query (with lot of restricted/calculated key figures and also cells, defined by cells definition)

Usually this type of query takes a lot of time to be executed.

Also you don't want to give this query to end users, but create simpler one, based on fetched data with quick runtime and without complex key figures and defined online cells.

There are several ways to fetch data from query into DSO:

• APD (Analysis Process Design). This is standard way to fetch data from query into DSO (type of direct update).
• RSCRM_BAPI TC. This is also standard way to fetch data from query into extract table and then load DSO from this table using standard ETL process.
• Custom report for fetching data to extract table and then load DSO from this table using standard ETL process.

APD and RSCRM_BAPI have advantages and disadvantages as shown below:

APD

Advantages	Disadvantages
Standard solution	Not able to operate with cells definition
Easy to implement
Can be scheduled in process chain (has own process type)

RSCRM_BAPI

Advantages	Disadvantages
Standard solution	Can not be scheduled in process chain without triggering an event. Should be executed manually
Able to operate with cells definition

We need a solution with following requirements:

1. Our query has cells with cell definition, so the solution should be able to operate with cells definition.
2. Should have possibility to be scheduled in process chain without events triggering (straight scheduling).

It is clear that not APD nor RSCRM_BAPI solutions alone are not fulfilling our requirements.

Goal

This blog demonstrates how to fetch data from complex Bex query (with cells definition) into extract table using custom report and schedule it in process chain without using events triggering.

Prerequisites

We will use extraction table created by RSCRM_BAPI so you need to create an extract via RSCRM_BAPI.

Goto TC RSCRM_BAPI and select your complex query. Please write down query view name as it shown on the screen - we will use it further.

Create extract by clicking on Extract button. Set Extract Type - Table and Extract Contents - Initialize.

General schema

• Create report via SE38 that fetches in background query data into extract.
• Create variant for report with query view name and extract name.
• Create DSO, data source, transformations, DTP for DSO loading.
• Create process chain

Implementation

Report creating

Goto SE38 and create report (lets say Z_RUN_QUERY).

Input parameters of this report should be:

1. Query view name
2. Extract name (created early in RSCRM_BAPI).
3. Overwrite/Add values to extract.

Add following code into report:

REPORT  Z_RUN_QUERY.  DATA: error(50)     TYPE c. DATA: g_t_return    LIKE bapiret2 OCCURS 0. DATA: l_proc        LIKE rscrmmon-processed. DATA: gv_batchid    LIKE RSCRMMON-BATCHID,       gv_jobname    LIKE TBTCO-JOBNAME,       gv_actstatus  LIKE TBTCJOB-STATUS,       gv_jobcount   LIKE TBTCO-JOBCOUNT,       gv_dbstatus   LIKE TBTCJOB-STATUS,       l_status      TYPE RSCRMBSTAT,       l_wait        TYPE i.  DATA: l_t_crmvar    LIKE RSCRMVARP OCCURS 0,       l_s_crmvar    TYPE RSCRMVARP.  DATA: lv_version_ip TYPE /bic/oizm_ver.  PARAMETER: p_repuid   TYPE rscrmrepuid OBLIGATORY. PARAMETER: p_extrnm   TYPE tabname OBLIGATORY. PARAMETER: p_overwr   LIKE RSCRMCONTXT-ACTIVE DEFAULT 'X'.  START-OF-SELECTION. *Open Query   CALL FUNCTION 'RSCRMBW_REPORT'     EXPORTING       i_mode      = 'OPEN'       i_reportuid = p_repuid     TABLES       e_t_return  = g_t_return.    IF g_t_return IS NOT INITIAL.     READ TABLE g_t_return TRANSPORTING NO FIELDS WITH KEY type = 'E'.     IF sy-subrc = 0.       CONCATENATE 'Errors occurred when opening query' error INTO error.       MESSAGE error TYPE 'E'.       EXIT.     ENDIF.   ENDIF.  *Execute Report   CALL FUNCTION 'RSCRMBW_REPORT'     EXPORTING       i_mode         = 'START'       i_reportuid    = p_repuid       i_execmode     = 'TABLE'       i_extract      = p_extrnm       I_CLEAREXTRACT = p_overwr     IMPORTING       E_BATCHID      = gv_batchid       E_JOBNAME      = gv_jobname       E_JOBCOUNT     = gv_jobcount     TABLES       e_t_return     = g_t_return.    IF g_t_return ISNOT INITIAL.     READ TABLE g_t_return TRANSPORTING NO FIELDS WITH KEY type = 'E'.     IF sy-subrc = 0.       CONCATENATE 'Errors occurred when executing query' error INTO error.       MESSAGE error TYPE 'E'.       EXIT.     ENDIF.   ENDIF.    IF sy-subrc = 0.     WHILE ( L_STATUS NE 'SUCC' ).       CALL FUNCTION 'RSCRMBW_BATCH_STATUS_CHECK'         EXPORTING           I_BATCHID       = gv_batchid         IMPORTING           E_STATUS        = l_status         EXCEPTIONS           NO_JOB_DATA     = 1           INHERITED_ERROR = 2           OTHERS          = 3.        IF l_status = 'RUNN' OR l_status = ' ' OR l_status = 'SCHE'.         WAIT UP TO 30 SECONDS.       ENDIF.     ENDWHILE.      IF l_status = 'SUCC'.       IF sy-subrc = 0.         WRITE 'Query finished'.       ELSE.         CLEAR error.         CONCATENATE 'Job ' gv_batchid ' failed.' INTO error.         MESSAGE error TYPE 'E'.       ENDIF.     ENDIF.   ENDIF.  *Close report   CALL FUNCTION 'RSCRMBW_REPORT'     EXPORTING       i_mode      = 'CLOSE'       i_reportuid = p_repuid.    IF g_t_return IS NOT INITIAL.     READ TABLE g_t_return TRANSPORTING NO FIELDS WITH KEY type = 'E'.     IF sy-subrc = 0.       CONCATENATE 'Errors occurred when closing query' error INTO error.       MESSAGE error TYPE 'E'.       EXIT.     ENDIF.   ENDIF.  END-OF-SELECTION.

Check and activate the report.

Report variant creating

Goto SE38, enter your report name, select Variants radio button in subobjects and then press Change.

Enter variant name and press Create button.

Add values for report parameters:

• P_REPUID - query view name (as shown in RSCRM_BAPI)
• P_EXTRNM - extract name
• P_OVERWR - overwrite data (set X)

Save a variant.

Note: there is possibility to extend the report by additional parameters like query variables variant and etc.

Create DSO, data source, transformations, DTP for DSO loading

Create DSO with needed data structure, data source above extract table (the name of table should be taken from RSCRM_BAPI as shown below), transformation and DTP according to your needs.

I will not elaborate about this section as there is not main blog's goal and most of BI developers are familiar with this topic.

Create process chain

Goto RSPC TC and create process chain.

First step of process chain should be starter.

The next step would be ABAP program. This is our early created report for fetching data from query into extract table. This program will be executed with variant we created.

The rest steps are very familiar for BI developers:

• PSA cleansing (if needed)
• Info package executing (if exist)
• DTP executing
• DSO data activation

At the end of whole process you will get in DSO the same data as in complex query, but already calculated and without online cells definition.