Export complex Bex query data into DSO
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:
Not able to operate with cells definition
Easy to implement
Can be scheduled in process chain (has own process type)
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:
- Our query has cells with cell definition, so the solution should be able to operate with cells definition.
- 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.
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.
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.
- 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
Goto SE38 and create report (lets say Z_RUN_QUERY).
Input parameters of this report should be:
- Query view name
- Extract name (created early in RSCRM_BAPI).
- Overwrite/Add values to extract.
Add following code into report:
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.