Understanding Data Sources
I am writing this blog to serve as central information for Data Sources. Here you will find the types of data sources that exist in your system and how they technically work.
What is a Data Source?
Types of Data Sources
- It combines the data of two or more data sources, that semantically match each other. As result, it will pull out all data of involved data sources
- It is a data source uploaded from an external source into the solution for reporting purposes.
Please note that SAP has increased the quantity of fields/characteristics to be uploaded into the system, as part of 1802 release it is possible to upload 150 fields/characteristics instead of 20
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, you can see the difference between them as below. We are going to use as example the following data:
Now that we have an example, let’s see how the join condition works in both data source types.
Observation: here the field in the join condition is the Employee ID.
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).
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.
Did You Know?
A Primary Key contains a unique value present for each row in your data source. It is the main identifier of each field value. In the given example, we can say that Employee ID, from Employee Master Data and Employee Payroll Details, is a Primary Key, because it will never be repeated.
smart tip: the most consistent join conditions are made with Unique Identifier IDs (UUID) fields. 🙂
It is defined by the values that a field can take, the programming language used, or the operations that can be performed on it.
If the data type of Employee ID from Employee Master Data was a XYZ data type and the Employee ID from Employee Payroll Details was a ZYX data type, the join condition would not work. The data type is available in the data source documentation.
Pay attention to the warning messages when you create a data source. You can also make use of the Check Consistency button. 😉
This can certainly cause an inconsistency in your joined data source! :O
Leading Zeros are zeros attached to the beginning of a number when a value is stored in the data source, they are present when the field has a fixed length, but the stored value is not large enough to fill the number of digits.
Please see the example below:
As you can see, they are apparently equal, but they are stored with a different amount of characters. The join condition does not consider how they are shown in the UI, it will consider the stored data, so the data source results will shown as Not Assigned (for Left Outer Join type) or you will get the warning message No Available Data to Display (for Inner Join type).