SAP Datasphere: Using Variable derivation for currency conversion measures within Analytic Model

[jaigupta]

 

Introduction

As part of this blog, I would like to introduce you to the new variable derivation feature in Analytic model within SAP Datasphere for currency conversion measures, as mentioned in the SAP Help documentation.

Before we start, you may be wondering what is variable derivation and why do we need this?

For example, let say we have a sales report where users would like to see Net Amount in the currency of their choice and it is preferred by them to choose a country to fetch the respective currency using a flexible lookup logic instead of them knowing the currency codes. For building the flexible lookup logic, an independent lookup entity having country-currency relationship can be used. Also, further in the lookup entity, extra features like language-dependent value help for countries can be added later, as well.

To realize the above scenario, 4 main components will be needed.

• Country variable - The one filled by the user, optionally with value help.
• Currency variable - It's configured to derive the value for itself by feeding a lookup entity with the value of the country variable.
• Lookup entity - Any table or view can be used here, but typically this would be a view with input parameter, either graphical or scripted in SQL or SQL script.
• Currency conversion measure - The measure that uses the currency variable value and converts a given currency amount, like sales value, to that very currency.

Fig a: User Workflow

The high-level user workflow would be as shown above and it provides the following advantages:

• Variable Reusability within Analytic Model: Same currency conversion variable can be used for other currency conversion measures within the same Analytic Model.
• Lookup Entity Reusability across Analytic Models: The lookup entity can be used in other Analytic Models as well; in case they have a similar need.
• Centralized Governance: If a country changes its currency like back when all countries in the Euro-zone gave up their own currencies, then such a change could be centrally managed in the lookup entity.
• Flexibility: Since, lookup entities can be freely developed as graphical or SQL views, the full power of SQL is at your disposal for derivations. Also, chaining of derivations can be performed.

Obviously, a country->currency lookup is very simplistic, but lookups can be lot more complex. Some scenarios are described below:

• Use the user identity from the session context to derive their cost center, subsidiary etc.
• Perform complex date calculations like start or end of current month or quarter, # of days since start of year etc.

At this point, only source variables and reference date variables can be derived, but SAP has some interesting enhancements in the works that will over time boost the power of variable derivations even more. Our vision is to extend the variable derivation for the other variable types, namely Restricted Measure & Filter variables, as well. And, further, introduce the same concept for Calculated Measures as well.

Please note that here we are just sharing the vision, hence not committing or liable to any new feature release. 

In the next sections, we will look at the inner workings of the country->currency lookup example.

Prerequisites

For Currency conversion, please make sure you have currency conversion tables available within your space as mentioned in the SAP Help Documentation.

Overview:

In our scenario, we will be using the derived source variables within Currency Conversion measures in Analytic Model to get the derived value for Target currency and also, for Reference date from two separate lookup entities, as shown below.

Fig b: Currency Conversion Measure

These values will be derived using a source variable in the Analytic Model where we have an option to use the Derive value, as shown below.

Fig c: Source Variable - Derive Value

1. Variable filled by – Dropdown to select between the option Manual Input and Derive value.
2. Lookup Entity – Using help icon, a relevant lookup entity can be chosen from the pop-up dialog. Any graphical or SQL view as well as any table in the space can be used. Most likely, the typical case will be a view with input parameters.
3. Result Column – This is the result or referenced column from the lookup entity. For now, only a single value can be returned.
4. Lookup entity Parameter Mappings – In the standard case of a lookup entity with input parameters, these can be mapped to other source variables (e.g. Country variable). They could theoretically also be set to a fixed value, but then derivation would always derive the same value, so this will rarely be useful.                                                                                                                         
5. Used In - Once the variable is used somewhere, like for example in a currency conversion measure, this usage is listed here.

So, from above, we can categorize the whole process within Analytic Model into below high-level steps.

 

 Step 1: Create Lookup Entities 

In this step, we will create two lookup entities - one for translating country to currency and another one for translating a human-readable time representation (today, yesterday, same day 5 years ago etc.) into a proper date.

1.1 First lookup entity, MCT_Lookup_Currency_From_Country, is a simple view containing 2 columns – Country and Currency.It stores data for the country->currency relationship like USD for US. 

Fig d: First Lookup Entity - MCT_Lookup_Currency_From_Country

Further, it has an input parameter Country for filtering the data on the Country column, as shown below.

Fig e: First Lookup Entity Country Filter

The source of this view is a simple table MCT Country, as shown below.

Fig f: First Lookup Entity Source 

1.2 Second look up entity, Date_Lookup_Entity, has an input parameter Date selection and is having the SQL view Date_Lookup as its source, as shown below.

Fig g: Second Lookup Entity - Date_Lookup_Entity

And this input parameter Date selection is further used to filter the ID column, as shown below.

Fig h: Second Lookup Entity Date Filter

Further, source SQL view Date_Lookup uses SQL Date and Time functions like CURRENT_DATE etc. to create a view with the date and time related data, as shown below.

Fig i: Second Lookup Entity SQL View

Moreover, this SQL view is created on the text table Date_Texts which has below simple structure and text data inserted into it using Data editor, as shown below.

Fig j: Second Lookup Entity Source table

 

Step 2: Create Source variables for User Input

In this step, we will create the variables to prompt the users for a country as well as for a readable date placeholder (like Today, Yesterday, Start of Month etc.).

2.1 Once the lookup entities are defined, please go to the respective Analytic Model and define the source variable, COUNTRY, as shown below.

Fig k: Source variable Country

2.2 Then, create another source variable, DATE_SELECTION, as shown below.

Fig l: Source variable Date

Step 3 – Create Source Variable for Variable Derivation

Now, we come to the core of this blog - the variable derivation. Here, we create two source variables and configure them such that they derive their values by help of the lookup entity and the prompt value provided by the end-user

3.1 Next, create derived source variables, CURRENCY_REF_DATE, as shown below.

 

Fig m: Derived Variable - CURRENCY_REF_DATE

Here, for the lookup entity use the view Date_Lookup_Entity, created in step 1, and result column as Date.

Then map the input parameter, Date selection, of the lookup entity to the source variable, DATE_SELECTION, created in the previous step, as shown below.

Fig n: Derived Variable Lookup Entity Parameter Mapping

Note that we could also create a new variable on the fly rather than reusing the variable DATE_SELECTION that we create earlier.

 3.2 Next, create the source variable, TARGET_CURRENCY_FROM_LOOKUP, to derive value for target currency, as shown below.

 

Fig o: Derived Variable - TARGET_CURRENCY_FROM_LOOKUP

Here, for lookup entity use the view MCT_Lookup_Currency_From_Country, created in step 1, and result column as Currency.

Then map the input parameter, Country, of the lookup entity to the source variable, COUNTRY, created in the previous step, as shown below.

Fig p: Derived Variable Lookup Entity Parameter Mapping

Step 4 – Create Currency Conversion Measure

By now, it's time to make use of our derived variable in a currency conversion measure.

4.1 First create a Currency Conversion measure, as shown below.

Fig q: Currency Conversion Measure

5.2 Next, in the measure definition for Target Currency and Reference Date choose the derived source variables created in previous step, as shown below.

Fig r: Choose Derived Variables

Once done save and deploy the Analytic Model.

 Step 5 – Analytic Model Preview

So, we're done building things and now, let's test things.

5.1  As a final step, please go to the Analytic Model Preview and enter the value for Input Parameters Country and Date selection as US and TODAY, respectively.

Fig s: Variable Screen

This will pass these values to the lookup entity and fetch the Target Currency and Reference Date to be used within the currency conversion measure to provide us the desired result.

Fig t: Derived Variable Lookup Entity Parameter Mapping

Summary

In this blog, I showed you how to use the currency conversion measures with the derived source variable.

Please make use of this cool new feature and let us know your valuable feedback.

Best wishes,
Jai Gupta

Find more information and related blog posts on the topic page for SAP Datasphere .