Monday, March 17, 2014

Bug & Workaround - SKIP not working in Logic Accounts

Hi all,

last week I got some feedback from a colleague saying that they tried using SKIP in Logic Accounts but it was not working as it was with Classic FDM.

Firstly, I would say that Logic Accounts have not been changed in FDMEE so they basically do the same as Classic FDM, creates additional data not coming from the source.

You have same operators but you will have to use Jython if you need to add any script to your logic accounts.

What about SKIP?
Skip is used to skip the calculation and therefore do not create the logic account when condition is met. In the example above the logic account will be only created if the current value is less than zero.

Let's see similar script in FDMEE using Jython:
We will multiply the current value of the logic account times 2 when it is less than zero. On the other hand, the logic account should not be created when CURVAL is equals or greater than zero. 

Bug
What happened if we execute the import?
Source Data:
Logic Accounts created:
Logic accounts are also created when CURVAL is greater than zero...not good :-)

If you take a look to the FDMEE process log you will notice how logic accounts are currently working.
Firstly, all logic accounts are generated. When I say all, I mean all:  

 INSERT INTO TDATASEG_T (
                    LOADID
                    ,PARTITIONKEY
                    ,CATKEY
                    ,RULE_ID
                    ,PERIODKEY
                    ,ACCOUNT
                    ,ENTITY
                    ,VALID_FLAG
                    ,CURKEY
                    ,CALCACCTTYPE
                    ,ATTR13
                    ,AMOUNT
                    ,AMOUNTX
                    ,UD1
                    ,UD10
     ,DATAKEY
      )
      SELECT       LOADID
       ,PARTITIONKEY
                    ,CATKEY
                    ,RULE_ID
                    ,PERIODKEY
                    ,ACCOUNT
                    ,ENTITY
                    ,VALID_FLAG
                    ,CURKEY
                    ,CALCACCTTYPE
                    ,ATTR13
                    ,AMOUNT
                    ,AMOUNTX  
                      ,UD1
                      ,UD10
      ,TDATASEG_DATAKEY_S.NEXTVAL AS DATAKEY
      FROM (
                    SELECT LOADID
                    ,PARTITIONKEY
                    ,CATKEY
                    ,RULE_ID
                    ,PERIODKEY
                   
,'LA_UNITNEG' AS ACCOUNT                    ,ENTITY
                    ,'Y' AS VALID_FLAG
                    ,CURKEY
                   
,-1 AS CALCACCTTYPE
                    ,'LA_UNITNEG' AS ATTR13
                    ,SUM(AMOUNT) AMOUNT
                    ,SUM(AMOUNTX) AMOUNTX
                      ,UD1
                      ,UD10
        FROM TDATASEG_T
       
WHERE LOADID=996 AND (ACCOUNT IN ('Units'))        GROUP BY
                    LOADID
                    ,PARTITIONKEY
                    ,CATKEY
                    ,RULE_ID
                    ,PERIODKEY
                    ,CURKEY
                    ,ENTITY
                      ,UD1
                      ,UD10
      ) LOGIC_ACCOUNT

2014-03-05 15:17:10,583 DEBUG [AIF]: Inserted 12 rows for logic account LA_UNITNEG
After logic accounts are generated, Jython function is executed for each logic account generated. We can see how an update statement is executed (CURVAL*2) for each logic account with negative balance (bold):

2014-03-05 15:17:10,605 DEBUG [AIF]: Executing function:
if 3598< 0:
 RESULT= 3598 * 2
else:
 RESULT = "skip"
2014-03-05 15:17:10,609 DEBUG [AIF]: Executing function:
if 3689< 0:
 RESULT= 3689 * 2
else:
 RESULT = "skip"
2014-03-05 15:17:10,641 DEBUG [AIF]: Executing function:
if -6988< 0:
 RESULT= -6988 * 2
else:
 RESULT = "skip"
2014-03-05 15:17:10,645 DEBUG [AIF]:
         update TDATASEG_T set
         AMOUNT=-13976
         ,AMOUNTX=-13976
         where DATAKEY='96384'       

2014-03-05 15:17:10,648 DEBUG [AIF]: Executing function:
if 2999< 0:
 RESULT= 2999 * 2
else:
 RESULT = "skip"
2014-03-05 15:17:10,651 DEBUG [AIF]: Executing function:
if 4064< 0:
 RESULT= 4064 * 2
else:
 RESULT = "skip"
2014-03-05 15:17:10,654 DEBUG [AIF]: Executing function:
if 3874< 0:
 RESULT= 3874 * 2
else:
 RESULT = "skip"
2014-03-05 15:17:10,657 DEBUG [AIF]: Executing function:
if -6254< 0:
 RESULT= -6254 * 2
else:
 RESULT = "skip"
2014-03-05 15:17:10,662 DEBUG [AIF]:
         update TDATASEG_T set
         AMOUNT=-12508
         ,AMOUNTX=-12508
         where DATAKEY='96388'       
2014-03-05 15:17:10,664 DEBUG [AIF]: Executing function:

if 3455< 0:
 RESULT= 3455 * 2
else:
 RESULT = "skip"
2014-03-05 15:17:10,667 DEBUG [AIF]: Executing function:
if 3291< 0:
 RESULT= 3291 * 2
else:
 RESULT = "skip"
2014-03-05 15:17:10,671 DEBUG [AIF]: Executing function:
if 4678< 0:
 RESULT= 4678 * 2
else:
 RESULT = "skip"
2014-03-05 15:17:10,674 DEBUG [AIF]: Executing function:
if 6874< 0:
 RESULT= 6874 * 2
else:
 RESULT = "skip"
2014-03-05 15:17:10,677 DEBUG [AIF]: Executing function:
if 4157< 0:
 RESULT= 4157 * 2
else:
 RESULT = "skip"


So basically:
1. Generate all logic accounts
2. Update amount for logic accounts meeting conditions specified in our script
3. What about the rest of logic accounts which should be skipped?

Workaround
Although you can think in other workarounds based on your requirements, I will show you one which basically consists in hacking FDMEE a little bit :-)

We know that most of the FDMEE functionality is shipped by Oracle in ODI Scenarios. Logic accounts generation is implemented in scenario SCEN_COMM_LOAD_BALANCES_001.xml (E:\Oracle\Middleware\EPMSystem11R1\products\FinancialDataQuality\odi\11.1.2.3.00\workrep)
If we take a look to the code in the ODI Scenario we can see how codes jumps to the next Jython function (continue statement) when result equals "SKIP" (RESULT.upper() = "SKIP")
So actually FDMEE is not skipping the logic account as it was inserted in a previous step.
Any ideas? we could add a SQL statement in order to delete logic accounts from temporary table TDATASEG_T.
DELETE FROM TDATASEG_T WHERE DATAKEY=...
We have replaced continue statement by delete statement.
The new version of the scenario must be re-imported into the ODI work repository (INSERT_UPDATE mode). This action can be performed using ODI Studio.
After we import data again, we can see how the logic accounts are now correctly generated:
 
The FDMEE process log now shows the delete statement executed for each logic account that is skipped:

Hopefully Oracle will fix this issue in the patch (11.1.2.3.500?)

What we learnt?
- How logic accounts internally work (currently)
- Review code from ODI scenarios
- How to jump to next iteration in Jython (continue statement)

Enjoy!


1 comment:

  1. Hai Franc ,

    Any update with this bug ?

    Thanks
    Setiawan

    ReplyDelete

Thanks for feedback!