Modeling in SAP HANA Cloud

Implementing Currency Conversion

Objectives
After completing this lesson, you will be able to:

After completing this lesson, you will be able to:

  • Explain how to set up currency conversion

Currency Conversion in SAP HANA Cloud

When you build information models, the source data is often expressed in a single currency. Typically, for sales data, this would be the transaction currency at the date of sale.

For reporting purposes however, it is often necessary to convert the currencies. The purposes can include (but are not limited to) the following:

  • Corporate Reporting

    When using a global reporting currency in corporate reporting, all values should be displayed in the global reporting currency.

  • Regional Reporting

    For example, the US region might want to see European figures in USD.

  • P&L Reporting

    You might want to analyze the effects of currency gains and losses.

  • Accounting

    Data conversion is a strong requirement in multicurrency general ledger transactions.

Currency Conversion

Due to the permanent fluctuation of currency exchange rates, you have to define a smart conversion process and, in particular, define which date must be considered to define the conversion rate to apply.

This requirement is even stronger when the source or target currency is volatile.

SAP HANA Cloud supports currency conversion. The key elements provided by SAP HANA Cloud are:

  • A set of tables to store master data about currencies, exchange rate types, and the exchange rate values.

  • A semantic typeAmount with Currency which you set for the measure you want to convert.

  • Calculation view settings to define, for each measure, how the conversion should be processed (which rate and conversion dates should be applied, where to find the source and target currency).

Currency Conversion Tables

One of the key building blocks to enable data conversion in SAP HANA Cloud is a set of tables to define currencies and exchange rate types, and to store the conversion rates.

Required Conversion Tables

Table NameDescription
TCURCCurrency codes
TCURRExchange rates
TCURVExchange rate types for currency translation
TCURFConversion factors
TCURNQuotations
TCURXDecimal places in currencies

These tables exist in most SAP systems (in particular, SAP Business Suite and SAP S/4HANA).

To enable currency conversion in SAP HANA Cloud, these tables must be available in the SAP HANA Cloud database. The tables are not provided by SAP HANA Cloud by default and you must provide them and update them yourself using any of the data provisioning tools.

Implementing Conversion in Calculation Views

The figure, Currency Conversion Process Flow, depicts the process flow for currency conversion within a calculation view.

Setting the Semantic Type

By default, a measure has no semantic type.

When a measure contains an amount, and if you want to enable conversion, you need to change its semantic type to Amount with Currency Code. You can do this in the following ways:

  • In the Output pane of the Aggregation node, select an aggregated column and, in the General Properties pane, define the semantic type. You can also double-click the column.

    This can also be done on the Star Join node of a cube with star join calculation view.

  • Alternatively, when the currency conversion is implemented at the uppermost level of the calculation view, select the Semantics node, and choose Assign Semantics.

Note

The semantic type Amount with Currency Code can be used even when conversion is not actually used.

In this case, you only have to set the currency property to identify the currency (or currencies) in which the amounts are expressed.

Defining Currency Conversion Settings

After assigning the semantic type Amount with Currency Code, you can enable conversion and define the main parameters used for conversion.

Key Settings for Currency Conversion

Key Settings for Currency Conversion

SettingDescriptionOptions
ClientThe client (MANDT) to use to filter the TCUR* tables contentSession Client / Fixed Client Number / Column / Input parameter
Source CurrencyThe currency in which the amounts to convert are expressedFixed / Column
Target CurrencyThe currency in which the amounts must be convertedFixed / Column / Input Parameter
Exchange TypeThe type of rate used to convert amounts. Example: Spot rate, average rate...Fixed / Column / Input Parameter
Conversion DateThe date used to match an amount and the corresponding conversion rateFixed / Column / Input Parameter
Exchange Rate(optional) A column from the source data that contains the exchange rate to be used 
Data TypeThe data type of the converted measure (overrides the data type of the converted column)Example: Decimal (15,2)
GenerateIf selected, this option creates a column that indicates for each converted amount the (target) currency in which it is expressed. 
Upon FailureSpecifies the behavior if the conversion cannot be executed (for example, if the rateFail (a query on the view generates an error), NULL (the column is not populated), Ignore (keeps the source amount without converting it)
AccuracyDefines how the conversion must be performedIntermediate rounding / Retain all possible digits

It is important to carefully define how the exceptions must be handled when converting data. In addition, to reduce the risk of conversion failure, make sure that the currency conversion tables TCUR* in your SAP HANA system are updated on a regular basis, in particular in a side-by-side scenario where they should always be in sync with the data imported from the remote SAP system.

Note
The result currency column is never exposed to client tools. It is only available to other SAP HANA views, where it can be used in additional calculations.

Decimal Shift and Rounding

By default, the precision of all values is two digits in SAP ERP tables.

As some currencies require accuracy in value, decimal shift moves the decimal points according to the settings in the TCURX currency table. If you want to round the result value after currency conversion to the number of digits of the target currency, select the Rounding checkbox.

Decimal shift back is necessary if the results of the calculation views are interpreted in ABAP. The ABAP layer, by default, always executes the decimal shift. In such cases, decimal shift back helps avoid wrong numbers due to a double shift.

Reusing Currency Conversion Settings between Columns

From SAP HANA 2.0 SPS02, it is possible to reuse the currency conversion settings for other measures in the same node of a Calculation View. This reduces manual definition and allows for more consistency, by avoiding mistakes. The settings can be applied to several measures at the same time.

The currency conversion settings can be reused in two different ways:

  • Reference

    The settings defined in a measure are applied as is in the other measures that you select, and cannot be modified in the other measures.

    In other words, the settings will always remain consistent and only the source measure for currency conversion setting can be changed, thus impacting the ones that reference it.

  • Copy

    The setting defined in a measure is just copied to the other measures, but they are not bound to each other. The currency conversion settings of the other measures can be freely modified.

Using an Input Parameter for the Currency

The input parameter can also be described as a prompt, in that it asks the user what currency to use.

If you want to define the currency at runtime, when the view is executed, you can create an input parameter.

VARCHAR (5) is the way that the currency code is defined in the TCUR* tables, so to be consistent, we recommend that you define the input parameter with the same data type.

Implement Currency Conversion in Calculation Views

Implement Currency Conversion in Calculation Views

Save progress to your learning plan by logging in or creating an account