Wednesday, 9 January 2019

Implementing Dynamic Currency Conversion Using Calculated Column Using Semantic Type Amount with Currency Code in SAP HANA

Objective


Currency Conversion is an important aspect of any data modelling and reporting software. SAP HANA software provides functionality for Currency conversion. Below we will see step by step process on how currency conversion takes place in SAP HANA. This article is completely based on my learning experience. I hope, this article will be useful for those consultants who are new to SAP HANA.

Prerequisite

1. Make sure Currency Conversion related tables (TCUR*) are present in your schema and has data.

SAP HANA Study Material, SAP HANA Guides, SAP HANA Tutorial and Material, SAP HANA Learning, SAP HANA Live

2. Make sure Sales order header and item tables VBAK and VBAP have been loaded into your HANA system.

TABLE NAME DESCRIPTION 
VBAK Sales Document: Header Data

Step by Step Implementation


1. Let us start by creating a “Calculation View” in the HANA system based on VBAK table.

SAP HANA Study Material, SAP HANA Guides, SAP HANA Tutorial and Material, SAP HANA Learning, SAP HANA Live

2.  Provide name and label. Make sure to check the “With Star Join” option.

Note: This can be done for “non-star join” type of calculation view or with an “Analytical View” as well. But for this example, we are taking this as “star join”

SAP HANA Study Material, SAP HANA Guides, SAP HANA Tutorial and Material, SAP HANA Learning, SAP HANA Live

By default, you will see that there is a “Star Join” node and a “Semantics” node which will appear.

3. Now, insert a “Projection” Click on the plus icon to add the table in the data foundation node and join it to the star join node. After that click on “Add Objects” in the projection to add a table.

SAP HANA Study Material, SAP HANA Guides, SAP HANA Tutorial and Material, SAP HANA Learning, SAP HANA Live

Select Table VBAK and click OK

SAP HANA Study Material, SAP HANA Guides, SAP HANA Tutorial and Material, SAP HANA Learning, SAP HANA Live

Select the fields in the VBAK table as shown below:

SAP HANA Study Material, SAP HANA Guides, SAP HANA Tutorial and Material, SAP HANA Learning, SAP HANA Live

Apply a filter on the field WAERK to only bring “EUR”

SAP HANA Study Material, SAP HANA Guides, SAP HANA Tutorial and Material, SAP HANA Learning, SAP HANA Live

Select the value EUR and then click OK

SAP HANA Study Material, SAP HANA Guides, SAP HANA Tutorial and Material, SAP HANA Learning, SAP HANA Live

4. Now, we need to create few “Input parameter”: first.

Create a new “Input Parameters” in the projection node

SAP HANA Study Material, SAP HANA Guides, SAP HANA Tutorial and Material, SAP HANA Learning, SAP HANA Live

Provide the necessary fields like “Name” and “Level” (DATE).

Select the “Parameter Type” as Direct

Select the “Default Value” as “Expression” and give the “Value” as format(now(),‘YYYYMMDD’)

This syntax will produce the current date when executed.

Also, select both the “Symantec Type” and “Data Type” as Date

After this Click OK

This will help us pick the date on which we want to get currency conversion.

SAP HANA Study Material, SAP HANA Guides, SAP HANA Tutorial and Material, SAP HANA Learning, SAP HANA Live

5. Again create another “Input Parameter”. This input parameter is for the “Target” currency.

SAP HANA Study Material, SAP HANA Guides, SAP HANA Tutorial and Material, SAP HANA Learning, SAP HANA Live

Provide the necessary details like name and label (ZCURRENCY)

Select Parameter types as Column

Now, in the “Column” section select “View/Table for value help” as the current view only and the “reference column” as WAERK

Click OK

SAP HANA Study Material, SAP HANA Guides, SAP HANA Tutorial and Material, SAP HANA Learning, SAP HANA Live

6. Now, create a new “Calculated Column” on the same “Star Join” node

SAP HANA Study Material, SAP HANA Guides, SAP HANA Tutorial and Material, SAP HANA Learning, SAP HANA Live

Provide the necessary fields like “Name” and “Level”.

Make sure to provide the “Data Type” as DECIMAL and “Column Type” as MEASURE.

Give the syntax and validate it

SAP HANA Study Material, SAP HANA Guides, SAP HANA Tutorial and Material, SAP HANA Learning, SAP HANA Live

After that click on “Semantics” tab and Select semantic type as “Amount with Currency Code”

SAP HANA Study Material, SAP HANA Guides, SAP HANA Tutorial and Material, SAP HANA Learning, SAP HANA Live

And then click on the “Conversion” check box and then select the “Currency” as “<calculated column>_CURRENCY”. In this case, this will be “TARGET_CURRENCY_CURRENCY”.

After that click on the “Schema for currency conversion” and select the appropriate schema.

SAP HANA Study Material, SAP HANA Guides, SAP HANA Tutorial and Material, SAP HANA Learning, SAP HANA Live

Select the schema under which the base tables exist and click OK

SAP HANA Study Material, SAP HANA Guides, SAP HANA Tutorial and Material, SAP HANA Learning, SAP HANA Live

Provide all details as shown and then click OK

SAP HANA Study Material, SAP HANA Guides, SAP HANA Tutorial and Material, SAP HANA Learning, SAP HANA Live

Source Currency: This we have to select the column from the source VBAK table.

Target Currency: This we have to select the “ZCURRENCY” input parameter, as this is something which the user will give.

Exchange Type: This we select that as “M” as this will ensure that the daily transaction rates are getting captured.

Conversion Date: This we have to select the “DATE” input parameter. This will give the user the flexibility to choose the date in which he/she wants to see the exchange rate.

Exchange Rate: This we again need to select the appropriate column in which the exchange rates are being stored. In this case, it is VKORG

7. Now, we click on semantics and check all the column are reflecting or not and then validate and Activate the calculation view

SAP HANA Study Material, SAP HANA Guides, SAP HANA Tutorial and Material, SAP HANA Learning, SAP HANA Live

SAP HANA Study Material, SAP HANA Guides, SAP HANA Tutorial and Material, SAP HANA Learning, SAP HANA Live

Execution:

Click on data preview.

SAP HANA Study Material, SAP HANA Guides, SAP HANA Tutorial and Material, SAP HANA Learning, SAP HANA Live

Provide values to the two “Input Parameter” as created.

Here we are changing the currency from EUR to USD, so we are giving the ZCURRENCY as “USD” and the conversion will happen based on the date range given.

Click OK.

SAP HANA Study Material, SAP HANA Guides, SAP HANA Tutorial and Material, SAP HANA Learning, SAP HANA Live

Click on raw data to view the entire data.

SAP HANA Study Material, SAP HANA Guides, SAP HANA Tutorial and Material, SAP HANA Learning, SAP HANA Live

Here we can see the output values with TARGET_CURRENY column.

SAP HANA Study Material, SAP HANA Guides, SAP HANA Tutorial and Material, SAP HANA Learning, SAP HANA Live

No comments:

Post a Comment