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 7: With reference to the previous department-employee source file, lets try to load the target table data as below:

DEPTNOENAME_LIST
10A,B,C,D
20E,F,G,H

Solution:

The approach to this Scenario will be exactly the same as that of Scenario 6, discussed in the previous section. First take a look at the Data flow below:

Data flow

The difference lies in the last three Query transforms namely- QRY_FORMAT, QRY_REV_SORT and QRY_FILTER. Apart from that the Data flow is an exact replica of the previous scenario solution.

In Query transform QRY_FORMAT we define a New Output Column, say SEQ_NUM_GRP with data type int and specify the Mapping as gen_row_num_by_group( QRY_CONCAT_NAME.DEPTNO ).

Query- Sequence By Group

Using the Data Services function gen_row_num_by_group we generate sequence numbers starting from 1 for every departments.

Next in the Query transform, QRY_REV_SORT, select the columns DEPTNO and ENAME_LIST from the Schema In of the Query transform and Map to Output. Specify the ORDER BY on DEPTNO Ascending and SEQ_NUM_GRP in Descending type.

Query- Reverse Sort

Finally in the Query transform, QRY_FILTER, we will filter only the first row of each department records coming from the previous transform. Select DEPTNO and ENAME_LIST from the Schema In of the Query transform and Map to Output. Specify the WHERE clause as is_group_changed( QRY_REV_SORT.DEPTNO ) = 1.

Query- Filter

Using the Data Services function is_group_changed we will capture only the first record of each set of departments.

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

Data Preview


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.

  • 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

  • SAP BODS Transforms

    This article deals with the various types of transformations available in SAP BODS. Transformations are in-built, optional objects used in dataflow to transform source data to desired output dataset objects available in Local Object Library under...

  • Map_CDC_Operation in Data Services

    Map CDC Operation is used to support relational or mainframe changed-data capture (CDC). This transform reads the source DML changes i.e. INSERT/UPDATE/DELETE and reflects the same in the target system. So the transform maps all the source rows...

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

  • Data Services Scenario Questions Part 1

    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.

  • How to use Data Services Pivot Transformation

    In this article, we will learn how to use SAP Data Services Pivot Transform. The Pivot transformation allows us to change how the relationship between rows is displayed. For each value in each pivot column, Data Services produces a row in the...

  • RANK in Data Services

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

  • SAP Data Services Analytic Functions

    In this tutorial we will learn how to implement Cumulative Sum in SAP Data Services (BODS). Since there is no available in-built Analytic Functions in SAP Data Services, we will achieve the end result by exploiting some in-built Data Services...

  • GROUP RANK in Data Services

    In this article, we will learn how to implement RANK and DENSE RANK operations on GROUP in SAP Data Services.

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