How to Merge Datasets in SAP Analytics Cloud
Business intelligence is all about analyzing performance. We use data to understand how our business is doing and to help inform better data-driven decisions for the future. Given any business, it is not so uncommon to have data clustered across multiple systems. For example, Actuals/Budget may come SAP and history from legacy. Below scenario explains how can we stitch the data together and create a story on top.
In the example below, we’re using SAP Analytics Cloud to track three different datasets — Actual Data, Actuals History and Budget. They all share some common dimensions i.e. Category, Country and Manager.
- Import all the datasets you want to use into SAP Analytics Cloud. In our example, we’ve imported three datasets. Actuals as a primary dataset.
Go to Designer tab, in the Builder click on the link as shown below to link the dimensions.
- Select the models that you would like to join. The linked dimensions do not need to be the same name, but the data they contain must be in the same format. As mentioned below, they both share common dimensions:
After linking the models, here is one sample chart for you
Note that there are several options to customize the link.
We can specify the join type to use the data.
Click on the ellipse, In the Link type there are three options.
1.All primary data: This is the default option, which displays all the data in the primary model and corresponding data in the Secondary model.
2.All data: This option allows all the data in the primary & secondary models to display.
3.The intersecting data only: this option allows only linked data to appear in the chart.
Note that the icon that displays beside the secondary model indicates the type of join that is currently selected.
We can control whether the system creates new members, which creates duplicates so that the data from each source is kept distinct, or Merges members, meaning all the data for the member is combined.
We can also choose which linked dimensions is driving the data in the chart, if more then one has been created, and also, we can edit the link.