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:
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:
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:
Restricted Key Figure (RKF) of Revenue C:
Calculated Key Figure (CKF) of Revenue A:
Query Structure will be as follows:
On execution, the output of the query will be as follows:
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.
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:
Select the query used for calculation of "Revenue A" as follows:
Drag the direct update DSO from Data Targets in the Analysis Process Designer and choose T_REVTAR as follows:
From the tansformations, drag ABAP routine and insert the coding as follows:
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:
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:
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:
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:
As dicussed initially for Country 100 and 104 revenues are maintained in the DSO TEST_REV.
