USING BUSINESS CONTENT EXTRACTORS IN

DATA SERVICES
EIM161
Exercises
Teched 2011, Bangalore
2

This exercise is about reading SAP Financial data into a SQL Server database for whatever reason, maybe
for a reporting application. At first we will use the SAP provided Extractor 0FI_GL4, the same one used by
SAP BW. Then we will assume that this Extractor does not provide us with the data we need and build our
own ABAP program to read the data from the BSEG & BKPF table the way we want it to look like.

USING THE EXTRACTORS TO READ DATA

Login
Start SAP Business Objects DataServices Designer, Login into the Central Management Server using the
user “Administrator” and the password “abcd1234”. When you click on “Log On” a list of available
repositories is shown at the bottom, pick the “DS40_Local” one and click on “Okay” to start the application.

Definition
For better understanding, let us use the terms
 Project Hierarchy: Just an organizational screen of the current opened project
 Object Library: A window to the repository – all objects can be found there
 Workspace: The area where we draw everything
3

Create a new project

By selecting the menu item Project -> New -> Project you can create a new project in your personal
repository. The name of the project does not really matter as it is just an organizational entity, you might pick
the name “EIM161”.

Datastores represent the source and the target connections

Where the data is read from and loaded into is saved in the “Datastore” object. As you see two connections
have been created already, the SAP_ERP_M18 is a connection to one ERP system we all share. The
TARGET_DS datastore points to your personal SQL server database installed on your laptop.
As a datastore is an object, you can find it in the Object Library under the tab “Datastores”.
4

Import the Source, an Extractor

At the moment the datastore has no object to read from, we want to read from an Extractor. So we need to
import the metadata – not the data itself! – about the fields the Extractor provides us with and other technical
information. So we open the SAP_ERP_M18 datastore, rightclick the node “Extractor” and select “Import by
Name”. In the next dialog we import the SAP ERP Extractor called “0FI_GL_4”.

The Extractor Settings

Each imported Extractor can have different settings, the most important being the “Name of the project” as
this identifies the Extractor-instance. The name as such is again arbitrary, but if two programs use the same
Extractor-instance, both will get the delta of changes so effectively stealing it. In our setup we all use the
same SAP server and we all want to build our own extraction program completely independent of the others,
we need to pick different names, e.g. your first name.
In addition we do not want to read all FI bookings stored in this ERP system, the half a million of Company
Code 1000 (BUKRS) is enough.
5

Import the Target table

Same thing for the target database table. In your local SQL Server database, the datastore “TARGET_DS”
points to it, there is a table called “FI_LINE_ITEMS” we want to load with above Extractor‟s data. So we
need to create an object representing the table by doing a rightclick over the “Tables” node of the
“TARGET_DS” datastore.

New Batch Job

With that we have all we need to write our program. The first object we need is a “Job”, that is the only
object you can execute in DataServices. To create one there are multiple ways, the most common one is to
rightclick the project in the project area and select “New Batch Job”. Not a real-time job but a batch job.
6

New global variable

While this batch job is selected, you click on the “Variables” icon (or chose from the menu item Tools ->
Variables), rightclick the “Global Variables” node and create a new global variable named
“$G_LOAD_TYPE”, datatype is “varchar(7)” and the default value is set to “DELTA” within single
quotation marks.
This variable will be used to control if we want to execute the job as a fresh initial load ($G_LOAD_TYPE =
'FIRST') or as delta ($G_LOAD_TYPE = 'DELTA').

Conditional
Depending on the value of the global variable two different dataflows should be executed, so we add a
“Conditional” object from the right hand tool palette onto the job‟s workspace. One click on the icon of the
palette selects the object, the second click in the workspace places it there.
To go into the “Conditional” or any other object in the workspace you can either doubleclick its body or click
on the object name like a hyperlink.
7

Define Condition and two dataflows

Within the conditional object we have three areas, the if condition, the then and else workspace.
The if-condition is “$G_LOAD_TYPE = 'FIRST' “, so if the value of our global variable is FIRST, then the
upper workspace is executed. Inside this workspace we want a first dataflow to be created, again in the tool
palette on the right hand side click on the dataflow icon and by clicking a second time somewhere in the
workspace you place the dataflow object there. Name it somehow like “DF_Read_FI_Line_Items” and create
a second dataflow for the else-part of the conditional named e.g. “DF_Read_FI_Line_Items_Delta”.

Design the first dataflow logic

The first-load dataflow should read from the Extractor, select a few columns and load our target table. So we
drag and drop the Extractor from the Object Library into the dataflow‟s workspace, same thing for the
TARGET_DS table we imported before. As tables can not only be loaded but read from also, when dropping
the table to the workspace we get asked if we want to read from it or load it. We want to make it a target.
And the query is our first transformation object we use, it is so frequently used that it got placed on the tool
palette as well.
8

Configure the Reader

By double-click on the Extractor in the dataflow‟s workspace we can go into this object call‟s settings. The
only one we care about here is the “Initial Load” flag being set to Yes as this is the initial load dataflow. In
other words, whenever this dataflow is executed, we will ask the SAP Extractor to give us all the data and not
just the delta.

Configure Query
We close the Extractor window and back in the dataflow a doubleclick on the query allows us to configure
this transform. As we have connected the query object with a target table already, it is obvious for the
transform what columns to output – the columns of the target table – and therefore all the output columns are
present already. Our task is to map the left hand columns to the corresponding output columns by a simple
drag and drop operation from left to right one column at a time.
The Mapping tab below the input/output pane shows the mapping formula for the currently selected output
column in case a manual edit of the formula is required or comments should be added.
9

Configure Loader
Closing the Query window we can now open the dataflow‟s target object. It is a relational table we want to
load, so the loader presents all the options (see the Options tab) you would expect. Our dataflow should do a
fresh load, so it is a good idea to delete the entire table contents first, the “Delete data before loading” flag
should be checked.

Go back to the Conditional

We close the Loader window and the dataflow window and back in the conditional we can go into the second
dataflow.
10

Build a similar dataflow

The delta dataflow is quite similar for our simple case, so we drag and drop the source and the target into the
dataflow as before and add the query transform.

Configure Reader of the delta dataflow

In the reader of the delta dataflow we set the “Initial load” flag to no, which is all we have to change. (On a
side note, we could also use a global variable to parameterize the initial load flag but two dataflows are more
flexible)

Configure Query identical to before

The query is not different to before, we drag and drop the matching input column over the output columns to
specify the mapping formulas.
11

(Tip: When you range select multiple columns on the left side and drop them onto the Query node of the right
hand side, all columns of same name with no mapping get that column as input and columns of different
name are added to the output schema)

Configure Loader to not delete data

While in the initial load case we had the loader option set to delete before loading, in a delta load we disable
that. All records should be appended.
12

Execute the job

Close all windows to cleanup the tabs below the workspace, then rightclick the job in the project hierarchy
and select “Execute”. In the following dialog the execution options are good enough, we need to go to the
global variable tab however.

Change our global variable to ‘FIRST’

There we need to change the expression 'DELTA' to 'FIRST' as we want an initial load. Actually, as the
target table is empty and the Extractor was never called before a delta would result in the same, it would be a
delta load over multiple years instead of just the last day, which is logically the same.
13

Job is running
When you click on okay, the signal is sent to the jobserver – installed on your laptop as well – and we get the
log file created by the jobserver shown in Designer. In the trace log we can see the job step executed at the
moment, by clicking on the second icon at the top you can switch to the monitor and in case of an error the
third icon word turn red and we could view the error log then.

Monitor Log
The monitor log lets us see how many rows have been processed by each thread. The number of threads
created does not have to correspond with the number of objects in the dataflow, there is a fancy optimizer
trying to collapse and distribute all across the entire CPUs for maximum throughput.
Last time we executed the job, the dataflow took 180 seconds and loaded 413‟536 rows.
14

View the loaded data

Going back to the dataflow you can click on the target table‟s magnifying glass icon to view the data you
have just loaded. Remember, as we executed the initial load job, the data was deleted from that table before
loading it, we have set the “delete data before loading” option in the target table loader options. So this data
you see here is really what you have loaded and nothing else.

Delta just adds all records, Transforms are available

Our delta dataflow is not much different to the initial load one but in reality you would have to merge the
delta into the existing rows, not just append the rows. How that merge has to happen is completely up to you
and the target table definition. Either way, we have all the transforms available to accomplish the merge.
15

THERE IS NO EXTRACTOR RETURNING THE DATA WE WANT

Create Job, Workflow, DataFlow

We create another job, this time called “Job_Custom_Read_FI_Line_Items”, and with this job being opened,
we click on workflow icon in the right hand side tool palette to create a new workflow object in the
workspace. A workflow is just another grouping element, without you would have to place all 1000s of
dataflows into a job directly. Using a workflow you can organize that better and actually, it is a best practice
to encapsulate a dataflow inside a workflow.
Then open this workflow and create a new dataflow inside.

Create target table and ABAP dataflow

From the object library, the datastore tab, drag and drop the node “Template Tables” into the dataflow. A
template table is an object where DataServices does create the table for us, so effectively before executing the
insert/update statements against a target table, the loader will also issue an initial create-table-statement at job
execution time.
We want to read SAP data so very likely we want to write an ABAP program which collects this data. In case
of the Extractors somebody at SAP has written that ABAP program already, for our current task we want to
write the ABAP program. Therefore we need to create an ABAP program, we click on the ABAP dataflow
icon in the tool palette and place it on the dataflow‟s workspace to create a new. A dialog pops up…
16

ABAP dataflow settings

An ABAP program has certain restrictions and settings we need to specify. First of all, one ABAP program
can run on one SAP server only, we have to pick the one. And then when we generate an ABAP program as
text file it has a name and inside SAP it has a name and executed in background a spool job name. You can
set that to any name which starts with Z, e.g. we can all use “Z_EIM161_FI” for all three fields, the first with
the file extension .aba?
(Note: Although we all share the same SAP server, the names can be the same as we use a on-the-fly abap
generation method. For development that is fine but not for production, but we don‟t care about that yet.)

Import tables to be used

Inside our ABAP program we want to read data from the FI tables BSEG and BKPF, so we have to import
the metadata of these two tables into the datastore.
17

Build ABAP dataflow

A doubleclick on the ABAP dataflow opens it and we can drag „n drop the two tables into it. From the tool
palette we add a query object and the “Data Transport” object is the logical end of all ABAP dataflows,
representing how the data created and saved on the SAP server is moved to the DataServices engine.

Configure the Query’s join clause

Inside the query we select the “FROM” tab and specify the join condition. It is an inner join between BKPF
and BSEG and as SAP has the foreign key relationship between those two maintained, the join condition will
be populated automatically.
18

Pick columns to be read

You can now pick all columns you are interested in, including the Z… columns the Extractor did not include.
At least the BUKRS, BELNR, GJAHR, BUZEI columns have to be dragged over as they form the primary
key. If one of those would be missing and still marked as primary key a unique key violation will be thrown
by the target database.

Set the where-clause

We do not want to read millions of rows, hence go to the WHERE tab and drag the column GJAHR of the
BKPF table into the text area so that we do not have to type it ourselves, then complete the where clause to
BKPF.GJAHR = 2011
19

Set a filename for the Data Transport

We close the query window and open the Data Transport object instead, there the filename is set. As we all
use the same SAP ERP Server, it make sense to use different file names so we do not overwrite each other‟s
data.

Execute this job

And finally we can execute this job as well. It will connect to the SAP system, generate the ABAP source
code and using the SAP provided function module RFC_ABAP_INSTALL_AND_RUN inject this source
into the ERP system for execution. This will work as the ERP system we are using is a development system,
in production only uploaded and tested ABAPs will be available for execution as like with all other CTS
based transports.
20
© 2011 by SAP AG. All rights reserved.
SAP and the SAP logo are registered trademarks of SAP AG in Germany and other countries. Business Objects and the Business Objects logo are trademarks or
registered trademarks of Business Objects Software Ltd. Business Objects is an SAP company.Sybase and the Sybase logo are registered trademarks of Sybase Inc.
Sybase is an SAP company such products and services, if any. Nothing herein should be construed as constituting an additional warranty.