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

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

Solution:

1. Let us reuse the File Format as defined in the previous example( Scenario 5 ) as Source.

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

3. At the Data flow level i.e. Context DF_SCENARIO_6, we Insert two Parameter using the Definitions tab. Lets name them as $PREV_NAME and $PREV_DEPT. Their Data types being varchar(100) and int respectively and Parameter type as Input.

Parameters- Data flow

At the Job level i.e. Context JB_SCENARIO_6, we initialize the Parameters $PREV_NAME and $PREV_DEPT using the Calls tab. We set both the Agrument values to NULL.

Parameters- Job

4. Next we create a New Custom Function from the Local Object Library. Lets name it CF_CONCAT_ENAME_BY_DEPT. Within the Custom Function Smart Editor, first we define four Parameters as below:

Parameter NameParameter TypeData type
$CURR_DEPTInputint
$PREV_DEPTInput/Outputint
$CURR_NAMEInputvarchar(20)
$PREV_NAMEInput/Outputvarchar(100)

Custom Function Definition

Also we modify the Return Parameter Data type to varchar(100).

5. Next we define the custom function as below and Validate the same.

if ( $PREV_NAME IS NULL OR $PREV_DEPT <> $CURR_DEPT )

begin
	
    $PREV_DEPT = $CURR_DEPT;
    $PREV_NAME = $CURR_NAME;
end

else
$PREV_NAME = $PREV_NAME || ',' || $CURR_NAME;	 

Return $PREV_NAME;

The purpose of defining the Parameter and Custom Function is to perform Parameter Short-circuiting. Here within the function, we basically set the $PREV_NAME Parameter of type Input/Output to concatenate all employee names till the current processing row. Since it is of type Input/Output the concatenated string value is passed back into the Dataflow Parameter. Also in this scenario, for change in department we reinitialize the concatenated string. So by using Custom Function we can modify and pass values to a Dataflow Parameter. Hence the Parameter defined at Dataflow level is short-circuited with the Input/Output Parameter of the Custom Function.

6. Lets go back and design the Data flow. First of all we take the File Format defined earlier, from the Local Object Library as Source.

Data flow

7. Next we place a Query transform, say QRY_SORT. First we select the columns DEPTNO and ENAME from the Schema In of the Query transform and Map to Output. Specify the ORDER BY on DEPTNO and ENAME in Ascending type.

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

Next we specify a New Function Call in Schema Out of the Query transform. Choose the Custom Functions from the Function categories and select the Function name CF_CONCAT_ENAME_BY_DEPT.

Next we Define Input Parameters. We specify the inputs as below:

$CURR_DEPT = QRY_SORT.DEPTNO
$PREV_DEPT = $PREV_DEPT
$CURR_NAME = QRY_SORT.ENAME
$PREV_NAME = $PREV_NAME

Function Input Parameters

Select the Return column as the Output Parameter.

9. Next we place a Query transform, say QRY_FORMAT. First we select the columns DEPTNO and Return from the Schema In of the Query transform and Map to Output. Rename the Return column to ENAME_LIST.

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

Data Preview

Click here to read the next scenario - String Aggregation.


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.

  • Top 50 SAP Business Objects Data Services (BODS) Interview Questions with Answers

    Learn the answers of some critical questions commonly asked during SAP BO Data Services interview.

  • How to install SAP BODS - Standalone

    This article is a step by step guide on how to install standalone SAP BODS Batch Job Server, Client Components, Web based Administrative Console and how to configure the same.

  • How to implement SCD Type 2 using History Preserve Transform in Data Services

    This tutorial teaches you how to use the "History Preserving Transform" in SAP Data Services (BODS) by demonstrating a practical use of this transform for the implementation of SCD Type 2. We have also provided hands-on video below so that you can...

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

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

  • SAP Data Services Custom Function

    We can create our own functions by writing script functions in SAP Data Services scripting language using the smart editor. User-script functions return a value.

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

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

  • Fools Guide to BODS - Repository Creation

    In the first chapter of this article we have learnt the very basic of BODS. In this part of the article we will begin with BODS Repository creation process

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