USD to EUR... KM to CM...
These are just simple examples of the "FROM" to "TO" Currency or Unit Conversion scenarios that you can calculate easily via SAP Profitability and Performance Management Cloud (SAP PaPM Cloud) 's Conversion Function. Since conversion is usually an important step in your calculation logic, let me guide you in understanding better how you can play around with the settings and even integrate to external rates (coming from SAP ECC, SAP S4HANA and so on... )
Let us start then with the SAP help documentation.
After visiting the Conversion Function Documentation, some of you probably noticed this highlighted note:
Note
SAP standard tables such as Exchange Rate and Conversion Factors are not readily available in the cloud. As a workaround, you can connect the HANA Cloud to your ERP or S/4HANA system and access the conversion and unit tables as virtual table. Alternatively, you can create all tables manually and upload data of the standard table.
In human language, this note means that unlike in SAP PaPM on-premise that the TCUR and T006 tables are readily available through SAP ERP or SAP S4HANA, it is not then the case for SAP PaPM Cloud. Reason is, since SAP PaPM Cloud is a standalone subscription based solution (SAP ERP or SAP S4HANA are not needed) in the cloud, then these tables are not automatically available for consumption.
Because of such, a one time setup of TCUR and T006 tables is mandatory to use and maximize SAP PaPM Cloud's Conversion Function.
Since I experienced 4 different tricks to make it work, I will teach you A to D tricks to handle this so you can fully enjoy using the conversion function.
A: Manually create the tables in your SAP PaPM Cloud's SAP HANA Cloud DB
There are instances that you want to create the TCUR and T006 tables straight in SAP PaPM Cloud DB (SAP HANA Cloud Database), in case this is the preferred way allow me to share with you some sql commands to make it easier for you to create the tables.
Note: In case you already have CSV files with fields and data records, you can skip steps A1 and A2, instead you can use the IMPORT DATA option
else follow below....
1) In SAP HANA Cloud Database as SAP_PAPM_ADMIN or any other user, create the tables needed using below SQL commands
---CREATE T006 Table
---Unit Conversion Rates Table
CREATE COLUMN TABLE "<SCHEMA>"."T006"(
"MANDT" NVARCHAR(3) DEFAULT '000' NOT NULL,
"MSEHI" NVARCHAR(3) DEFAULT '' NOT NULL,
"KZEX3" NVARCHAR(1) DEFAULT '' NOT NULL,
"KZEX6" NVARCHAR(1) DEFAULT '' NOT NULL,
"ANDEC" SMALLINT DEFAULT 0 NOT NULL,
"KZKEH" NVARCHAR(1) DEFAULT '' NOT NULL,
"KZWOB" NVARCHAR(1) DEFAULT '' NOT NULL,
"KZ1EH" NVARCHAR(1) DEFAULT '' NOT NULL,
"KZ2EH" NVARCHAR(1) DEFAULT '' NOT NULL,
"DIMID" NVARCHAR(6) DEFAULT '' NOT NULL,
"ZAEHL" INTEGER DEFAULT 0 NOT NULL,
"NENNR" INTEGER DEFAULT 0 NOT NULL,
"EXP10" SMALLINT DEFAULT 0 NOT NULL,
"ADDKO" DECIMAL(9, 6) DEFAULT 0 NOT NULL,
"EXPON" SMALLINT DEFAULT 0 NOT NULL,
"DECAN" SMALLINT DEFAULT 0 NOT NULL,
"ISOCODE" NVARCHAR(3) DEFAULT '' NOT NULL,
"PRIMARY" NVARCHAR(1) DEFAULT '' NOT NULL,
"TEMP_VALUE" DOUBLE DEFAULT 0 NOT NULL,
"TEMP_UNIT" NVARCHAR(3) DEFAULT '' NOT NULL,
"FAMUNIT" NVARCHAR(1) DEFAULT '' NOT NULL,
"PRESS_VAL" DOUBLE DEFAULT 0 NOT NULL,
"PRESS_UNIT" NVARCHAR(3) DEFAULT '' NOT NULL,
CONSTRAINT "T006~0" PRIMARY KEY INVERTED VALUE(
"MANDT",
"MSEHI"
)
)
UNLOAD PRIORITY 5 AUTO MERGE;
---CREATE T006D Table
---Unit Conversion Dimension Table
CREATE COLUMN TABLE "<SCHEMA>"."T006D"(
"MANDT" NVARCHAR(3) DEFAULT '000' NOT NULL,
"DIMID" NVARCHAR(6) DEFAULT '' NOT NULL,
"LENG" SMALLINT DEFAULT 0 NOT NULL,
"MASS" SMALLINT DEFAULT 0 NOT NULL,
"TIMEX" SMALLINT DEFAULT 0 NOT NULL,
"ECURR" SMALLINT DEFAULT 0 NOT NULL,
"TEMP" SMALLINT DEFAULT 0 NOT NULL,
"MOLQU" SMALLINT DEFAULT 0 NOT NULL,
"LIGHT" SMALLINT DEFAULT 0 NOT NULL,
"MSSIE" NVARCHAR(3) DEFAULT '' NOT NULL,
"TEMP_DEP" NVARCHAR(1) DEFAULT '' NOT NULL,
"PRESS_DEP" NVARCHAR(1) DEFAULT '' NOT NULL,
CONSTRAINT "T006D~0" PRIMARY KEY INVERTED VALUE(
"MANDT",
"DIMID"
)
)
UNLOAD PRIORITY 5 AUTO MERGE;
---CREATE TCURF Table
---Currency Conversion Prefactors Table
CREATE COLUMN TABLE "<SCHEMA>"."TCURF"(
"MANDT" NVARCHAR(3) DEFAULT '000' NOT NULL,
"KURST" NVARCHAR(4) DEFAULT '' NOT NULL,
"FCURR" NVARCHAR(5) DEFAULT '' NOT NULL,
"TCURR" NVARCHAR(5) DEFAULT '' NOT NULL,
"GDATU" NVARCHAR(8) DEFAULT '' NOT NULL,
"FFACT" DECIMAL(9) DEFAULT 0 NOT NULL,
"TFACT" DECIMAL(9) DEFAULT 0 NOT NULL,
"ABWCT" NVARCHAR(4) DEFAULT '' NOT NULL,
"ABWGA" NVARCHAR(8) DEFAULT '00000000' NOT NULL,
CONSTRAINT "TCURF~0" PRIMARY KEY INVERTED VALUE(
"MANDT",
"KURST",
"FCURR",
"TCURR",
"GDATU"
)
)
UNLOAD PRIORITY 5 AUTO MERGE;
---CREATE TCURN Table
---Currency Conversion Notation Table
CREATE COLUMN TABLE "<SCHEMA>"."TCURN"(
"MANDT" NVARCHAR(3) DEFAULT '000' NOT NULL,
"FCURR" NVARCHAR(5) DEFAULT '' NOT NULL,
"TCURR" NVARCHAR(5) DEFAULT '' NOT NULL,
"GDATU" NVARCHAR(8) DEFAULT '' NOT NULL,
"NOTATION" NVARCHAR(1) DEFAULT '' NOT NULL,
CONSTRAINT "TCURN~0" PRIMARY KEY INVERTED VALUE(
"MANDT",
"FCURR",
"TCURR",
"GDATU"
)
)
UNLOAD PRIORITY 5 AUTO MERGE;
---CREATE TCURR Table
---Currency Conversion Rates Table
CREATE COLUMN TABLE "<SCHEMA>"."TCURR"(
"MANDT" NVARCHAR(3) DEFAULT '000' NOT NULL,
"KURST" NVARCHAR(4) DEFAULT '' NOT NULL,
"FCURR" NVARCHAR(5) DEFAULT '' NOT NULL,
"TCURR" NVARCHAR(5) DEFAULT '' NOT NULL,
"GDATU" NVARCHAR(8) DEFAULT '' NOT NULL,
"UKURS" DECIMAL(9, 5) DEFAULT 0 NOT NULL,
"FFACT" DECIMAL(9) DEFAULT 0 NOT NULL,
"TFACT" DECIMAL(9) DEFAULT 0 NOT NULL,
CONSTRAINT "TCURR~0" PRIMARY KEY INVERTED VALUE(
"MANDT",
"KURST",
"FCURR",
"TCURR",
"GDATU"
)
)
UNLOAD PRIORITY 5 AUTO MERGE;
---CREATE TCURV Table
---Currency Conversion Configuration Table
CREATE COLUMN TABLE "<SCHEMA>"."TCURV"(
"MANDT" NVARCHAR(3) DEFAULT '000' NOT NULL,
"KURST" NVARCHAR(4) DEFAULT '' NOT NULL,
"XINVR" NVARCHAR(1) DEFAULT '' NOT NULL,
"BWAER" NVARCHAR(5) DEFAULT '' NOT NULL,
"XBWRL" NVARCHAR(1) DEFAULT '' NOT NULL,
"GKUZU" NVARCHAR(4) DEFAULT '' NOT NULL,
"BKUZU" NVARCHAR(4) DEFAULT '' NOT NULL,
"XFIXD" NVARCHAR(1) DEFAULT '' NOT NULL,
"XEURO" NVARCHAR(1) DEFAULT '' NOT NULL,
CONSTRAINT "TCURV~0" PRIMARY KEY INVERTED VALUE(
"MANDT",
"KURST"
)
)
UNLOAD PRIORITY 5 AUTO MERGE;
---CREATE TCURX Table
---Currency Conversion Precisions Table
CREATE COLUMN TABLE "<SCHEMA>"."TCURX"(
"CURRKEY" NVARCHAR(5) DEFAULT '' NOT NULL,
"CURRDEC" SMALLINT DEFAULT 0 NOT NULL,
CONSTRAINT "TCURX~0" PRIMARY KEY(
"CURRKEY"
)
)
UNLOAD PRIORITY 5 AUTO MERGE;2) Once tables are created in the desired schema, Load data records via Open Data
3) Once tables and data records were completely validated and stable, it is important to GRANT SAP_PAPM some privileges to read on these tables via below commands
---To grant full access to the schema
GRANT ALL PRIVILEGES ON SCHEMA "<SCHEMA>" TO SAP_PAPM;
---To grant specific artifact access
GRANT ALL PRIVILEGES ON "<SCHEMA>"."TCURV" TO SAP_PAPM;
GRANT ALL PRIVILEGES ON "<SCHEMA>"."TCURX" TO SAP_PAPM;
GRANT ALL PRIVILEGES ON "<SCHEMA>"."TCURN" TO SAP_PAPM;
GRANT ALL PRIVILEGES ON "<SCHEMA>"."TCURR" TO SAP_PAPM;
GRANT ALL PRIVILEGES ON "<SCHEMA>"."TCURF" TO SAP_PAPM;
GRANT ALL PRIVILEGES ON "<SCHEMA>"."T006" TO SAP_PAPM;
GRANT ALL PRIVILEGES ON "<SCHEMA>"."T006D" TO SAP_PAPM;
4) Once SAP_PAPM user has been fully granted, you may start using the said tables then in conversion settings without issues. Just ensure though to take into account some hints from my below screenshot
Trick A done!
B: Consuming from external source
Most of the time there is already an existing TCUR or T006 tables in your private network, specially if you have SAP ERP or SAP S4HANA, if this is the case then we can consume those information via virtual table or data replication.
Read this blogpost to get an idea how to integrate your existing SAP PaPM Cloud's runtime database (SAP HANA Cloud) with external sources or database.
1) Since by now we should know how to create remote source from SAP HANA On-Premise to SAP HANA Cloud or SAP HANA Cloud to SAP HANA Cloud, we can already fast forward into creating virtual tables.
---CURRENCY TABLES
CREATE VIRTUAL TABLE "<TARGETSCHEMA>"."TCURF" AT "<REMOTESOURCENAME>"."<NULL>"."<SOURCESCHEMA>"."TCURF";
CREATE VIRTUAL TABLE "<TARGETSCHEMA>"."TCURR" AT "<REMOTESOURCENAME>"."<NULL>"."<SOURCESCHEMA>"."TCURR";
CREATE VIRTUAL TABLE "<TARGETSCHEMA>"."TCURV" AT "<REMOTESOURCENAME>"."<NULL>"."<SOURCESCHEMA>"."TCURV";
CREATE VIRTUAL TABLE "<TARGETSCHEMA>"."TCURX" AT "<REMOTESOURCENAME>"."<NULL>"."<SOURCESCHEMA>"."TCURX";
CREATE VIRTUAL TABLE "<TARGETSCHEMA>"."TCURN" AT "<REMOTESOURCENAME>"."<NULL>"."<SOURCESCHEMA>"."TCURN";
---example CREATE VIRTUAL TABLE "SAP_PAPM_ADMIN"."TCURF" AT "SAPPEC_HDB"."<NULL>"."SAPPEC"."TCURF";
---UNIT TABLES
CREATE VIRTUAL TABLE "<TARGETSCHEMA>"."T006" AT "SAPPEC_HDB"."<NULL>"."<SOURCESCHEMA>"."T006";
CREATE VIRTUAL TABLE "<TARGETSCHEMA>"."T006D" AT "SAPPEC_HDB"."<NULL>"."<SOURCESCHEMA>"."T006D";
---example CREATE VIRTUAL TABLE "SAP_PAPM_ADMIN"."T006" AT "SAPPEC_HDB"."<NULL>"."SAPPEC"."T006";2) Check in the target schema if the Virtual tables are created and stable. Below is an example
3) Once tables and data records are consistent, it is important to GRANT SAP_PAPM some privileges to read these tables via below commands
---To grant full access to the schema
GRANT ALL PRIVILEGES ON SCHEMA "<SCHEMA>" TO SAP_PAPM;
---To grant specific artifact access
GRANT ALL PRIVILEGES ON "<SCHEMA>"."TCURV" TO SAP_PAPM;
GRANT ALL PRIVILEGES ON "<SCHEMA>"."TCURX" TO SAP_PAPM;
GRANT ALL PRIVILEGES ON "<SCHEMA>"."TCURN" TO SAP_PAPM;
GRANT ALL PRIVILEGES ON "<SCHEMA>"."TCURR" TO SAP_PAPM;
GRANT ALL PRIVILEGES ON "<SCHEMA>"."TCURF" TO SAP_PAPM;
GRANT ALL PRIVILEGES ON "<SCHEMA>"."T006" TO SAP_PAPM;
GRANT ALL PRIVILEGES ON "<SCHEMA>"."T006D" TO SAP_PAPM;
4) Once SAP_PAPM user has been fully granted, you may start using the said tables then in conversion settings just like in Trick A.
Trick B done!
Let us check trick C for answers
C: Use other tables as conversion tables
In case such scenario happens that your consistent TCUR and T006 tables are named differently, you can still use them in Conversion function without renaming them.
Since from the past 2 tricks we already have seen what must be done in the database to ensure stable and authorized usage of the tables, i will jump straight to the conversion settings for Trick C to save your time.
In SAP PaPM Cloud tenant, under Conversion setting you can use the tables that are not having the default TCUR or T006 table names by performing a mapping.
If we skip this section in our vanilla setups (Trick A and B), in trick C this is important to be filled.
Note: If you actually map tables in these textboxes, the system will not look for the default TCUR and T006 tables. Instead the system will respect the mapped tables and will be used during conversion.
Alright, now trick C is done!
Let me share with you trick D, not so much of a used configuration, but definitely something useful if you tend to have conversion tables that are actually adjustable by the end-users.
if it sounds interesting, then keep reading (^^,)
😧 Modifiable Conversion tables using PaPM Editable QE.
Since I enjoyed playing around with the Customized table mapping feature like in Trick C, let us make it more interesting. Let us
- create a Local PaPM Model Table as our TCURR Table
- create an editable query on top so that it can be adjusted during processing
- use a view that projects the data from Ytable, and use that as our TCURR table in the conversion setup
How? Here's how...
this model table represents the editable via user interface TCURR table. Take note of the function ID you will need it later in step 3.
Note: If you are not familiar which fields to use, you can do a sidetrick of consuming temporarly an external TCURR table using MT HANA so the fields will be added to your environment then right after create this model table using those fields that are automatically added.
3) Create a SAP HANA Cloud View on top of the Model table
As we need to consume back the adjusted rate for our conversion, you will need to create a SAP HANA Cloud view on top of the SAP_PAPM table using the database explorer
---view creation coming from the Model table in step D.1
CREATE VIEW "<SCHEMA>"."<TABLENAME>" AS (SELECT MANDT, KURST, FCURR, TCURR, GDATU, UKURS, FFACT, TFACT FROM "SAP_PAPM"."Y105<ENV><VER><FID>");
---example
CREATE VIEW "SAP_PAPM_ADMIN01"."Y105CV10001TCURR" AS (SELECT MANDT, KURST, FCURR, TCURR, GDATU, UKURS, FFACT, TFACT FROM "SAP_PAPM"."Y105CV10001TCURR");4) Use now this specific TCURR table (hana view created under step 3) in your conversion setting
AFTER
Tricks A to D, done!!
I hope this blogpost helped you to understand and be more confident with using Conversion Function during modeling and calculations.
Happy Modeling in the cloud!
- SAP Managed Tags:
| User | Count |
|---|---|
| 5 | |
| 3 | |
| 3 | |
| 2 | |
| 2 | |
| 2 | |
| 2 | |
| 1 | |
| 1 | |
| 1 |
