There are two ways for combining data sources in SAP Analytics Cloud. One is through Data Blending, and the other is to combine data sources at the model level at the time of model creation. In this blog, we will look at how to combine data from two different sources at the model level and the types of data sources that can be combined.
‘Combine Data’ option is available while creating a new model. This allows you to combine two to five data sources at the model level. The types of data sources that can be combined are shown in the table below:
Consider a scenario where you have two different excel files as source files – the first file contains Supplier and Spend information and the second file contains a ‘Y/N’ flag for each supplier to indicate if they are preferred suppliers. Create a model by importing the first file as a data source, make necessary changes to the dimensions and measures and then combine this data with the second file.
Now let’s look at the steps to combine the 2 data sources at the model level:
1. Create the Data Model
First, you must import one of the source files, say ‘Spend File’, and create a new data model. You can do this by going to
Main Menu -> Create -> Model -> Import a file from your computer -> Select Source File -> Import
Select the required file.
2. Combine Data Option
‘Combine Data’ icon is present under Transformations menu as shown in the image below.
On clicking the icon, a pop up appears, asking to select the data source.
You can either get data from a file or directly from a data source. In this scenario, the second data source is also from a flat file.
Select the source file and click on Import.
3. Combine Settings
Once the file has been imported, the ‘Combine Settings’ pop up window appears. Here you can choose a column for combining the two data sources.
Supplier is the common dimension in both the data sources, to make a join. You can choose only one column for combining data. Once the column has been dragged and dropped in ‘Combine Column’ for both the data sources, a ‘Combine Preview’ is shown on the right-side pane. This provides a summary of the number of records that have been accepted, duplicated or omitted during the join.
4. Join Type
On the top end of the pop-up window, there is a drop-down providing two options:
- All primary data – combined data contains all primary data values (like left outer join)
- Intersecting data only – only data that has matched the join condition is present in the result set (like inner join)
By default, ‘All primary data’ is selected.
Sample data set can be viewed on the bottom of the pop-up window.
5. Combined Model
On finishing all the above steps, click on the ‘Combine’ button to join the data sources. Now you can see that the columns from both the files have been combined and is available in the model.
- The maximum number of combined cells is 30 million.
- The maximum number of combined rows is 1 million.
- The maximum number of combined columns is 1 hundred.
- For a given data set, a maximum of five ‘Combine Data’ processes can be run.
- You cannot combine two data sets using a calculated column.
- You cannot combine two data sets in a story.
Combining data sources at the model level has its own benefits. It allows you to reuse the combined data model for various stories, saving you the time of blending the models at story level every time you create a new story with the same data. This method also allows you to use the combined data in analytical applications where the only option to blend data is by scripting.
Reach out to us here today if you are interested to evaluate if SAP Analytics Cloud is right for you.