Little Known EPM tools: Using JHAT
JHAT tool is another way to automate HFM tasks using a batch file and the HFM API. It is the former HAT updated to be compliant with the last HFM release. JHAT offers the opportunity to use any scheduler to launch HFM tasks and provide better flexibility than Task Flows.
JHAT utility is present here (and the batch file embeds all libraries, paths and other references to execute HFM tasks);
Drive:\Oracle\Middleware\EPMSystem11R1\products\FinancialManagement\Server\jhat.bat
In this example set, we are going to make use of the power of PowerShell scripting and the functionality provided by JHat to perform an HFM metadata scan.
Running an HFM Metadata Scan using JHat
To begin with, let’s create an external variables file called PRD_External_Vars.ps1 and define all our required variables here.
You could also create 1 single file and declare the variables at the beginning, but I just like it to keep it separate as it becomes easy to manage and generally a cleaner approach.
#HFM Variables $HFM_user_jh = '"hfmadmin"' #Username to login $HFM_Password_jh = '"p@ssw0rd"' #Password for the user $HFM_Server_jh = '"HFMPrd"' #HFM cluster name $HFM_Application_jh = '"Demo"' #Application Name $Delimiter = '";"' #App file delimited $HFMScanMode = '"Replace"' #Use the 'Scan' or 'Replace'
Now that we have all our variables declared, let’s just get it going with the JHat script…
Let’s begin by importing all the variables that we declared above in out JHat script
#Create External Variable File Name (as per the environment) $dir_ext_var = "mydrive:\mydir\PRD_External_Vars.ps1" #Retrieve Variables from External Variable File . $dir_ext_var
While we are at it, let’s also declare few additional variables for the log and properties life.
#Log file to log all the steps being executed by JHat $HFMBatchLog = "mydrive:\\mydir\\HFMMetadata\\HFM_Metadata_Update_Load.log" #Location for the properties file that will be created by PowerShell on the fly. This will be used by JHat $OutPath="mydrive:\mydir\HFMMetadata" #Location of the jhat file installed on the Financial Management server $JHatLocation = "D:\Oracle\Middleware\EPMSystem11R1\products\FinancialManagement\Server" #Location of the properties file. This would be passed as a parameter to the jhat batch $InputFileLocation = "mydrive:\\mydir\\HFMMetadata\\hfm_md_load.properties" #Temporary location of the jhat log file $LogFileLocation_jh = "mydrive:\\mydir\\HFMMetadata\\hfmJH.log" #Temporary location of the powershell log file $LogFileLocation_ps = "mydrive:\\mydir\\HFMMetadata\\\hfmPS.log" #Log file which can be reviewed later after the job execution is completed. $LogPath_jh="mydrive:\\mydir\\HFMMetadata\\PROD_HFMMDScanJH.log" #Location of the powershell if there are any errors with powershell execution. $LogPath_ps="mydrive:\\mydir\\HFMMetadata\\PROD_HFMMDScanPS.log" #App file that will be used to perform the scan and/or load of HFM metadata (the file can be XML too) $DimensionFile="mydrive:\\mydir\\HFMMetadata\\HFM_MetadataFile.app"
The next interesting bit is to create the HFM Metadata load properties file on the fly. This file would be used by JHat utility to perform the metadata scan…
What we are doing below is to create a properties file that would be used by JHat to;
- Login to the application,
- Open a session for the application,
- Perform a metadata scan,
- Store the output in a log file,
- Close the session and
- Logout of the application.
#Clear contents of existing .properties file on the fly Clear-Content $OutPath\hfm_md_load.properties #Available Functions #Function Name: Logon - Login to the application #Function Name: OpenApplication - Open a session to the specified application #Function Name: LoadMetadata – Scan and/or load HFM metadata into the specified application #Function Name: CloseApplication – Close the session opened #Function Name: Logout – Log out of the application #Create .properties file on the fly Add-Content -Path $OutPath\hfm_md_load.properties -Value "Logon(""False"","""",$HFM_user_jh,$HFM_Password_jh);" Add-Content -Path $OutPath\hfm_md_load.properties -Value "OpenApplication($HFM_Server_jh,$HFM_Application_jh);" Add-Content -Path $OutPath\hfm_md_load.properties -Value "LoadMetadata($DimensionFile,$LogPath_jh,$Delimiter,$HFMScanMode,""True"",""True"",""True"",""True"",""True"",""True"",""True"",""True"",""True"",""True"",""False"",""False"",""False"",""True"");" Add-Content -Path $OutPath\hfm_md_load.properties -Value "CloseApplication();" Add-Content -Path $OutPath\hfm_md_load.properties -Value "Logout();" Add-Content -Path $OutPath\hfm_md_load.properties -Value "End" #Call Jhat api #The jhat batch requires the log file location and the inputfile location as the parameter Start-Process -FilePath "$JHatLocation\jhat.bat" -ArgumentList "-O$LogFileLocation_jh -I$InputFileLocation"
Finally, let’s run the PowerShell now.
Once the execution is complete, checking in Consolidation Administration, we can see that the Metadata load started and completed without any errors.
There are various other functions available with JHat,
Running An InterCompany report using JHat
#Function Name: GenerateReport – To Generate ICP report #Arg0 = Path (Path of the document in document manager) #Arg1 = docName (Name of the document) #Arg2 = reportType (valid options - intercompany, journal, EPU, ICTransactions, IC Match By Account, IC Match by ID) #Arg3 = reportFormat (HFM_FORMAT) #Arg4 = reportFile (location of the file where report must be stored) #Arg5 = overriddenPOV (specify the POV to override it with) GenerateReport("\\\","Monitoring_REP_Plug_Acct_Matching", "intercompany","HFM_FORMAT","D:\Oracle\Temp\Workspace\Intercompany\InterCompany.html","S#Scenario.Y#2019.P#Jun.W#YTD.V#<Entity Curr Total>.E#{Example.[Base]}");