Figure 1: The Analytical Model - Calculated and Restricted Measures (Source: SAP)
Introduction
The
SAP Datasphere Analytic Model Series is intended to provide you with useful guidance on how to utilize the new Analytic Model to leverage the potential of your data landscape. The Analytic Model allows for rich analytical modelling in a targeted modelling environment and will be THE go-to analytic consumption entity for SAP Datasphere.
This article is the
fourth of the blog post series and
introduces calculated and restricted measures. So far, the following blogs have been published:
The possibilities to work with Calculated and Restricted Measures is not new at all. However, this function was not available in the SAP Datasphere until now.
With the advent of SAP Datasphere, the Analytic Model has now been introduced that supports this useful feature. That’s why I want to briefly explain the feature in this blog post.
Calculated Measures
Calculated measures are calculations based on other, already existing measures – like source measures, restricted measures, or other calculated measures. This means that these other measures are calculated first and then the calculation is performed. That’s why calculated measures are often called “calculation after aggregation”: the aggregation of the other measures are done and only then does the calculation start. This order is important because it largely affects the overall result. It is normally always what users expect, but since there is also the feature of “calculated columns” in Graphical Views and SQL Views, it is important to fully understand the difference.
Comparison – Calculated Columns vs. Calculated Measures
To clearly spell out this difference, let’s take an extremely simple data set with sales data by product & category, showing quantity & value for both:
Figure 2: Sample Dataset (Source: Own Image)
Users might want to look at the overall quantity sold, overall revenue made or drill-down by product and category. All of this is entirely possibly and simple to do. But how about a slightly more advanced question? What if I try to calculated the average Price of my transactions?
An initial modelling approach could be to have a view that reads the data and that contains a calculated column
Price it which simply divides Revenue by Quantity. The calculation would happen on record level.
Figure 3: Calculated Column Price in Graphical View (Source: Own Image)
But would this really work in reporting? Would it still work for all possibly drill-down states, like when we look at the average price of transactions in each category?
In this case, the mentioned ordering of aggregations is really crucial, because otherwise the maths gets into our way: we obviously need to first calculate the value of all transactions, then the quantity of all transactions and only then build the ratio. If instead we just sum the prices of all individual transactions (i.e. build ratio first and then do the summing), we’d get a wrong number.
So let’s make another attempt at modelling this and turn this instead into a calculated measure (spoiler alert: this is the correct way; the modelling of price as a calculated column will read to wrong results and we can show it):
Figure 4: Calculated Measure Correct_Price in Analytical Model (Source: Own Image)
We now have defined two measures:
- Measure Price is a calculated column that was defined in the view that is feeding into the Analytic Model. In the jargon of the Analytic Model, this makes it a Source Measure
- Measure Correct_Price on the other hand is a Calculated Measure of the Analytic Model and feeds itself of the Source Measures Value & Quantity by taking their fraction.
Figure 5: Example Calculated Measure (Source: Own Image)
If we now look at their results of both measures in the data preview and do the drill-down by Product & Category, both calculations yield the same result:
Figure 6: Comparison of the source and calculated measure (Source: Own Image)
However, as soon as the aggregation level is changed, only
Correct_Price shows the correct value. The numbers for the price are wrong because they are calculated for each record and then aggregated for the current drilldown according to the aggregation settings of the source (here: sum).
Figure 7: Correct_Price and Price after aggregation (Source: Own Image)
As you see, this pattern of having to first aggregate and then apply calculations is very important to achieve consistent results. Many complex insights in the data can only be realized if the right ordering of aggregations is applied and calculated measures, esp. when stacked, allow you to do exactly this.
Restricted Measures
Restricted measures build on existing Measures but apply flexible filter expressions. Examples:
- SalesUSA is the aggregated value of the Measure Sales, but with the restriction to Country = US.
- ValueInControllingArea is aggregated on the source Measure Value but with die restriction on ControllingArea = 1 (Figure 😎
Restricted Measures are commonly used in SAP Business Warehouse & SAP Analytics Cloud, but could not be modeled in SAP Data Warehouse Cloud until now. In the Analytical Model, restricted measures can be modeled flexibly.
Figure 8: Example of a Restricted Measure in an Analytical Model (Source: Own Image)
There are different variants of Restricted Measures in the new SAP Datasphere Analytical Model:
- Filter statically, like in the screenshot above
- Filter dynamically on so-called Restricted Measure Variables. Restricted Measure Variables let users pick the member of a given dimension (e.g. “Product ABC” of dimension Product). This value can subsequently be used in the expression of a Restricted Measure Variable. We’ll learn more about variables in next week’s blog.
- Restricted measures can also build on other measures (“measure stacking”), not only on fact source measure.
Restricted measures might sound simple, but they are very relevant for many reporting cases. Let’s look at one account example in particular.
Restricted Measures in Accounting
With the help of Restricted Measures, data that is organized in the form of an “account model” can be turned into round to the form of a “measure model”. Now what do we mean by this?
Accounting data often uses the account model for their data. Accounting documents post values to accounts and that account becomes a dimension of the model. Values are all in a single measure, but it is qualified through its respective account.
The table below shows an example of this:
By defining a restricted measure per account and then aggregating it, this
account model can be turned into a
measure model
- Claims: Restricted Measure on Value where Account = 1410
- Liquid Assets: Restricted Measure on Value where Account = 1200
As a result, the entire chart of accounts can be represented in this way using a complex set of restricted measures.
Conclusion
This blog gave a brief introduction to calculated and restricted measures of the new Analytical Model of SAP Datasphere. SAP BW professionals will surely be happy about this feature, as it combines the new cloud-based tools with the established features.
Thanks for reading! I hope you find this post helpful. For any questions or feedback just leave a comment below this post.
Many thanks to Jan Fetzer for the collaboration on this blog post.
Further Links
Find more information and related blog posts on the
topic page for SAP Datasphere.
If you have questions about SAP Analytics Cloud you can submit them in the
Q&A area for SAP Datasphere in the SAP Community.