Show TOC

Joining Data from Multiple SourcesLocate this document in the navigation structure

Use

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

Three types of join are supported:

  • Inner Join: Joins 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. First, the data from the first data source is read and then sorted according to the fields in the join condition. Then the data from the second data source is read in turn and sorted according to the fields in the join condition. Finally, the data from the two tables is merged.

If you have set the Process Data in Memory key figure ( Start of the navigation path Goto Next navigation step Performance Settings End of the navigation path), all the data is kept in the main memory when it is sorted. If this key figure is not set, the data is stored in temporary database tables.

Recommendation

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

For more information, see: Creating InfoSets

Activities
  1. Select the fields that you want to pass on to subsequent nodes using the indicator in front of each field.

  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 to the left is the data source that was first joined with the join node. To swap the left and right tables, select the appropriate option in the context menu.

Example

Data on customers 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 characteristic 0CUSTOMER with an inner join with a join condition via the field 0CUSTOMER. 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.