Logo DWBI.org Login / Sign Up
Sign Up
Have Login?
Login
New Account?
Recovery
Go to Login
SAP Data Services

Data Services Scenario Questions Part 4

Updated on Oct 02, 2020

In this tutorial we will discuss some scenario based questions and their solutions using SAP Data Services. This article is meant mainly for Data Services beginners.

This article is one of the series of articles written to showcase the solutions of different business scenarios in SAP Data Services. You may browse all the scenarios from the below list.

  1. Cumulative Sum of salaries, department wise
  2. Getting the value from the previous row in the current row
  3. Getting the value from the next row in the current row
  4. Getting total Sum of a value in every row
  5. Cumulative String Concatenation (Aggregation of string)
  6. Cumulative String Aggregation partition by other column
  7. String Aggregation

Scenario 4: Let's try to design a dataflow to get the the sum of salaries of all departments and this sum should repeat for all the rows. The target table data should look like below:

DEPTNOSALARYSUM_SALARY
10100010000
20200010000
30300010000
40400010000

Solution:

1. Let us reuse the File Format as defined in the previous examples as Source.

2. Next we use the same Batch Job, JB_SCENARIO_DS. Within the Job we create a Data Flow, say DF_SCENARIO_4.

3. Use File Format, from the Local Object Library as Source in the Data flow Workspace.

Data flow
Data flow

4. Next we place a Query transform, say QRY_ALL_ROW. First select the columns DEPTNO and SALARY from the Schema In of the Query transform and Map to Output.

Next we define a New Output Column, say DUMMY with data type int and specify the Mapping as numeric 1.

Query -All Rows
Query -All Rows

5. Next we use another Query transform, say QRY_SUM_SAL. Select the columns SALARY from the Schema In of the Query transform and Map to Output. Rename the column to SUM_SALARY and specify the Mapping value as sum(FF_SRC_DEPT.SALARY ).

Using the Data Services function sum we will calculate the sum of salaries of all departments. Next to assign this value as a column along with all the source rows we will Join the two flows or pipelines.

So for that we define a New Output Column, say DUMMY with data type int and specify the Mapping as numeric 1.

Query -SUM
Query -SUM

6. Next we place another Query transform, say QRY_JOIN. Select the columns DEPTNO, SALARY of Input Schema QRY_ALL_ROW from the Schema In of the Query transform and Map to Output. Also select the column SUM_SALARY of Input Schema QRY_SUM_SAL from the Schema In of the Query transform and Map to Output. Perform an INNER JOIN between the Input Schemas QRY_ALL_ROW and QRY_SUM_SAL based on the column DUMMY.

So for all the source records we get the sum of salaries for all departments repeated as a column value.

Query -JOIN
Query -JOIN

7. Finally we place a Template Table as Target in the Target Datastore.

Data Preview
Data Preview

Click here to read the next scenario - Cumulative String Concatenation (Aggregation of string).