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
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.