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.

Combining Data

‘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:

Combining Data Sources in SAP Analytics Cloud Data Model

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.

Combining Data Sources in SAP Analytics Cloud Data Model

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.

Combining Data Sources in SAP Analytics Cloud Data Model

On clicking the icon, a pop up appears, asking to select the data source.

Combining Data Sources in SAP Analytics Cloud Data Model

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.

Combining Data Sources in SAP Analytics Cloud Data Model

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.

Combining Data Sources in SAP Analytics Cloud Data ModelSupplier 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.

Combining Data Sources in SAP Analytics Cloud Data Model

 

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.

Combining Data Sources in SAP Analytics Cloud Data Model

Sample data set can be viewed on the bottom of the pop-up window.

Combining Data Sources in SAP Analytics Cloud Data Model

 

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.

Combining Data Sources in SAP Analytics Cloud Data Model

 

Limitations:

  • 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.

Subscribe to our Newsletter

5920 Windhaven Pkwy
Plano TX 75093.

+1 888-227-2794

+1 972-232-2233

+1 888-227-7192

solutions@visualbi.com