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.

Scenario 4: Lets 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

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

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

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

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

Data Preview

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


Have a question on this subject?

Ask questions to our expert community members and clear your doubts. Asking question or engaging in technical discussion is both easy and rewarding.

Are you on Twitter?

Start following us. This way we will always keep you updated with what's happening in Data Analytics community. We won't spam you. Promise.

  • Web Service Call in SAP Data Services

    This article demonstrates, how to implement external web services call in SAP Data Services.

  • Data Services Scenario Questions Part 4

    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.

  • SCD implementation from Source based CDC table

    Consider a scenario where we want to capture all or any changes in the source table to be maintained as history in our data warehouse dimension table. So if we consider day end batch load we will miss out any intermediate changes to the master data as...

  • Handling XML source files in SAP Data Services

    This article will demonstrate how to read data from XML based source files using SAP Data Services. Here our objective is to load employee and department information respectively from the source XML based file.

  • Table Comparison Transform to Implement Slowly Change Dimension (SCD) in Data Service

    In this tutorial we will learn a new SAP Data services transform, known as Table Comparison Transform and we will see how we may use this transform to implement "slowly changing dimension" (SCD) Type - I. Like before, we have added a video tutorial at the...

  • Learning SAP Data Services - Online Tutorial

    We are pleased to start this new tutorial page for SAP BusinessObjects Data Services (BODS). If you do not know SAP Data Services (BODS) yet but wish to master this ETL tool, you have come to the right place. Even if you are already familiar with...

  • DENSE RANK in Data Services

    In this article, we will learn how to implement DENSE RANK operation in SAP Data Services.

  • How to use Lookup and Join in SAP Data Services

    In this tutorial, we will learn how to perform lookup and join in SAP BusinessObjects data services using the Query Transform. In the way, we will also see how to use some of the basic inbuilt functions that are provided in data services. A short...

  • Fools Guide to BODS - Introductory Tutorial

    This article is a guide to the readers who are totally ignorant about the tool SAP Business Objects Data Services. It is presented in a very simple way so that readers would be familiar with common terms, terminologies and would be able to run...

  • How to handle multiple input file in SAP Data Services

    In this tutorial, we will learn how we can use a single data flow to process multiple input files of same structure using SAP Data Services. Like before, we have included one hands-on video with the tutorial to help you see the technique in action