SAP Analytics Cloud – Blending information – Example
In the previous articles we configured the setup for being able to use the blending capabilities of SAP Analytics Cloud. So now, lets put this to work.
In our Example, we have a BW Query, which will return a set of measures by Customer or Product as shown below.
We also have a Excel spreadsheet with values by customer ID
In SAP Analytics Cloud we do have the model based on the SAP BW Query, so we can start building our Story with the first table showing the results from our BW Query as shown above.
We then use the “Data” option in the toolbar…
… and we list the data sources and we can then use the option to add a new data set to our story.
We then have the option to upload our Excel spreadsheet.
After the data is loaded we are presented with the raw information and we can configure our Dimensions and measures and then navigate back to our Story.
Now we have two data sets in our story and we need to setup the Linked Dimensions.
In the Linked Dimension configuration we can now define, how our two data sources are going to be joined.
In our example we see the dimensions from the BW Query on the left hand side and the columns from the Excel spreadsheet on the right hand side.
We select dimension Sold-to-Party from the BW Query and then we make sure that the linking will be done using the ID.
We then select the Customer ID from the Excel spreadsheet. You can also look at some sample data for each of the data sources, which comes in very helpful.
In our example we are using the dimension Sold-to-Party, which is a dimension type CHAR with a conversion routine in SAP BW. When looking at the sample data….
… we can see that SAP Analytics Cloud is not using the Key value for the linking, but instead uses the internal key value – which in this example comes with leading zeros for the full length of the dimension definition.
Please note, that the linking of the values is done on the internal key values in case of the BW Live connection, not the key value that you are seeing displayed in the table.
As of now (November 2019) you don’t have the option to choose between the different key values, nor do you have the option to create a formula on top of the value (for example to remove the zeros), so in our example we have to add the leading zeros to the customer number in our Excel spreadsheet.
In our example I included a second column with the adjusted Customer ID value, so I will use that for the linking.
After the linking has been defined I am navigating back to the table and open the builder panel.
Click on the option “Add Linked Models” and select the model based on the Excel spreadsheet.
After doing so, I can navigate to the columns part of the table in the Builder Panel and I am offered both models for selecting measures.
.. and my table then shows measures from both data sources.
Another important configuration as part of the blending, is the ability to choose the join type. When you navigate back to the Builder Panel, you can navigate to your linked model and configure the Link Type and the active linked dimensions.
Happy Blending …
SAP Analytics Cloud – Blending information (Part 1)
SAP Analytics Cloud – Blending information (Part 2)
SAP Analytics Cloud – Blending information (Part 3)
SAP Analytics Cloud – Blending Example
Thanks a lot for explaining how to merge to diferent data sets in SAC. Regretfully I can see that merging diferents models from diferent Data Sources in the same Story is not the strength from this tool. I think it lacks some options comparing to other BI tools, I hope SAP will improve this important feature in the future.
As usual your presentation is awesome.
Moreover Is it possible to implement the same in Analytics Designer by any chance?
Thanks for the approach. I must emphasize the opinions of Bose and Alejandro. Currently there are so many lacks of functionality that you cannot fully recommend SAC.
In SAC stories I tried to use a working linked model source in a geo chart. Later I realized that this is not possible - right?
Why is blending and or combining of different data sources not possible in Analytic Designer? I even cannot find that on the future roadmap.
My tries to blend a BW live query with a flatfile source in a story led alway to the error that something is wrong defined. 'The Blending is wrongly defined : Subquery 'Cube:t.C.C70RZ ... (long cryptic message)' returened an error. Please check the query definition.
It is a very simple xls file - only two columns
I cannot give that to business users.
Hey, very useful post, helped me a lot.
The only issue i encounter with this is that once i did the blending i can't add totals to the table.