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.

  • Why SAP Data Services

    SAP BusinessObjects Data Services delivers a single enterprise-class solution for Data Integration, Data Quality, Data Profiling, and Text Data Processing that allows us to integrate, transform, improve, and deliver trusted data to critical...

  • 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 Metadata Query Part 1

    Data Services provides full access to the repository metadata tables and views for metadata analysis. To access this metadata either we can use SQL SELECT statements or use the metadata reporting from Management Console.

  • 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 Metadata Query Part 2

    This article is a continuation of the previous topic related to Data Services Metadata Query. Let us explore more into the Data Services Repository Metadata.

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

  • Auditing in SAP Data Services

    A proper data reconciliation process must be in place in any data Extraction-Transformation-Load (ETL) process. A successful reconciliation process should only indicate whether or not the data is correct. But data reconciliation is not easy....

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

  • Error Handling, Recoverability- SAP Data Services

    Error Handling, Recoverability are the important aspects of any ETL tool. Some of the ETL tools have some sort of in-built error-handling and automatic recovery mechanisms in place.

  • Data Services Metadata Query Part 3

    This article is a continuation of the previous topic related to Data Services Metadata Query. Let us explore more into the Data Services Repository Metadata.