SAP Analytics Cloud – Planning – Measure Based Model (Pre-converted actuals & Working with Multi Currency Conversion)

Authors: Sridhar Sundaram ; Kanchana Subbiah

The use case for this blog is where myself & Sridhar tried to achieve the following scenarios with the new measure-based Planning model in SAP Analytics Cloud

• Pre-converted actuals from SAP S/4 HANA
• Multiple currency planning and currency translation

In a real-life scenario, Users want to compare actuals vs plan numbers by different currencies

(especially group currency, company code currency and transaction currency) and more often the actuals need to be made available using the daily rates maintained in S/4 HANA TCURR table, while in planning we typically use average & closing rates.

In an Account type model, there is an option to load the pre-converted actuals by enabling them from model preferences.

With the new measure-based model, this option is not available. As we know already we could have measure based model, we can have multiple measures with or without an Supporting Account Dimension

Additionally, you could set the calculation priority / solve order using model preference settings which determines whether Account dimension settings (like aggregation etc.) or measure settings takes precedence

In this use case , where we need to load 3 different currency types for the same Account Dimension member , we could create them as 3 different measures (one for each currency type) and load the 3 different currency values from (ACDOCA) Universal Journal Entry table of S/4 HANA consumed directly as oData Services API in a SAP Analytics Cloud query where fields are mapped as

• TSL– Amount in Transaction Currency
• HSL– Amount in Company Code Currency
• KSL– Amount in Global Currency

SAP Analytics Cloud Query on S/4 HANA – API_JOURNALENTRYITEMBASIC_SRV

Map To Target

The steps described below, details out the above use case.

1. Create a Measure Based Model in SAP Analytics Cloud with the required Dimension and Measure as shown below

Measure Based Model

2. Create a Currency as one of the Generic dimensions and maintain the transaction currencies as members and enable Currency in Details – System Properties of the dimension

Currency Dimension

Dimension Settings

3. Create the Measure for Local Currency, Group Currency and Transaction Currency which is visible in the calculation’s workspace in the Model.

Currency Calculation

4. For the company Code currency, we use the Unit and Currencies from the Company Code Dimension, alternatively this can also be maintained as fixed if you do not want to use the company code currency similar to group currency

Measure Details

5. For the Group currency, we use the currency option as Fixed and default it to USD

Group Currency

6. For the Transaction currency, where the data will be entered in different currencies, we use the Unit and Currencies from the Currency Dimension

Unit and Currency

7. Save the Model, to test this let’s load some data in all three currency in Actuals in an excel file

Sample Data file

8. Empty rows can be either filed with 0.00 or selected as NULL when the data is loaded to SAP Analytics Cloud Model data management and prepare the xlsx file named Opex_Measure_Model_Test.xlsx as the Data load status does not complete if some measures have blank values .

Data File upload Status Successful

9. Now we have pre-converted actuals loaded successfully into SAP Analytics Cloud in the three different measures. We create a story to display the Actuals vs Plan numbers.

10. In this use case, we assume users have to enter the plan numbers by transaction currency. Users will plan against TX_currency measure against currency dimension member.

Input Form

For currency conversion for Plan numbers we need to Create a Data Action to execute the currency conversion in SAP Analytics Cloud.

11. In the measure-based model, an additional functionality of having currency conversion is available which is not available in Classic Account based Planning Model.

Data Action settings

12. In the Data Action step configure currency conversion between transaction currency to Group Currency and Company Code Currency

Conversion Rules

We have the option to trigger the data action for specfic version or by categories and for booking date or based on a fixed date.

Data Action

13. Make sure the currency rates are maintained in the SAP Analytics Cloud Currency table for the category and Txn currency to Group / Company Code Currency for which the conversion needs to be executed as shown

SAP Analytics Cloud Currency Table

14. Execute the Data Action entering the version as the Prompt for the Data Action Trigger in the story

15. The relevant Group and Local Currency conversions happened based on the value entered in Transaction Currency for EUR with the maintained currency rates in the SAP Analytics Cloud currency conversion table as shown in the yellow cells below and displayed against the Company Code Currency measure and group currency measure.

SAP Analytics Cloud Story

The story can be enhanced using various Input Control filters in the required dimensions like Currency, Measure etc. Also, we have the option of making the currency conversion dynamic for the plan numbers using conversion measures and allow users to enter the data in either of the currency as referred in the blog post: https://blogs.sap.com/2021/04/26/enter-plan-data-in-any-transactional-currency-in-the-new-model-of-sap-analytics-cloud/
For Variance reporting and persisting the currency conversion values, we could follow the above use case.
SAP Help:
Working with Currencies in a Model with Measures
https://help.sap.com/doc/00f68c2e08b941f081002fd3691d86a7/2021.7/en-US/92cab98504dc479fb19740a2715241bd.html