SAP Knowledge Base Article - Public

2878398 - Left Outer Join and Inner Join Data Sources

Symptom

***Image/data in this KBA is from SAP internal systems, sample data, or demo systems. Any resemblance to real data is purely coincidental***

There is a business requirement for creating a new data source and it is needed to understand how do each type of join data source works,

Environment

SAP Cloud for Customer

SAP Business ByDesign

Resolution

A joined data source contains characteristic values that match from the data source A to a data source B (or more), that will include key figure values. This connection will be defined by a join condition set between two or more equal characteristics present in the data sources A and B that contains the same data type.

There are two types of joined data source, Left Outer Join and Inner Join.

Let's use the following tables as data sources examples in order to check the differences between new created Left Outer Join and Inner Join data sources.

data source1.JPGdata source 2.JPG

Left Outer Join

A left outer join selects all characteristics values from the anchor (left) data source and those characteristic values that match in the other joined data sources.

As the below example is showing, the join condition ignored all the data of the Employee Payroll Details which are not present in the Employee Master Data, such as the Employee IDs 25896 and 36963. Also, the Employee IDs 78945 and 21456 exist only in the Employee Master Data, so the values from Bank Name are showing as Not Assigned, since this characteristic (Bank Name) is being retrieved only from Employee Payroll Details, but the characteristics Employee ID, Employee Name and Manager have been maintained as they are part of the anchor data source (Employee Master Data).

data source 3.JPG

Inner Join

An Inner Join type will show only the data present in both data sources, else they will be removed from the final result.

Please take a look at the example below, see that the Employee IDs 78945, 21456, 25896 and 36963 are not being retrieved from both data sources, it happens because of the fact that the Employee IDs 78945 and 21456 are only in the Employee Master Data, while the Employee IDs 25896 and 36963 are present only in the Employee Payroll Details, hence they will be excluded by the inner join data source type.

data source 4.JPG

Keywords

datasource, data source, data, joindatasource, join, join types, inner join, left outer join , KBA , AP-RC-ANA , C4C, ByD, Travel: Analytics Tools & Frameworks , How To

Product

SAP Business ByDesign all versions ; SAP Cloud for Customer core applications all versions