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.