In this tutorial, we will learn how to use SAP data services Case Transform. Case transform provide us a means to implement "If... Then... Else" logic in data services. In the end a short video is provided with this tutorial to give you a hands-on feel in data services.

This article is part of our comprehensive data services tutorial "Learning SAP Data Services - Online Tutorial", you may want to check that tutorial first if you have not already done so.

What is 'Case Transform'?

Case transform is basically like 'select... case' in VB or 'switch... case' in C programming language. This is also similar to the "Router Transform" available in Informatica ETL tool. Whenever there are multiple filter conditions or we want to route data to multiple pipe lines based on some conditions, we use case transform.

Video Illustration on how to use Case Transform

We will use the EMP table (having employee names etc. along with their corresponding departments) to demonstrate the use of case transform. If you are not following this tutorial series from the beginning and do not know how the EMP table look like, just remember that, among other things, EMP table has employee name (column name ENAME), employee number (column name EMPNO) and department no (DEPTNO).

Now suppose, we want to generate 3 different flatfiles based on whether an employee belongs to department 10, 20 or 30. To do this, we will use a 'Case Transform' in the data flow and reroute the data based on the department numbers.


Options available under case transform

As you have seen in the video above, there are 3 specific options that are available inside case transform. Let's understand what are those

Produce Default Output

Case transform allows you to create your own labels (or groups) and define the condition or criteria, passing which data will flow into that group. However, it may also happen that incoming data does not match with any of the conditions in the label. Let's say, we created labels for department 10, 20 and 30. But the input record is associated with department 40. What will then happen to this record? By default, this record will flow into a default group. The label of this group is also 'default'.

However, if you are sure that the incoming data will not flow into any other groups apart from what you have designed or even if it flows, you do not need the data for the default group, you can turn-off the check box that says "Produce default output with label".

Row can be TRUE for one case only

Uncheck this option, if there is a possibility that one incoming row will satisfy multiple label conditions. For example, let's say the condition for the first label is deptno > 10 and second label is deptno > 20, then one incoming record with department number 30 will match both the conditions. In such cases, if you have 'Row can be TRUE for one case only' checked, the data will only go to any one of the labels.

Preserve Expression Order

As explained above, where we have one row matching more than one labels and we have ticked 'Row can be TRUE for one case only', the row will flow into any one of the satisfying labels. But to which label, the row flows depends on this condition. If we have this check box checked, the row will follow the order of the labels and accordingly flow into the first label.


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 use SAP Data Services Case Transform

    In this tutorial, we will learn how to use SAP data services Case Transform. Case transform provide us a means to implement "If... Then... Else" logic in data services. In the end a short video is provided with this tutorial to give you a hands-on...

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

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

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

  • Text Data Processing using SAP Data Services

    This article deals with Text Data Processing using SAP Business Objects Data Services with the intension of Text Analytics. SAP BODS provides a single ETL platform for both Structured and Unstructured data as well as Data Quality, Data Profiling...

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

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

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

  • One Stop to SAP BODI/BODS

    BODI Business Objects Data Integrator or BODS Business Objects Data Services is a GUI workspace that allows to create jobs that extracts data from heterogeneous sources, transforms that data using built-in transforms and functions to meet business...