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

Data for illustration

For the purpose of this tutorial, we will use 3 tables namely,

  • EMP - This table contains employee information including employee number, employee name, department number, salary etc.
  • DEPT - This is the master table for Departments. It basically contains department number and department name
  • SALGRADE - This table contains salary grades based on a range of salary

How to perform Join in data Services

Given the above data sets, let us assume we want to generate below results by joining EMP and DEPT table.

EMPNO   ENAME   DNAME
7839    King    Accounting
7369    Smith   Research
.... .... ....

If we want to obtain the above set using plain SQL, we will write an SQL statement like below joining both the tables using outer join

Select e.empno, e.ename, d.dname
From Emp e Left Outer Join Dept d
On e.deptno = d.deptno

In order to perform the same join in Data Services, we will take advantage of Query Transform. Query transform is a multipurpose transform in data services that basically imitate an SQL statement and lets you do varied things including joining, aggregation, filtering etc.

We will use query transform here to establish the join between EMP and DEPT table as shown in the screenshot below

Query Transform

Please see the video at the bottom of this article to go-through the individual steps in Data Services

How to perform Lookup in Data Services

Theoretically a lookup is similar to a SQL function call. A function takes one or multiple values as input and returns one (or multiple) value as output. In the video below, we have demonstrated how you may use a data services lookup.

Data Services Hands-on Video

Here is a quick video that show you the steps on how to perform Join and/or lookup in BODS.


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

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

  • SAP BODS Cluster Installation

    This article is a step by step guide on how to configure SAP BODS for High Availability using Windows Cluster services. To take advantage of fail-over support for SAP BusinessObjects Data Services services in a Windows Clustering Environment,...

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

  • RANK in Data Services

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

  • XML file generation using SAP Data Services

    This article will demonstrate how to generate XML target files using SAP Data Services. Here our objective is to generate XML file with employee and department information.

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

  • Fools Guide to BODS - Designer

    In our earlier article on Data Services we have learnt how to register the local repository with CMC. In this part of the article we will start using Data Services Designer.

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

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