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:
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.
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!
Hai Franc ,
ReplyDeleteAny update with this bug ?
Thanks
Setiawan