Skip to Content

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.

image

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

image

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:

Check and activate the report.

  

Report variant creating

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

image

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)

image

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.

image

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.

image

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.

To report this post you need to login first.

4 Comments

You must be Logged on to comment or reply to a post.

  1. Ethan Jewett
    Interesting approach.

    There are lots of reasons to dump query data into a DSO. But for a pure reporting scenario like this, what advantage does this have over utilizing the OLAP cache (possibly pre-populated using Information Broadcasting)?

    (0) 
    1. Andrey Uryukin Post author
      Hi.

      Thank you for your comments.
      In this blog I tried to simplify business scenario as much as possible, so it seems “pure reporting scenario” indeed.
      But in reality, I need this data in DSO for further using by another systems. I have to bring data to those systems in very special format (calculations, cells definitions) that, I think, was impossible (or possible, but in cumbersome way) by standard ways.

      Regards.

      (0) 
  2. Vinay Lohakare

    Hello Andrey,

    Excellent post.

    I had below query:

    If my BEx query has a ready for input variable will i be able to move the data?

    If yes , then how 🙂

    Thanks,

    Vinay

    (0) 
  3. Christian Boehringer

    Hi Andrey,

    very smart idea to leverage existing functionality and schedule load of data into a DSO object.

    However, as far as I know in RSCRM_BAPI you are not able to extract calculated or restricted key figures (designed in BEx Query Designer). You will only get standard key figure items in extract structure, since restricted / calculated key figures have type float which is not allowed in database tables.

    How did you overcome this issue? In your blog you are mentioning “complex queries (with restricted/calculated key figures)” – so I assume you were able to extract this complex structure into a database table.

    Regards,

       Christian

    (0) 

Leave a Reply