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 3: Lets try to Design a dataflow to get the Next Departments Salary for the current Department. If there is no next row exists for the current row, then the next row salary should be displayed as null. The target table data should look like below:

DEPTNOSALARYNEXT_SALARY
1010002000
2020003000
3030004000
404000NULL

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_3.

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_SEQ_NUM. First we select the columns DEPTNO and SALARY from the Schema In of the Query transform and Map to Output.

Next we specify a New Output Column in Schema Out of the Query transform. Let's name it as SEQ_NUM with data type int.

Sequence Number

Next we define the Mapping value as gen_row_number().

Using the Data Services function gen_row_number we can generate sequence number for each incoming records with a start value of 1.

5. Next we place two Query transform, say QRY_MASTER and QRY_DETAIL. For QRY_MASTER, select the columns DEPTNO, SALARY and SEQ_NUM from the Schema In of the Query transform and Map to Output.

For QRY_DETAIL, select the columns DEPTNO, SALARY and SEQ_NUM from the Schema In of the Query transform and Map to Output. Next change the Mapping value as QRY_SEQ_NUM.SEQ_NUM - 1 .

Detail Pipeline

6. Next we place another Query transform, say QRY_NEXT_SAL. Select the columns DEPTNO, SALARY of Input Schema QRY_MASTER from the Schema In of the Query transform and Map to Output. Also select the column SALARY of Input Schema QRY_DETAIL from the Schema In of the Query transform and Map to Output. Rename this Output column to NEXT_SALARY. QRY_MASTER is selected as source and we perform a LEFT OUTER JOIN with QRY_DETAIL based on the column SEQ_NUM.

JOIN- Master Detail

Since for all the detail records the sequence number is decreased by 1, upon joining the first row of the master will join with the second row of the detail. Hence we will be able to capture the Next Salary for the currently processing Department row.

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

Data Preview

Click here to read the next scenario - Getting total Sum of a value in every row.


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.

  • Data Services Scenario Questions Part 2

    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.

  • Using MERGE Transform in SAP Data Services

    This tutorial describes how to use MERGE transform in SAP Data Services (formerly known as BusinessObjects Data Services). A short video is also provided to provide hands-on.

  • Data Services Flatfiles Tips

    Often we come across scenarios where we have the flat file definition in an excel sheet and we need to create corresponding File Format in SAP Data Services. Alternatively we import file format definition from a Sample Source file.

  • 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...

  • Data Services Scripting Language

    We can use the Data Services Scripting Language to write scripts and custom functions to address complex logical expressions.

  • How to implement SCD Type 3 in Data Services

    In this tutorial we will learn how to implement Slowly Changing Dimension of Type 3 using SAP Data Services. SCD type 3 design is used to store partial history. Here we are only interested to maintain the "current value" and "previous value" of an...

  • 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.

  • Data Services Scenario Questions Part 6

    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.

  • Fools Guide to BODS - Registering Repository to CMC

    In our earlier article, we have learnt how to create a repository in BODS. Once that part is done, The final process before launching the BODS application is registering the repository with...

  • 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.