Blend Data in SAP Analytics Cloud Stories

[michael_melters]

Introduction

The SAP Analytics Cloud is a self-service solution, which means that story designers regularly face the task of displaying data from various sources (datasets or models) in a single visualization. In this blog, I would like to show how this can be implemented. The reason I wrote this blog is that blending for tables was gone in optimized stories. Since it is back now with the new table experience, becoming the default for table creation in SAC Stories, I would like present it to you. You might not have heard of it, as Blending was removed from SACS21 then, the course/learning journey is the most relevant for this topic. If you don’t want to read the whole blog you can register for the one our Live Session Exploring Data Blending in SAP Analytics Cloud Stories Exploring Data Blending in SAP Analytics Cloud Stories

The data presented in this blog is based on HANA live data sources, which are available in the Learning Hub's practice system, so everything demonstrated here can be tried out by yourself. Let’s start with it.

In reporting, data from different data sources is regularly displayed side by side so that the key figures from these data sources are visible at a glance. The data from these sources is often related to each other, as they use the same dimensions.

In both tables shown here, the same dimension Region is displayed. It makes sense to present this data in a single table so that the two key figures # of Orders and Sales Revenue are nicely placed next to each other and can be easily compared. Of course it would be dangerous to just make the two tables look like one table, because if data is changing only in only place and not in the other you might end up comparing the wrong figures. To get it straight we really need to tell SAC that a dimension in one model based on same or almost same members in the other model.

This is done via the feature "Link Dimensions." Here, the dimension that is available in both data sources needs to be linked.

It may also be desirable to create a calculation whose formula uses key figures from both data sources. In the following table, both dimensions are linked and a calculation has taken place. The column Sales Revenue per Order is exactly such a calculation. Another scenario would be to add an attribute which available only in one data source, but not in the other.

Basic Procedure

In order to display the data from both models in a single table, two steps must be carried out:

1. Linking the two models via one or more common dimensions
2. Adding a linked model in the table or chart that is supposed to display the data from both models.

To 1. Linking the two models via a common dimension

1. In the story's edit mode, the linking is done in the data area using the button
2. In the Link Dimensions window, you select the dimensions that have matching values. In my simple example, it is sufficient to set the dimension Region for both models, i.e., on the left and right, and confirm the settings with the Set button.

However, there may also be cases where two or more dimensions need to be linked, for example, if year and month are separate dimensions and the data should be displayed at the month level.

To 2. Adding a linked model in the table or chart that is supposed to display the data from both models.

If you now add a table or a chart in the story, you can now assign a linked model in the builder section of the designer, i.e., where the table structure is defined, for example:

Here I now select my linked model PACIFICA_SHIPPING_INFO:

Once the model is assigned, objects from both models can be included in the table structure. Here, for example, as seen in the row area:

The icon in front of PACIFICA_ORDER_FINANCE indicates that Region is a linked demension and the dot icon after Region indicates that Region is coming from the primary model.

The table with Sales Revenue from PACIFICA_ORDER_FINANCE and # of Orders from the model PACIFICA_SHIPPING_INFO now looks like this:

Finding the right (collection of) dimension(s)

It can happen that the data from two models cannot be properly combined, because the wrong dimension was linked. If there is a problem with the correctness of the displayed data for a dimension the visualization would not be rendered at all and therefore would look like this:

As you can see there is an indicator for the problem (!). If you click on it you will see this:

You will need to put Store dimension the chart.

In this case it is not sufficient to link on Region and therefore must be done at a more granular level, which is to link the Store dimension. Since here a Store is always assigned to exactly one Region, the link on Region can be removed without causing any limitations or incorrect values.

As soon as Store in the left model is linked with Store in the right model, this message disappears and instead another message appears. This time, it is the first of the two messages, which gives me a hint that the linking via Store is still not correct.

Sometimes visualization cannot be rendered, as there is a lack of uniqueness in the values. In those cases, where for example you have several stores with the same name, uniqueness is achieved by linking not via the description (default setting), but via the ID:

When linking dimension please consider:

• In the example presented, the data of two models were linked together, but it is also possible to link three or more models. However, this increases complexity, and it is naturally worth thinking about whether to integration of the data on the model level which means before creating the story. If would do so you would get a better reusability and a better performance in most cases.
• Two HANA Live data sources were linked together. Not all Live data sources can be linked, and here I refer to the help.sap.com. There are further restrictions related to the blending functionality, including displaying totals, Top N, sorting, creating variances based on combined calculations. A complete list of these restrictions can also be found in the official product documentation.
• If SAC reports that values are not correctly linked, it can sometimes be very tedious to find out exactly where the problem lies. Comparing cumulative calculations with and without linking can help identify which data records are affected.

Link Types

When a linked model has been stored in a table or chart, the link type can be specified. Here you can determine how to handle cases where members of the Region dimension exist in only one of the two models.

The link type has three options:
All primary data,
All data,
Intersecting  data only

Here is an example of how the different Link Type would behave. Hence the following table is primary data:

Region	Sales Revenue
APJ	$      146,326,784
China	$        63,750,577
EMEA North	$  1,077,635,472

And this table show the content of secondary data:

Region	# of Orders
APJ	1691
China	739
NA	3743

Then the result for the option All Primary data.would be what you see here:

Region	Sales Revenue	# of Orders
APJ	$      146,326,784	1691
China	$        63,750,577	739
EMEA North	$  1,077,635,472

Result for the option All data. Here, all regions are listed, regardless of whether they come from the primary or secondary data, and there are empty cells for both key figures:

Region	Sales Revenue	# of Orders
APJ	$              146,326,784.00	1691
China	$                63,750,577.00	739
EMEA North	$          1,077,635,472.00
NA		3744

Result for the option Intersecting data only. Here, only the regions that are present in both the primary and secondary data are listed. There are no empty cells here:

Region	Sales Revenue	# of Orders
APJ	$      146,326,784.00	1691
China	$        63,750,577.00	739

If you ever worked with relational databases you might already have noticed that link types are similar to joins in an SQL select script. All Primary data can be understood like a Left Outer Join, All data behaves like an full outer join and Intersecting data only like an Inner Join.

Where to find additional information about Blending

• Help Portal: https://help.sap.com/docs/SAP_ANALYTICS_CLOUD/00f68c2e08b941f081002fd3691d86a7/7b9defa34dc34c0881dee...
• Blending in Future Live Session:
  I cover blending functionality in a future Live Session. This session it covers also filtering and calculations when models are linked
  https://learning.sap.com/live-sessions/exploring-data-blending-in-stories-in-sap-analytics-cloud

Practice System in order to recreate the examples shown in the blog

There is so much to explore in the area of blending than I can possibly cover here, as mentioned in the beginning, all the examples here can be recreated in several SAP Learning Hub practice systems.

These are:

• Hands-on Practice for Exploring SAP Analytics Cloud (SACE11)

https://learning.sap.com/practice-systems/introduction-to-sap-analytics-cloud

• Hands-on Practice for SAP Analytics Cloud: Story Design (SACS21)

https://learning.sap.com/practice-systems/introduction-to-sap-analytics-cloud

• Hands-on Practice for SAP Analytics Cloud: Modeling and Data Transformation (SACM21)

https://learning.sap.com/practice-systems/sap-analytics-cloud-data-modeling

• The practice systems for planning courses like SACP23, and SACP34 can be used also without any restrictions.

The two models I used In my examples are:

• P00M_Pacifica_Order_Finance
• P00M_Pacifica_Shipping_Order

They can be found in the following folders:

• Public/SACE11/Content or
• Public/SACS21/Content

Relevance for SAP Certification C_SAC

Please keep in mind that, in a way, this topic is relevant for the certification Data Analyst Certification (C_SAC) also. Although blending might not be a topic will not be asked about in the certification as it is today, but blending functionality is sure is needed in environments where multiple datasources are used in single stories and it might come back into certification again in the future. Then you would be happy to know how the functionality works.