In SAP Data Warehouse we support the connection with SAP systems such as S/4Hana and BW/4Hana. These sources come with currency conversion function out of the box. This function is especially required for reporting and analytical purposes, to consolidate and aggregate amounts and values associated with different currencies.

Customers of SAP Data Warehouse expect same currency conversion capability, to assure consistency of results between the various SAP systems.

In this blog I want to show you, how to apply preliminary Currency Conversion in SAP Data Warehouse Cloud in three steps:

• Replicate the Currency Conversion tables from an SAP Source system (based on ABAP and SAP HANA)
• Create a new View
• Add new calculate column using the Currency Conversion function

Important Note

Please note, that this solution is only a preliminary solution for applying Currency Conversion in SAP Data Warehouse Cloud. It should be used for testing purposes and non-productive environment only !

Known limitations:

• Only applicable on Graphical View
• No UX support, but using SQL script in the Calculated Column
• No input parameter support –> parameterization must be statically defined in the script
• Potential performance issues, due to missing optimization

Documentation on currency conversion

More information about currency conversion can be found on the SAP help site:

https://help.sap.com/viewer/7c78579ce9b14a669c1f3295b0d8ca16/Cloud/en-US/d22d746ed2951014bb7fb0114ffdaf96.html

Recording

https://sapvideoa35699dc5.hana.ondemand.com/?entry_id=1_eortnemj

Step 1: Replicate the Currency Conversion Tables

1. Make sure you have established a valid ABAP Connection in the Space Management of SAP Data Warehouse Cloud:
2. In the Data Builder create a (dummy) Graphical View:
3. Drag and Drop the Currency Tables from the ABAP Source into the Canvas. In the next popup dialog, please click on ‘Import and Deploy’ button. The required tables are the following:
   

   ABAP Table Name	Description
   TCURV	Configuration Table
   TCURX	Precisions Table
   TCURN	Notation Table
   TCURR	Rates Table
   TCURF	Prefactors Table
   TCURC	Currencies Table

   More details about the required tables are described in the SAP HANA SQL Reference Guide:
   https://help.sap.com/viewer/7c78579ce9b14a669c1f3295b0d8ca16/Cloud/en-US/d22d746ed2951014bb7fb0114ffdaf96.html

    

   

   

4. Go to the Data Integration Monitor to replicate the content/data of the Currency Conversion tables into SAP Data Warehouse Cloud:

Step 2: Create a new Graphical View

1. Create a new Graphical View from the Data Builder
2. Add the ABAP Tables into the canvas, where the currency conversion shall be applied on. In my example it’s the SFLIGHT demo table:

Step 3: Add new calculated column using the Currency Conversion function

1. Add a new calculation node and column into the graph:
2. Enter the Currency Conversion Function into the Expression:

   CONVERT_CURRENCY(
   	"AMOUNT" => "PRICE", 
   	"SOURCE_UNIT" => "CURRENCY", 
   	"TARGET_UNIT" => 'EUR', 
   	"CONVERSION_TYPE" => 'M', 
   	"REFERENCE_DATE" => CURRENT_DATE, 
   	"CLIENT" => '002', 
   	"SCHEMA" => 'DEMO', 
   	"ERROR_HANDLING" => 'set_to_null', 
   	"STEPS" => 'shift,convert,round',
       "PRECISIONS_TABLE" => 'TCURX', 
       "CONFIGURATION_TABLE" => 'TCURV', 
       "PREFACTORS_TABLE" => 'TCURF', 
       "RATES_TABLE" => 'TCURR'
   )

3. Adjust basic parameters settings of the Currency Conversion Function:
   • AMOUNT: Column containing the values to be converted
   • SOURCE_UNIT: Source currency column
   • TARGET_UNIT: Target currency
   • REFERENCE_DATE: Reference date or dynamic variable CURRENT_DATE
   • CLIENT: ABAP Client
   • SCHEMA: Schema or Space name
   • CONFIGURATION_TABLE: The table identifier of the conversion type configuration (default TCURV)
   • PRECISIONS_TABLE: The table identifier of the precision table (default TCURX)
   • RATES_TABLE: The table identifier of the conversion rates table (default TCURR)
   • PREFACTORS_TABLE: The table identifier of the pre-factors table (default TCURF)
4. More information on the currency conversion can be found on the SAP HANA SQL Reference Guide.

Step 2b: Create a new SQL View

alternatively you can also a SQL View instead, if you prefer to define your view via SQL Scripting.

1. Create a new Graphical View from the Data Builder
2. Add the ABAP Tables into the canvas, where the currency conversion shall be applied on. In my example it’s the SFLIGHT demo table:

Step 3b: Add new calculated column using the Currency Conversion function in SQL

1. Add a new calculation into SQL script:
2. Insert the Currency Conversion Function into the SQL Script:

   SELECT "MANDT",
   	"CARRID",
   	"CONNID",
   	"FLDATE",
   	"PRICE",
   	"CURRENCY",
   	"PLANETYPE",
   	"SEATSMAX",
   	"SEATSOCC",
   	"PAYMENTSUM",
   	"SEATSMAX_B",
   	"SEATSOCC_B",
   	"SEATSMAX_F",
   	"SEATSOCC_F",
   	CONVERT_CURRENCY(
                "AMOUNT" => "PRICE", 
                "SOURCE_UNIT" => "CURRENCY", 
                "TARGET_UNIT" => 'USD', 
                "CONVERSION_TYPE" => 'M', 
                "REFERENCE_DATE" => CURRENT_DATE, 
                "CLIENT" => '002', 
                "SCHEMA" => 'ZST_TCUR', 
                "ERROR_HANDLING" => 'set_to_null', 
                "STEPS" => 'shift,convert,round', 
                "PRECISIONS_TABLE" => 'TCURX', 
                "CONFIGURATION_TABLE" => 'TCURV', 
                "PREFACTORS_TABLE" => 'TCURF', 
                "RATES_TABLE" => 'TCURR'
           ) AS "PRICE_CONVERTED",
   	'USD' AS "TARGET_CURRENCY"
   FROM "SFLIGHT"

    

3. Adjust basic parameters settings of the Currency Conversion Function:
   • AMOUNT: Column containing the values to be converted
   • SOURCE_UNIT: Source currency column
   • TARGET_UNIT: Target currency
   • REFERENCE_DATE: Reference date or dynamic variable CURRENT_DATE
   • CLIENT: ABAP Client
   • SCHEMA: Schema or Space name
   • CONFIGURATION_TABLE: The table identifier of the conversion type configuration (default TCURV)
   • PRECISIONS_TABLE: The table identifier of the precision table (default TCURX)
   • RATES_TABLE: The table identifier of the conversion rates table (default TCURR)
   • PREFACTORS_TABLE: The table identifier of the pre-factors table (default TCURF)
4. More information on the currency conversion can be found on the SAP HANA SQL Reference Guide.

With that I hope I could give you a preliminary idea on how Currency Conversion could be applied in SAP Data Warehouse Cloud.

Looking forward to posting soon the final Currency Conversion solution with a much better user experience and simpler configuration.

Original Article:
https://blogs.sap.com/2020/05/12/preliminary-currency-conversion-in-sap-data-warehouse-cloud/