Skip to Content
Technical Articles
Author's profile photo San Tran

Preliminary Currency Conversion in SAP Data Warehouse Cloud

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

Apply Currency Conversion with Graphical View

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: Wrap the Currency Tables (TCUR*) as Views

Currently there is a known security boundary, that prevents Stories created in SAP Analytics Cloud to access directly any table in SAP Data Warehouse Cloud. This applies also for Currency Tables such as TCURR, TCURV, TCURF and TCURX.

For the time being you have to wrap these Currency Tables as Views and declare them in the COVERT_CURRENCY function. Please create for that a new View V_TCURR by dragging and dropping the TCURR table into it.

Please repeat the same for the other tables, such as TCURX, TCURF, TCURV.

 

Step 3: 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 4: 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. Please make sure that you are using the Wrapper Views for the currency tables (V_TCURR, V_TCURV, V_TCURF, V_TCURX):
    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" => 'V_TCURX', 
        "CONFIGURATION_TABLE" => 'V_TCURV', 
        "PREFACTORS_TABLE" => 'V_TCURF', 
        "RATES_TABLE" => 'V_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.

 

Apply Currency Conversion with SQL View

Step 1: 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 SQL 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 2: Wrap the Currency Tables (TCUR*) as Views

Currently there is a known security boundary, that prevents Stories created in SAP Analytics Cloud to access directly any table in SAP Data Warehouse Cloud. This applies also for Currency Tables such as TCURR, TCURV, TCURF and TCURX.

For the time being you have to wrap these Currency Tables as Views and declare them in the COVERT_CURRENCY function. Please create for that a new View V_TCURR:

 

Step 3: Add new SQL View using the CONVERT_CURRENCY function:

  1. Add a new calculation into SQL script:
  2. Insert the Currency Conversion Function into the SQL Script. Please make sure that you are using the Wrapper Views for the currency tables (V_TCURR, V_TCURV, V_TCURF, V_TCURX):
    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" => 'V_TCURX', 
                 "CONFIGURATION_TABLE" => 'V_TCURV', 
                 "PREFACTORS_TABLE" => 'V_TCURF', 
                 "RATES_TABLE" => 'V_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.

 

Assigned tags

      8 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Chia-Yu Wu
      Chia-Yu Wu

      Hi San,

      great introduction to the currency conversion. This is actually what we needed!

      We have gone through exactly the steps you have shown us above, and were able to see the data in the graphical view in data preview mode.

      However, when i deployed the data and tried out in the Story it gave us the following failure code:

       

      Error: [Caught exception: exception 70000719: currency/unit conversion error: search table error: could not read currency precisions from table "H00"."ICSAPP"."TCURX"-insufficient privilege: Detailed into for this error can be found with guid 'F30982C52AE4AE4189404C5631D78ADE' at Authorization/insuffPrivi/impl/InsufficientPrivilegeThrow.cpp322 (SQLScript function ICS: AV_P2P_FINAL_1_]

       

      It is somehow kind of strange, since the data was already shown in the preview mode, and when we try to consume it in the story, only this calculated column has this problem.

       

      It would be great if you also have shared the same experience.

       

      Cheers,

       

      Chia-Yu

      Author's profile photo San Tran
      San Tran
      Blog Post Author
      with the recent delivered Version 2020.18.x of SAP Data Warehouse Cloud there is a new security implementation, which limits the SAP Analytics Cloud to access directly DWC tables (such as TCURX, TCURR, etc.).
      We are currently investigating this issue in the development and will provide soon a patch for that.
      In the meantime please follow the workaround:
      1. For each of the currency tables (especially for TCURR, TCURV, TCURX, TCURF) create a wrapper graphical view containing only one single table each. And expose this view for consumption e.g.
      2. In the Currency Conversion function please reference the wrapping views to the configuration tables, e.g.
      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" => 'V_TCURX', 
                   "CONFIGURATION_TABLE" => 'V_TCURV', 
                   "PREFACTORS_TABLE" => 'V_TCURF', 
                   "RATES_TABLE" => 'V_TCURR'
              )

      For more configuration please also refer to  SAP HANA SQL Reference Guide.

      Best regards,

      San

       

      Author's profile photo Chia-Yu Wu
      Chia-Yu Wu

      Hi San Tran

      Thank you very much! It worked.

      Have a nice day 🙂

      Chia-Yu

      Author's profile photo Chia-Yu Wu
      Chia-Yu Wu

      San Tran  Hello San,

      since the last update in DWC, we are not able to use the currency conversion function anymore .

      Our current DWC version is: 2020.22.63

      This is the code we currently have in the calculated column definition, which worked fine in the previous version:

      image.png

      When we tried to deploy the same view in the current DWC version, we get the following error message:

      image.png

      What is weird is that when we access the space from our external HANA DB explorer, the convert currency function works.

      It would be great if you could help us out with this issue.

       

      Thanks in advance ?

       

      Chia-Yu

       

      Author's profile photo San Tran
      San Tran
      Blog Post Author

      Hi Chia-Yu,

      Unfortunately I can not see the attached images.

      Can you please share it again?

      Thanks and regards,

      San

       

      Author's profile photo Chia-Yu Wu
      Chia-Yu Wu

      Hello San,

      thanks for your reply.

      Here again the screenshots:

       

      error%20code

      error code

      current code

       

      In any case the pngs are still not showing, I will reload them again ?

       

      Thank you and best regards,

       

      Chia-Yu

      Author's profile photo Paul Vatter
      Paul Vatter

      hi Chia-Yu

      it seems that you are using a graphical view, correct?

      In the SQL view we had this error message because we have used an alias (e.g. a."WRBTR") but not in the complete statement (e.g. just "WAERS" instead of a."WAERS").

      Looking at the statement above this seems not to be the case, but myabe an indication.

      Best regards

      Paul

      Author's profile photo Chia-Yu Wu
      Chia-Yu Wu

      Hello Paul,

      great thank you for your advice, we have added the alias in front of our columns, and now it is working again :).

      thank you very much, greetings from Germany,

       

      Chia-Yu