Search This Blog

Loading...

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.


Tuesday, April 19, 2016

Encrypting and Decrypting FDMEE Passwords for Scripts

I have been working with FDM Classic and FDMEE for some time now and when working with passwords it gets interesting.  There are multiple options for adding layers of password protection for FDM/FDMEE scripts.
  1. Hardcoded passwords (definitely causes exposure)
  2. Custom encryption/decryption script/compile program
  3. Leveraging a password vault with API calls 
  4. System environment variables 
  5. ODI encryption and decryption process
  6. Leverage FDMEE's Encryption and decryption process
  7. etc...
Note: All of the options above are only as strong as you can secure the scripts from unauthorized access to the scripts or encryption\decryption code.  The key is to limit access and build in layers of security for process. 

In the past I would build a custom script to perform the encryption/decryption when needed, which works great but requires maintenance.  I read a great FDMEE Encryption Blog by Francisco Amores, where he leverages ODI to perform the encryption and decryption process. He broke down the process leveraging ODI and a custom script to retrieve the encrypted passwords from the ODI/FDMEE repository.  

The option that I like to use leverages the FDMEE Encryption/Decryption process outlined within the FDMEE Admin guide.  

Step 1: Create Encrypted Password Text File

Follow the instruction in the admin guide to create a encrypted password text file in the FDMEE password directory, which is defined within FDMEE system setting. 


 

If we look within the EncryptPassword.bat file we will notice a call to three different .jar files. 
  1. aif-batch.jar
  2. epm_j2se.jar
  3. registry-api.jar

The import jar file is the registry-api.jar which contains the encrypt and decrypt process the FDMEE uses when running the EncryptPassword.bat from the command line or FDMEE batch jobs that require a password.

The trick is to leverage the jar file in our custom FDMEE scripts when needing to access a SQL database or other secure process that requires a password. 

Step 2: Import the registry-api.jar Encryption process

Create a jython script and load the registry-api.jar for access to the encryption process.

1
2
#Import the encryption process
import com.hyperion.hit.registry.Encrypter as Encrypter

Step 3: Get the Encrypted password from UserName file


 1
 2
 3
 4
 5
 6
 7
 8
 9
10
import os
#get the password file
uid = "BReynolds"
uid_filename= uid + ".fdmee"
strFile = "//<ServerName>/FDMEE_DATA/PASSWORD/" + uid_filename
#get the encrypted password
if os.path.exists(strFile) and os.path.isdir(strFile) == False:
 #can put conditions around the file that is opened
 p = open(strFile,'r');
 enc = p.read();

In order to leverage the code multiple times we can pass variables to complete the uid_filename so that different processes can set the uid variable. In this example it is very simplistic for example purpose, but can be more dynamic based on FDMEE location attributes. 

Step 4: Decrypt Password for use


1
2
3
#decrypt the encrypted password
dbPass = Encrypter.decryptString(enc)
fdmAPI.showCustomMessage("Encrypted Password: " + str(enc) + " Decrypted Password: "  + str(dbPass))

Once we decrypt the password using Encrypter.decryptString, it can be read into a variable or called directly into the password input parameter.


I hope this helps everyone out there create a better way to handle passwords in scripts.  All feedback is always welcomed.