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:
... and we would like to show those values broken down by Region.
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.
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.
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...
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.
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
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.
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.
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.
- SAP Managed Tags:
3 Comments
