This blog is SAP Analytics Cloud – Planning – Measure Based Model.
- 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
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
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
3. Create the Measure for Local Currency, Group Currency and Transaction Currency which is visible in the calculation’s workspace in the Model.
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
5. For the Group currency, we use the currency option as Fixed and default it to USD
6. For the Transaction currency, where the data will be entered in different currencies, we use the Unit and Currencies from the Currency Dimension
7. Save the Model, to test this let’s load some data in all three currency in Actuals in an excel 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 .
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.
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.
12. In the Data Action step configure currency conversion between transaction currency to Group Currency and Company Code Currency
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.
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
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.
The story can be enhanced using various Input Control filters in the required dimensions like Currency, Measure etc.
