Search This Blog

Saturday, June 25, 2016

FDMEE Journal By Entity Load Process

Issue Loading Flat File Journals Using FDMEE

We were recently working with a client to convert their historical FDM Classic application to FDMEE.  With that there were a handful of locations that required processing a flat file Journal entry for HFM.  It appeared to be a quite simple migration task for FDMEE, after all this was pretty routine in FDM Classic. The flat files that we were dealing with happen to have 10-30 entities and there was a requirement to enable Journal ID by Entity within FDMEE.  Simple enough!

We setup the Global Application Properties and then made specific application load setting on the Data Load Rule. Everything was tracking the way we anticipated. It was time to load and validate the Journal Entries made it to HFM.  Everything went great until we clicked Export!  It returned a grey fish and issue within the log file.  Below are the two examples from different installations MS SQL and ORACLE database. 

MS SQL Error Example:

Error in Comm.executeJythonScript
Traceback (most recent call last): File "<string>", line 528, in executeJythonScript
File "C:\Oracle\Middleware\EPMSystem11R1/products/FinancialDataQuality/bin/HFM_LOAD.py", line 966, in <module>
blnLoadStatus = exportJournalFile(BigDecimal(str(fdmContext["LOCKEY"])),hfmAdapter,fdmAPI,fdmContext,strJournalFile,TargetOptions.get(20),TargetOptions.get(23),TargetOptions.get(21))
File "C:\Oracle\Middleware\EPMSystem11R1/products/FinancialDataQuality/bin/HFM_LOAD.py", line 644, in exportJournalFile
rsDrill = fdmAPI.executeQuery(strSQL, None)
SQLSyntaxErrorException: java.sql.SQLSyntaxErrorException: [FMWGEN][SQLServer JDBC Driver][SQLServer]Invalid column name 'UD21X'.

Oracle SQL Error Example:

Error in HfmData.loadData 
Traceback (most recent call last):
File "<string>", line 42, in loadData
File "<string>", line 549, in executeVBScript
File "<string>", line 528, in executeJythonScript
File "E:\Oracle\Middleware\EPMSystem11R1/products/FinancialDataQuality/bin/HFM_LOAD.py", line 966, in <module>
blnLoadStatus = exportJournalFile(BigDecimal(str(fdmContext["LOCKEY"])),hfmAdapter,fdmAPI,fdmContext,strJournalFile,TargetOptions.get(20),TargetOptions.get(23),TargetOptions.get(21))
File "E:\Oracle\Middleware\EPMSystem11R1/products/FinancialDataQuality/bin/HFM_LOAD.py", line 644, in exportJournalFile
rsDrill = fdmAPI.executeQuery(strSQL, None)

SQLSyntaxErrorException: java.sql.SQLSyntaxErrorException: ORA-00904: "UD22X": invalid identifier


I knew that there are only twenty UD columns in the in the TDATASEG table within FDM and FDMEE.  Which leads me to believe that the HFM_LOAD.py script was dynamically generating too much SQL, which in-turn blew up during the SQL execution on line 644 during the load process.  At first I thought there was an issue with the Import Format or Location setting, but it was a very simplistic Import Format and Location.  We scaled down the Journal Flat file and it processed successfully. We discovered that when the load file had up to 9 entities it worked fine, but anything more than that would cause it to fail. 

After verifying the setting once more, we cracked open the HFM_LOAD.py script and took a quick look.  In the exportJournalFile function the script performs a handful of tasks that are import to the SQL execution at line 644.  There are property setting and things that I will not go into here. 
  1. Get the Max Number of Custom Dimensions
    • populated fdmCustoms which is used later
  2. Get the Active Dimensions
  3. Generates the Trial Balance from the Active Dimensions
  4. Begins writing the Journal Header
  5. Writes Journal Lines
  6. Then at line 469 we begin the process to create the SQL for rsDrill
    • All of the code made sense. I even took it a step further and manually executed the code blocks in a custom script with FDMEE to insure it was returning the appropriate data, which it was. 
    • At line 587 or 632 depending on the database FDMEE is sitting on the Where clause is being dynamically built.  This code block matches the error that we received above. 

Where Clause UDx loop

        x = 0
        while x <= (len(oIntersect.varUDx) - 1):
          if oIntersect.varUDx[x] == None or oIntersect.varUDx[x] == "":
            strSQL += "And ((UD" + str(x + 3) + "X IS NULL) or (UD2X = '')) "
          else:
            strSQL += "And ((UD" + str(x + 3) + "X) = '" + oIntersect.varUDx[x] + "') "
          
          x += 1

    • At line 644 the rsDrill record set is created leveraging the SQL that was generated in the above process. I had the HFM_LOAD.py script write out to the log file the SQL generated during each execution, which is by Entity. 

SQL From Load File

SELECT 
 PartitionKey, CatKey, PeriodKey, 
 Desc1, Desc2, CalcAcctType, DataView, 
 Account, Entity, ICP, UD1, 
 UD2, UD3, UD4, UD5, UD6, UD7, 
 UD8, UD9, UD10, UD11, UD12, UD13, 
 UD14, UD15, UD16, UD17, UD18, UD19, 
 UD20, Attr1, Attr2, Attr3, Attr4, 
 Attr5, Attr6, Attr7, Attr8, Attr9, 
 Attr10, Attr11, Attr12, Attr13, Attr14, 
 ChangeSign, DECODE(ChangeSign, 0, (Amount), 1, (Amount *-1), (Amount)) AS Amt, 
 HasMemoItem, DataKey, ArchiveID 
From tDataSeg 
Where 
 (LOADID = 1324 And ((CalcAcctType) > 0) 
 And ((upper(DataView)) = 'YTD') 
 And ((upper(AccountX)) = '180010') 
 And ((upper(EntityX)) = '2333') 
 And ((upper(ICPX)) = '[ICP NONE]') 
 And ((upper(UD1X)) = 'HQ') 
 And ((upper(UD2X)) = 'HQ') 
 And ((UD3X) = 'EC_Base;SH_Base;HQ;HQ;SH_Base;HQ;EL_Base;EL_Base;HQ;HQ;') 
 And ((UD4X) = 'CF_CapEx;CF_CapEx;CF_OUT;CF_OUT;CF_OUT;CF_OUT;CF_CapEx;CF_OUT;CF_OUT;CF_OUT;') 
 And ((UD5X IS NULL) or (UD2X = '')) And ((UD6X IS NULL) or (UD2X = '')) 
 And ((UD7X IS NULL) or (UD2X = '')) And ((UD8X IS NULL) or (UD2X = '')) 
 And ((UD9X IS NULL) or (UD2X = '')) And ((UD10X IS NULL) or (UD2X = '')) 
 And ((UD11X IS NULL) or (UD2X = '')) And ((UD12X IS NULL) or (UD2X = '')) 
 And ((UD13X IS NULL) or (UD2X = '')) And ((UD14X IS NULL) or (UD2X = '')) 
 And ((UD15X IS NULL) or (UD2X = '')) And ((UD16X IS NULL) or (UD2X = '')) 
 And ((UD17X IS NULL) or (UD2X = '')) And ((UD18X IS NULL) or (UD2X = '')) 
 And ((UD19X IS NULL) or (UD2X = '')) And ((UD20X IS NULL) or (UD2X = '')) 
 And ((UD21X IS NULL) or (UD2X = '')) And ((UD22X IS NULL) or (UD2X = '')) ) 
ORDER BY 
 CalcAcctType, Entity, Account

At first I thought this was a client specific issue, but nothing else in the system was having an issue. I went to our 11.1.2.4 demo environment (base install) and asked a client if I could use their Test environment(11.1.2.4.200) to create a test Journal load process. Both environments failed with the same issue.

I wrote a small condition within in the two while x <= (len(oIntersect.varUDx) - 1): loops that limits the dynamic SQL to only twenty UD columns in the Where Clause. Below are the before and After code blocks.

Before Update While Loop


        x = 0
        while x <= (len(oIntersect.varUDx) - 1):
          if oIntersect.varUDx[x] == None or oIntersect.varUDx[x] == "":
            strSQL += "And ((UD" + str(x + 3) + "X IS NULL) or (UD2X = '')) "
          else:
            strSQL += "And ((UD" + str(x + 3) + "X) = '" + oIntersect.varUDx[x] + "') "
            
          x += 1

After Update While Loop

        x = 0
        while x <= (len(oIntersect.varUDx) - 1):
          if oIntersect.varUDx[x] == None or oIntersect.varUDx[x] == "":
            if int(x+3) <=20:
              strSQL += "And ((UD" + str(x + 3) + "X IS NULL) or (UD2X = '')) "
          else:
            if int(x+3) <=20:
              strSQL += "And ((UD" + str(x + 3) + "X) = '" + oIntersect.varUDx[x] + "') "
          
          x += 1

After the update we ran a few test loads and all the journal loaded successfully. We notified the appropriate people at Oracle and I implemented this as a temporarily fix, until Oracle comes back with a solution.

I hope this helps anyone that is running into the same issue.


No comments:

Post a Comment