Symptoms

Hyperion Amount balances are being reduced in FDMEE as if it’s being divided by another 100, or moving the decimal places 2 spots for specific to few currencies (in this example two currencies IDR / JPY).

In following screen captured out of SAP, we can observe the account 111000 for IDR Currency display the accumulated balance of 58,827,195.

In FDMEE, using built-in SAP Adapter without any customization(s) the value for the above line-item is imported as 588,271.95

Cause

SAP Standard Behavior:

As and when SAP stores / retrieve data for our queries, it looks into TCURX table for the Number of Decimals tagged for a specific currency in Currency field. As we knew, TCURX table contains all currencies that were defaulted and number of decimals. TCURX Table store all currency codes which are not equal to 2 decimals.

TCRUX Table view in SAP:

At this moment it’s important to understand, How SAP stores data into FAGLFLEXT Table with Currency and Decimal values from TCURX Table?

If Currency Decimal is 0 then Amount / 100 [Amount divided by 100]

If Currency Decimal is 1 then Amount / 10 [Amount divided by 10]

If Currency Decimal is 2 then Amount (TCRUX table do not store the currency with Decimal 2).

If Currency Decimal is 3 then Amount * 10 [Amount multiplied by 10]

If Currency Decimal is 4 then Amount * 100 [Amount multiplied by 100]

If Currency Decimal is 5 then Amount * 1000 [Amount multiplied by 1000]

In this example, we are considering IDR Currency that’s having 0 (Zero) decimal that’s calculated as Amount / 100.

Let’s Mitigate the Symptom

In FDMEE, by default we prefer to pull the values from FAGLFLEXT Table (General Ledger: Totals) that create the issue as described in the Symptom. We mitigated the issue with customizing the solution by considering couple more tables from SAP.

We can fix the issue with a simple 10 steps. Let’s understand step-by-step.

1. Additional tables need to be get read access in SAP on Hyperion user (user to connect SAP DB2 database).
   1. SAPR3.TCURX
      
   2. SAPR3.T001 (holds Company and its local currency details)
2. Create 3 temporary staging tables on FDMEE Database
   1. HYP_TCURX

      

   2. HYP_T001

      

   3. LOGICAL_BALANCES_SAP_FAGLFLEXT (Same as LOGICAL_BALANCES_SAP with additional of last 4 columns)

      

3. Import new SAP tables into Model

   

4. Import new Staging tables into Model
   
5. Create  #1 new Interface   under 

   Source: SAPR3.TCURX

   Target: HYP_TCURX

   Map according to interface mappings

   Points to remember:

   Flow control: False

   Truncate: True

6. Create #2 new interface as

   Source: SAPR3.T001

   Target: HYP_T001

   Map according to interface mappings

   Points to remember:

   Flow control: False

   Truncate: True

7. Create #3 new interface as

   Source: SAPR3.FAGLFLEXT Join HYP_T001

   Target: LOGICAL_BALANCES_SAP_FAGLFLEXT

   Map according to interface mappings

8. Create #4 new interface (modified SAP to Logical Balances)

   Source: LOGICAL_BALANCES_SAP_FAGLFLEXT Left outer Join HYP_TCURX

   Target: LOGICAL_BALANCES_SAP

   Map according to interface mappings

9. Update SAP DB2 Balances package as below

   

10. Regenerate ODI Scenario at Import Format in FDMEE