Skip to Content

In the previous scenarios (Part 1, Part 2) we reviewed the basic blending scenarios and we also reviewed the steps in SAP Analytics Cloud and when SAP Analytics Cloud is calculating formulas and aggregating the data.

This time we will look at a concrete example, where – depending on which data you choose – you will receive correct and “not so correct” information.

We will use the same two data source, which we have been using in the previous scenario.

What we are trying to show this time:

  • Revenue for 2017
  • Discount Value for 2017 (absolute)
  • Discount in % for 2017
  • Order Value 2018

 

… and we would like to show those values broken down by Region.

 

Step 1: Query Source Models for relevant information

In our example we have the two data sources and for the information we would like to present in the final resultset, we do need information from both models.

 

The first data set provides us with the Revenue and Discount values for the year 2018 and we do also have the Region information available in the first data set.

The second data set does not have the Region information, so we have to get that via Linked Dimensions from the first data set.

 

Step 2: Data Source specific Formulas

In the second step SAP Analytics Cloud will process any formulas on the data sources individually.

For our example, the formula that is being processed in this step is the Discount % calculation.

As Story Designer, all we have to do is to create the calculation as shown below.

 

and make sure the new calculation becomes part of our chart.

From a pure data visualization point of view, you wouldn’t create a chart like this, but in this example it is about showing the calculations and the effect of blending on the final results – so lets not worry about it for a second.

 

Step 3: Blending

In the 3rd step SAP Analytics Cloud will then perform the actual blend. As Story Designer all we have to do is, to configure the Linked Dimension (see Part 1 and Part 2) and add the Linked Model to our chart.

As soon as we have the Linked Model added to our chart, we can then also select measure Order Value from the second data set.

and our chart will then look like this…

 

So where is the problem now ?

You might have noticed that the Discount % value for California (as an example) went up from 21.5 % to now 89% – but the data has not changed really.

In short, the reason is based on the situation that the calculation of the Discount % value happens before the blend.

So lets look at the details and we will focus on California and Nevada as two regions from the data set.

 

When we created the chart showing the Revenue 2017 and the Discount Value 2017 and the Discount %, there was no need to use the configured blending, because all values are in data source 1.

So in that case SAP Analytics Cloud would just use the elements from Data Source 1 and would end up with a resultset as shown below and there is no need to involve the second data source.

 

Revenue 2017 Disocunt Value 2017 Discount %
California 599000 128900 22%
Nevada 509000 118000 23%

 

When we then add the Order Value from the second data set, we do need the configured linked dimension and our raw resultset would look like this

 

Revenue 2017 Disocunt Value 2017 Discount % Order Value
CUST001 Homerun Millwork Inc. California  $                          184,000  $                            14,700 8%  $                     43,000
CUST013 Homerun Millwork Inc. California  $                          126,000  $                            31,400 25%  $                     34,900
CUST007 Homerun Millwork Inc. California  $                          122,000  $                            29,300 24%  $                     40,000
CUST019 Homerun Millwork Inc. California  $                          167,000  $                            53,500 32%  $                     33,600
TOTAL  $                          599,000  $                          128,900 89%  $                   151,500
CUST008 BikeWorld Inc. Nevada  $                          122,000  $                            25,600 21%  $                     39,000
CUST002 BikeWorld Inc. Nevada  $                          109,000  $                            33,900 31%  $                     37,000
CUST014 BikeWorld Inc. Nevada  $                          144,000  $                            15,800 11%  $                     35,500
CUST020 BikeWorld Inc. Nevada  $                          134,000  $                            42,700 32%  $                     28,700
TOTAL  $                          509,000  $                          118,000 95%  $                   140,200

 

So you can probably now see the problem already. The calculation of the Discount % value is done before the blending happens and and then a simple SUM is applied to the calculation as part of the aggregation, so we are going to get these two total rows.

Revenue 2017 Disocunt Value 2017 Discount % Order Value
California  $                              599,000  $                               128,900 89%  $                              151,500
Nevada  $                              509,000  $                               118,000 95%  $                              140,200

 

SAP Analytics Cloud will use those linked dimension configurations that are required to create the final output. As shown in our example, this can lead sometimes to an unexpected result, because calculations are done on a per data set level and then potentially aggregated after the blending.

 

You can always see which dimensions are used (or even manually configure) when you go to the linked models of your chart. You have the ability to see and choose the Active Linked Dimensions

 

 

and you have the option to set the Link Type.

 

To report this post you need to login first.

2 Comments

You must be Logged on to comment or reply to a post.

Leave a Reply