Skip to Content

Aim: How to capture the data from BEx Query output and store in BI/BW Data Targets using Analysis Process Designer?

Let us consider the sample data:

  Sample Data

Scenario:

The source data contains revenue as null  for Product A and Product D.

Consider the requirement as below:

a) Revenue of A = Revenue of B + Revenue of C

b) Revenue of A should not be null in the DSO and should be maintained with the calculated value in the Query.

c) Revenue of D = Revenue of A

 Step 1: Create a Standard DSO TEST_REV and load the data to DSO.

Load the sample data to DSO. The data after loading to DSO will be as below:

FILE_DSO

 The aim is to calculate the revenue of A (A= B+C) and capture the data in the DSO for country 100 and 104.

The final result in the DSO should contain revenue as 400 for country 100 and 104.

Step 2: Create a Query for calculation of Revenue of A.

Query TEST REVENUE is created with two restricted key figures for ‘Revenue of B’ and ‘Revenue of C’ and a calculated key figure is created for calculating ” Revenue of A = Revenue of B + Revenue of C “.

Restricted Key Figure (RKF) of Revenue B:

RKFB

 

Restricted Key Figure (RKF) of Revenue C:

RKFC

 

Calculated Key Figure (CKF) of Revenue A:

CKFA

 

Query Structure will be as follows:

Query_Structure

 

On execution, the output of the query will be as follows:

Query_Output

From the above, revenue of A is calculated using a calculated key figure at runtime. Now the value 400 has to be maintained in TEST_REV DSO for country 100 and 104 which are currently blank.

Step 3: Creating a Direct Update DSO with a similar structure of TEST_REV DSO.

A direct update DSO T_REVTAR has to be created, since it is a prerequisite for APD.

Step 4: Create an APD for maintaining revenues of Country 101 and 104 in the standard DSO TEST_REV.

 

APD_CREATE

 

From the above Analysis Process Designer (APD) pane drag the query from data sources, since the data need to be captured from a query. A prompt for query will appear as follows:

Choose_Query

Select the query used for calculation of “Revenue A” as follows:

Query_Selection

Drag the direct update DSO from Data Targets in the Analysis  Process Designer and choose T_REVTAR as follows:

Choose_DSO

 

From the tansformations, drag ABAP routine and insert the coding as follows:

Coding

 

In the above code KYF_0001 indicates “Revenue of A”, where it is populated for country 100 and 104.

The Analysis Process Designer (APD) for Revenue will be as follows:

APD_REVENUE

 

The coding is written in Routine 1.On execution of Analysis Process Designer (APD) the data will be loaded to Direct Update DSO where the Country 100 and 104 will be loaded with Revenue 400 which is calculated in the query.

For checking the contents in the Direct Update DSO right click on the DSO as follows and click on Properties -> Display Data. The data after execution is as follows:

 

Taget_DSO

For the above, Country 100 and 104 are populated with revenue 400. Now transformations are created from T_REVTAR to TEST_REV and the data is loaded to countries 100 and 104.

Transformation mapping between T_REVTAR and TEST_REV will be as follows:

 Transformation

 

Since only the revenues need to be updated for Countries, only mapping of these two fields are done.

The data after loading the data from T_REVTAR to TEST_REV is as follows:

Output

As dicussed initially for Country 100 and 104 revenues are maintained in the DSO TEST_REV.

To report this post you need to login first.

Be the first to leave a comment

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

Leave a Reply