Hero Backgroud Elements 2
Reading Time: 12 Min

SAP Analytics Cloud Feature Highlight – Blending – Scenario 3

Learning Article
  • In the previous posts (Part 1 and Part 2), we reviewed basic blending scenarios. We also discussed the steps in SAP Analytics Cloud along with calculating formulas and aggregating the data.

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

    We continue using the same data sources that we have been using in the previous scenarios.

    In this scenario, we show:

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

    In addition, we also show these values broken down by Region.

    Step 1: Query source models for relevant information

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

    The first data set provides us with the Revenue and Discount values for 2018, and we 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 processes any formulas on the data sources individually.

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

    As story designers, all we have to do is create the calculation as shown below.

    Next, we must ensure the new calculation becomes part of our chart.

    From a pure data visualization standpoint, you wouldn’t create a chart like this. However, in this example, it’s about showing the calculations and the effect of blending on the final results.

    Step 3: Blending

    In the 3rd step, SAP Analytics Cloud performs the actual blend. As story designers, we need 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.

    Our chart will then look like this:

    So where’s the problem now?

    As you may have noticed, the Discount % value for California (as an example) went up from 21.5 % to 89% – but the data has not changed.

    The reason for this discrepancy is based on the calculation of the Discount % value before the blend. So lets look at the details and focus on California and Nevada as the two regions from the data set.

    When we created the chart, we showed three values:

    • Revenue 2017
    • Discount Value 2017
    • Discount %

    Since all values are in data source 1, there was no need to use the configured blending. In that case, SAP Analytics Cloud uses the elements from data source 1 and ends up with a resultset as shown below. There is no need to involve the second data source.

    However, when we add the Order Value from the second data set, we need the configured linked dimension and our raw resultset looks like this:

    You can likely see the problem. The calculation of the Discount % value is done before the blending happens and then a simple SUM is applied to the calculation as part of the aggregation. The result is we end up with these two total rows.

    SAP Analytics Cloud uses 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 configured) when you go to the linked models of your chart. You have the ability to see and choose the Active Linked Dimensions

    You also have the option to set the Link Type.

    Additional Resources

    Author: Ingo Hilgefort

    Ingo Hilgefort is the Chief Product Expert for SAP Analytics Cloud based in Vancouver, Canada. Originally from Walldorf, Germany, Ingo moved to Vancouver in 2004, but has been with SAP for over 15 years. When not at work, he enjoys spending time with his wife and three dogs.