Join Data from Multiple Sources 

Use

You use transformation  Join Data from Multiple Sources to join two data sources with one or more common fields (database function Join).

Three types of join are supported:

●      Inner Join: Join data records with identical values in the join fields.

●      Left Outer Join: Unlike the inner join, all data records in the left-hand table occur in the result, even if no corresponding data record is found in the right-hand table.

●      Full Outer Join: This join does more than the left outer join; all data records on the right-hand side occur in the result, even if there is no corresponding data record on the left-hand side.

Features

The join is performed in ABAP with a sort-merge-join logic. All data from the first data source is read and then sorted according to the fields in the join condition. All data from the second data source is then read and in turn is sorted according to the fields in the join condition. The data from the two tables is then merged.

If you have set the Process Data in Memory indicator (by choosing Goto →Performance Settings), all the data is retained in the main memory when it is sorted. If this indicator is not set, the data is stored in temporary database tables.

If, in the analysis process, you are joining large volumes of data from the master data or from DataStore objects, we recommend that you use an InfoSet. This improves performance, since fewer temporary tables are required and the join is executed in the database itself.

See InfoSets.

Activities

       1.      Use the indicator in front of each field to select the fields you want to pass on to subsequent nodes.

       2.      Create a join condition between the fields of the various data sources that are to be joined by drawing a connecting line between the fields using the mouse.

       3.      In the context menu of the connecting line, define the type of join (inner, left outer, or full outer join). The inner join, which is relevant in the majority of cases, is suggested.

The inner join is represented by a solid line, the left outer join by an arrow pointing to the right, and the full outer join by a broken line.

The table on the left-hand side is the data source that was joined first with the join node. To swap the left-hand and right-hand tables, select the appropriate option from the context menu.

Example

Customer data in an InfoProvider is to be enhanced with attributes from the master data for the Customer characteristic (0CUSTOMER). To do this, you join the InfoProvider and the 0CUSTOMER characteristic using an inner join with a join condition via the 0CUSTOMER field. The required attributes from the master data and the selected fields from the InfoProvider form the output structure of the node. At runtime, the master data from the characteristic is added to each record in the InfoProvider.