- SAP Managed Tags:
In the first scenario on the blending options of SAP Analytics Cloud, we took a very simple example by setting up the Linked Dimension - in our example based on Customer ID - and then simply showing the data together in a single chart.
In the next few steps, we will use the same data models, but we will add some calculations and also calculations across models into our overall resultset and take a closer look at how SAP Analytics Cloud is aggregating the data and when exactly and in which order those calculations are being calculated.
To quickly recap, our Data Source 1 shows the Revenue and the Discount (absolute) for 2017 per customer.
Our second data set shows the orders for the year 2018 per customer.
In addition we configured the Linked Dimension option based on the Customer ID.
In this example we would like to create a resultset, which shows:
and all this broken by customer.
So you can see we have calculations that are using elements from a single data source and we have calculations that include elements from both data sources.
I will outline in the next sections the individual steps that you don't necessary see in the product, but it will help explaining the result that you do see in the product. The steps outline how SAP Analytics Cloud would process the data, from retrieving the data, aggregating it, processing calculations, blending it, and then finally displaying the resultset.
In our example we have two data sources and for the information we would like to present in the final resultset, we do need information from both models.
In the first step SAP Analytics Cloud would then retrieve the required information from those two datasources, which would also include the fact, that the data is - per datasource - aggregated to the required level.
In our example that means we are retrieving the Revenue and Discount Value for 2017 per customer from Data Source 1 and the sum of the Order Values per Customer from Data Source 2.
In the second step SAP Analytics Cloud would then process any formulas on the data sources individually. So in other words Formulas that only involve elements from Data Source 1 and Formulas that only involve elements from Data Source 2.
For our example that would be the calculation of the Net Revenue 2017.
As Story Designer, all we have to do is to Add the calculation...
... and create the calculation as shown below.
So SAP Analytics Cloud would now have 2 resultsets like this.
In the 3rd step SAP Analytics Cloud would then blend the data. So the blending in our example is done based on the Customer ID.
So after the blending SAP Analytics Cloud would have a resultset like this:
Now after the blending is done, there could also be additional aggregation steps but for this example we don't have to worry about it (we will look at it in the next example) because we use the information on Customer level and that is what we are looking for in our final resultset.
After the blending of the two dataset has been done, SAP Analytics Cloud will then process any formulas that involve more than one data source. So in our example, that would be the combined value of 2017 and 2018.
As a Story Designer you can simply add the calculation like any other calculation.
The final table would then look like this.
You might have noticed that the calculations which involve both data sources are processed after the blend and aggregations, so there can be situations where the result that you see is not necessary matching what you would expect. We will take a closer look at such a scenario in the next blog.
Please remember that the outlined steps are just there for illustration purposes and as a Story Designer you would just
In the next few steps, we will use the same data models, but we will add some calculations and also calculations across models into our overall resultset and take a closer look at how SAP Analytics Cloud is aggregating the data and when exactly and in which order those calculations are being calculated.
To quickly recap, our Data Source 1 shows the Revenue and the Discount (absolute) for 2017 per customer.
Our second data set shows the orders for the year 2018 per customer.
In addition we configured the Linked Dimension option based on the Customer ID.
In this example we would like to create a resultset, which shows:
- Revenue for 2017
- Discount Value for 2017 (absolute)
- Net Revenue (Revenue - Discount) for 2017
- Order Value for 2018
- Order Value for 2017 + 2018
and all this broken by customer.
So you can see we have calculations that are using elements from a single data source and we have calculations that include elements from both data sources.
I will outline in the next sections the individual steps that you don't necessary see in the product, but it will help explaining the result that you do see in the product. The steps outline how SAP Analytics Cloud would process the data, from retrieving the data, aggregating it, processing calculations, blending it, and then finally displaying the resultset.
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 do need information from both models.
In the first step SAP Analytics Cloud would then retrieve the required information from those two datasources, which would also include the fact, that the data is - per datasource - aggregated to the required level.
In our example that means we are retrieving the Revenue and Discount Value for 2017 per customer from Data Source 1 and the sum of the Order Values per Customer from Data Source 2.
Step 2: Data Source specific Formulas
In the second step SAP Analytics Cloud would then process any formulas on the data sources individually. So in other words Formulas that only involve elements from Data Source 1 and Formulas that only involve elements from Data Source 2.
For our example that would be the calculation of the Net Revenue 2017.
As Story Designer, all we have to do is to Add the calculation...
... and create the calculation as shown below.
So SAP Analytics Cloud would now have 2 resultsets like this.
Step 3: Blending
In the 3rd step SAP Analytics Cloud would then blend the data. So the blending in our example is done based on the Customer ID.
So after the blending SAP Analytics Cloud would have a resultset like this:
Now after the blending is done, there could also be additional aggregation steps but for this example we don't have to worry about it (we will look at it in the next example) because we use the information on Customer level and that is what we are looking for in our final resultset.
Step 4: Formulas (Blended Calculations)
After the blending of the two dataset has been done, SAP Analytics Cloud will then process any formulas that involve more than one data source. So in our example, that would be the combined value of 2017 and 2018.
As a Story Designer you can simply add the calculation like any other calculation.
The final table would then look like this.
You might have noticed that the calculations which involve both data sources are processed after the blend and aggregations, so there can be situations where the result that you see is not necessary matching what you would expect. We will take a closer look at such a scenario in the next blog.
Please remember that the outlined steps are just there for illustration purposes and as a Story Designer you would just
- Add the two data sources
- Setup the Linked dimensions
- Add the calculations
- Add the final table
1 Comment
