HFM

Using JHAT to automate HFM tasks

by on
Using JHAT to automate HFM tasks
15024 Views
65 Comments

HFM Task Flows provides a way to automatize HFM tasks (consolidations, data extracts, etc…). JHAT tool is another way to automatize these 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 a better flexibility than Task Flows.

How does it work?

JHAT utility is present in the path hereunder:

Drive:\Oracle\Middleware\EPMSystem11R1\products\FinancialManagement\Server\jhat.bat

The batch file embeds all libraries, paths and other references to execute HFM tasks:

  • Home settings

  • Library settings
 
  • Java and JHAT execution settings

Before the first run, it’ mandatory to create “setenv.cmd” file and set the parameter EPM_ORACLE_INSTANCE_FOR_JHAT to point to the EPM instance:

JHAT usage

A text file provides the tasks to be executed against HFM. Basically, the structure of the file is as below:

  • Logon()
  • OpenApplication()
  • SetPOV()
  • Consolidate() (or other HFM function)
  • CloseApplication()
  • Logout()

For example:

You only have to provide the file previously create as argument and call the utility to run the task:

jhat.bat -I E:\JHAT\test.txt

Use “jhat.bat -H” command to explore the available options:

This utility can be used to launch consolidations, data load, data extraction, etc…

Some available functions from the HFM API:

  • LoadSecurity
  • ExtractSecurity
  • LoadMetaData
  • ExtractMetaData
  • LoadData
  • ExtractData
  • LoadRules
  • ExtractRules
  • Allocate
  • …

As the tool is based on batch file, it’s easy to schedule using Windows Task Scheduler or any other enterprise scheduling tool (Control M, Dollar U, etc…).

 

Tags automatizationbatchepmHFMhyperionjhatschedulingworkspace
Next article Loading many columns in FDMEE
Previous article Hyperion Financial Management On-Premise Roadmap

65 Comments

      • Nguyen Lê

        Hello Anand Thota,

        Sure you can convert the script which call jhat.bat in PowerShell.
        Here is an example of what can be done :

        $HAT_Script=’E:\Test_JHAT\conso.hat’
        $LogPath=’E:\Test_JHAT\’
        $LOG_FILE=’E:\Test_JHAT\conso.log’

        Start-Transcript -path $LogPath\JHAT-$(((get-date).ToUniversalTime()).ToString(“yyyyMMddThhmmssZ”)).log

        start-Process “cmd.exe” “/c jhat.bat -I$HAT_Script -O$LOG_FILE -T -E1 -R1 -A0”
        exit

        Reply
  6. Tom

    hi Nguyen, this looks interesting, but we are using HFM 11.1.2.3 and I couldnt find this JHAT tool in our installation folder, nor on Oracle support site. Do you have more information on this that how it can be installed or from where to get it? Thx

    Tom

    Reply
    • Tom

      From Oracle support:

      For versions prior to 11.1.2.4, there was a third party command line utility called HFMCmd (you can google it and find it for download). But it was written with the Windows HFM API which went away with 11.1.2.4. For 11.1.2.4, look into JHAT which is supported by HFM.

      Reply
  9. nizar

    Hi Daniel,

    Please find below an example for extract data:
    ExtractData(“c:\\Voyager\\Data_Extract.dat”, “c:\\Voyager\\Data_Extract.log”, ”;”, ”YTD”, ”2000”, ”Actual”, “Entity”, ”All”, “1”);

    The last “1” is for the “dynamic data”. Choose “1” to extract dynamic data and 0 if you don’t want to.

    Kind regards,

    Nizar

    Reply
    • Eric Snow

      Hi Niza,

      I called the function ExtractData by using this line ExtractData(“myfile.dat”, “mylog.log”, “;”, “YTD”, “2018”, “ACTUAL”, “MyEntity”, “All”, “1”, “True”) ;

      I got a NumberFormatException error message on “MyEntity” parameter. Is it supposed to get Integer type value ?
      I can’t figure out. Could you help me.

      Regards,
      Eric

      Reply
    • Samaresh Jana

      Hi Nizar,
      I called the function ExtractData by using below code.

      Logon(“false”, “”, “username”,”password”);
      OpenApplication(“cluster”, “application”);
      SetPOV(“Actual”,”2016″,”Dec”,”YTD”,”My Entity”,”USD”,”My Account”,”[Icp None]”,”[None]”,”[None]”,”[None]”,”[None]”);
      InitLists ();
      AddItemToList(“1″,”Entity”,”My Entity”);
      ExtractData(“c:\\Test\\Data_Extract.dat”,”c:\\Test\\Data_Extract.log”,”;”,”YTD”,”2016″,”Actual”,”1″,”All”,”1″”true”);
      CloseApplication();
      Logout();

      But I am getting NullpointerException for ExtractData line.Could you please help me.
      Thanks.

      Reply
  10. nizar

    Hi,

    for the Entity you must use lists.

    Below an exemple for the Entity dimension:

    InitLists ();
    AddItemToList(“1″,”Entity”, “NEONN”);

    then you must use “1” for the Entity field.

    Best regards.

    Reply
  11. Bhuvaneswari

    Hi,

    This is great information.

    I am able to run translations using below function for an entity, but when I am trying to run translation for a list of entities (member list) then it’s giving me an error “The POV selected for this function is invalid”.

    Could you help me?

    SetPov(“ACTUAL”,”2018″,”Oct”,”YTD”,”Entity_NonUSD_List”,”USD”,”BS”,”[ICP Top]”,”Cust1″,”Cust2″,”Cust3″,”Cust4″);
    Translate();

    Thanks.

    Reply
  12. nizar

    Hi Bhuvaneswari,

    You have to ask your HFM developer to give you the right POV to use for the translate.

    It depends on the application structure.

    Best regards.

    Reply
  14. Samaresh Jana

    Hi Nizar,
    I called the function ExtractData by using below code.

    Logon(“false”, “”, “username”,”password”);
    OpenApplication(“cluster”, “application”);
    SetPOV(“Actual”,”2016″,”Dec”,”YTD”,”My Entity”,”USD”,”My Account”,”[Icp None]”,”[None]”,”[None]”,”[None]”,”[None]”);
    InitLists ();
    AddItemToList(“1″,”Entity”,”My Entity”);
    ExtractData(“c:\\Test\\Data_Extract.dat”,”c:\\Test\\Data_Extract.log”,”;”,”YTD”,”2016″,”Actual”,”1″,”All”,”1″”true”);
    CloseApplication();
    Logout();

    But I am getting NullpointerException for ExtractData line.Could you please help me.
    Thanks.

    Reply
    • Saurabh

      Were you able to get around the NullpointerException error?

      I am getting the following error and it seems like the utility is not able to process the file at all.
      java.lang.NullPointerException
      at oracle.epm.fm.jhat.ProcessFile.readFileAndExecute(ProcessFile.java:75)
      at oracle.epm.fm.jhat.JHATMain.main(JHATMain.java:30)
      java.lang.NullPointerException
      at oracle.epm.fm.jhat.ProcessFile.readFileAndExecute(ProcessFile.java:118)
      at oracle.epm.fm.jhat.JHATMain.main(JHATMain.java:30)
      Press any key to continue . . .

      I am just trying to logon and open the application
      Logon(“false”,””,”USERNAME”,”PASSWORD”);
      OpenApplication(“CLUSTERNAME”,”APPLICATIONNAME”);
      CloseApplication();
      Logout();

      Reply
  15. nizar

    Hi Samaresh, Hi Saurabh,

    You have to use the Jhat.bat file directly from the server. if you copy paste it will not work unless you did many configuration steps.

    Reply
  16. Tahina

    Hi,

    Is there a way to extract user and group only instead of extracting all (security class, security class access, …) by using the ExtractSecurity function.
    Thank you.

    Regards

    Reply
  24. nizar

    Hi,

    Below an example how to call Extended analytics :

    EAExtract (“MyDSN”,”EA_Simple”,”specified”,”All”,”1″, “All”, “2”, “4”, “All”, “3”, “1”, “2”, “2”, “1”, “1”);

    Parameters are below :

    1 DSN Name
    2 Table Prefix Name
    3 Clear Option (All or Specified)
    4 Scenario (List ID # or All)
    5 Year (List ID # or All)
    6 Period List (List ID # or All)
    7 View (List ID # or All)
    8 Entity (List ID # or All)
    9 Value (List ID # or All)
    10 Account (List ID # or All)
    11 ICP (List ID # or All)
    12 Custom1 (List ID # or All)
    13 Custom2 (List ID # or All)
    14 Custom3 (List ID # or All)
    15 Custom4 (List ID # or All)

    Reply
    • Abhishek

      Hi Nizar,

      Thanks for your update. I am getting null pointer exception even when. I am just trying to login and logout using following script.

      Logon(“false”,””,””,””);
      OpenApplication(“HFMPPRD”,”HFM”);
      CloseApplication();
      Logout();

      Best,
      AB

      Reply
  25. Peter

    Hi,

    Can you please tell me where I can find the syntax for the Functions used in JHAT (such as LoadData, ExtractData etc), I have scoured the internet but cannot find much information ?

    Reply
  26. Russ

    Hello, when using jhat.bat with the -I command, I get the error Input file does not exist. However, it does exist and I am referring to the proper directory it resides in. Any ideas as to how to correct this? Thanks so much for a great blog post.

    Reply
    • Harijs

      Please specify “-I” switch and path to the file WITHOUT space:
      jhat.bat -I”your file”

      The same for the “-O” switch.

      Reply
  28. PRANAY

    Can someone help with LOADDATA() I’m using following script to load data, I get Load succeeded but no data is loaded, I do get rejects in the log with message “Line: 1, Error: No section has been specified to determine if this is data, description or line item detail.”
    Script:
    Logon(“false”, “”, “ID”, “Password”);
    OpenApplication(“Server”, “app”);
    LoadData(“Replace”, “T:\Oracle\Automation\Datafile.csv”, “T:\Oracle\Automation\logfile_name.log”, “True”,”false”,”,”);
    CloseApplication();
    Logout();

    Questions:
    1. What does the True, False and empty “,” pertains to
    2. Is there a way to assign rule for the load?
    3. What would the file format should be?

    Thanks

    Reply
    • Nguyen Lê

      Hi,

      Please find below an example to use LoadMetaData function:

      LoadMetaData (“file”,”log”,”;”,”Merge”,”true”,”true”,”true”,”true”,”true”,”true”,”true”,”true”,”true”,”true”,”true”,”true”,”true”,”true” “;”)

      Parameters are below :

      1 meadata file
      2 log file
      3 delimiter
      4 load mode
      5 Currencies
      6 Scenarios
      7 Entities
      8 Accounts
      9 Custom1
      10 Custom2
      11 Custom3
      12 Custom4
      13 Consolidation Methods
      14 App Settings
      15 System Accounts
      16 Value Dimension
      17 Load ICP
      18 Integrity Check

      regards
      Nguyen

      Reply
    • Nguyen Lê

      Hi,

      Please find below an example for EAExtract function :

      OpenApplication(“HULK”,”CSB”);

      InitList();
      AddItemToList(“1″,”Period”,”July”);
      AddItemToList(“2″,”Entity”,”CONNECTICUT.STAMFORD”);
      AddItemToList(“3″,”Account”,”Sales”);
      AddItemToList(“4″,”Value”,””);
      AddItemToList(“5″,”ICP”,”[ICP_None]”);
      AddItemToList(“6″,”Custom1″,”GolfBalls”);
      AddItemToList(“7″,”Custom2″,”Customer2”);
      AddItemToList(“8″,”Custom3″,”[None]”);
      AddItemToList(“9″,”Custom4″,”Increases”);
      AddItemToList(“10″,”View”,”Periodic”);
      AddItemToList(“11″,”Scenario”,”Actual”);
      AddItemToList(“12″,”Year”,”2004″);

      EAExtract(“EATEST”,”CSB”,”All”,”Periodic”,”2004″,”Actual”,”1″,”2″,”3″,””,”All”,”All”,”All”,”All”,”All”,”All”,”All”);

      1 DSN Name
      2 Table Prefix Name
      3 Clear Option (All or Specified)
      4 Scenario (List ID # or All)
      5 Year (List ID # or All)
      6 Period List (List ID # or All)
      7 View (List ID # or All)
      8 Entity (List ID # or All)
      9 Value (List ID # or All)
      10 Account (List ID # or All)
      11 ICP (List ID # or All)
      12 Custom1 (List ID # or All)
      13 Custom2 (List ID # or All)
      14 Custom3 (List ID # or All)
      15 Custom4 (List ID # or All)

      regards
      Nguyen

      Reply
  31. Johnny

    hello,
    how do you figure out which arguments and in what order needs to be passed to each command ?

    LoadMetaData (“file”,”log”,”;”,”Merge”,”true”,”true”,”true”,”true”,”true”,”true”,”true”,”true”,”true”,”true”,”true”,”true”,”true”,”true” “;”)

    for example load metadata above. How did you figure this out ?

    Reply
    • Nguyen Lê

      Hello Johnny,

      In this example I also provide the argument list.
      so you just need to mach the command line argument with the list.
      the first one is metadata, the second the log file, the third the delimiter ….

      1 meadata file
      2 log file
      3 delimiter
      4 load mode
      5 Currencies
      6 Scenarios
      7 Entities
      8 Accounts
      9 Custom1
      10 Custom2
      11 Custom3
      12 Custom4
      13 Consolidation Methods
      14 App Settings
      15 System Accounts
      16 Value Dimension
      17 Load ICP
      18 Integrity Check

      Kind regards
      Nguyen

      Reply
  32. Giovanni

    crap it seems like almost every command in which you need to enter custom dimensions is limited to 4 custom dimensions.

    Anyone figure out how to get most of these to work ?

    for example you cant load a metadata file if you are using more than 4 custom dimensions.

    Reply
    • Nguyen Lê

      Hi Giovanni,

      After discuss with the dev it seems that you are right for some function and other function accept all arguments.
      Unfortunately you need to test to know what you can do with which function.

      Kind regards
      Nguyen

      Reply
  33. RK

    I am trying to run consolidation on HFM 11.2 environment. Typically we pass Scenario, Year, Period start, Period End,Entity name and Consolidation Type. Is there any order in which these paramteres are to be passed? Below is the syntax which I tried but that did not seem to work.
    Logon(“false”,””,”HFMConsol”,”******”);
    OpenApplication(“HFMClusterDEV”,”TEST”);
    SetPOV(“ACTUAL”,”2021″,”MAR”,”LEZ”);
    Consolidate(“AllWithData”);

    CloseApplication();

    Logout();

    LEZ is the Entity

    Reply
    • Stephanie M

      Yes RK

      There is an order see example screenshot above of Consolidation presented; however if this help here is my actual data maybe you can figure out the order. Note it may wrap the lines since this is a cut and paste so be mindful of the information presented.

      Logon(“false”,””,”myaccount”,”noneofyourbusiness”);
      OpenApplication(“PRODHFMCL”,”FINCLOSE”);
      SetPOV(“Actual”,”2021″,”Mar”,”Periodic”,”E_Z00″,”USD”,”A_NE0000″,”[Icp Top]”,”P_U0000″,”C_H000″,”L_L000″,”Balance_Type”);
      SetPOV(“Actual”,”2021″,”Apr”,”Periodic”,”E_Z00″,”USD”,”A_NE0000″,”[Icp Top]”,”P_U0000″,”C_H000″,”L_L000″,”Balance_Type”);
      Consolidate(“Impacted”);
      SetPOV(“Actual”,”2021″,”Mar”,”Periodic”,”E_D00″,”USD”,”A_NE0000″,”[Icp Top]”,”P_U0000″,”C_H000″,”L_L000″,”Balance_Type”);
      SetPOV(“Actual”,”2021″,”Apr”,”Periodic”,”E_D00″,”USD”,”A_NE0000″,”[Icp Top]”,”P_U0000″,”C_H000″,”L_L000″,”Balance_Type”);
      Consolidate(“Impacted”);
      CloseApplication();
      Logout();

      Reply
  36. ThomasC

    Hi,
    I know it’s an old post, but anyway… I was trying to test the JHAT tool using the following command to extract data from HFM, but I get an error when I try to run the following command:
    ExtractData(“E:\\Install\\Data_Extract.dat”,”E:\\Install\\Data_Extract.log”,”;”,”YTD”,”2020″,”Actual”,”1″,”All”,”1″,”true”);

    Note: I have tried with fewer parameters but that did not work either. I am unsure about the parameters after Actual… Hope you can help, thanks….

    Error:
    Start execution of action(script line 1) at Fri Apr 23 23:28:15 CEST 2021
    Successful
    End execution at Fri Apr 23 23:28:17 CEST 2021
    Action elapsed time: 00:00:01.498

    **********OpenApplication : Successful**********
    Start execution of action(script line 2) at Fri Apr 23 23:28:17 CEST 2021
    Successful
    End execution at Fri Apr 23 23:28:17 CEST 2021
    Action elapsed time: 00:00:00.313

    **********ExtractData : Failed**********
    Line No: 3
    Start execution of action(script line 3) at Fri Apr 23 23:28:17 CEST 2021
    Encountered unexpected exception null
    java.lang.reflect.InvocationTargetException
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at oracle.epm.fm.jhat.ProcessFile.readFileAndExecute(ProcessFile.java:197)
    at oracle.epm.fm.jhat.JHATMain.main(JHATMain.java:30)
    Caused by: java.lang.NullPointerException
    at oracle.epm.fm.jhat.action.ExtractActionHandler.extractData(ExtractActionHandler.java:607)
    … 6 more

    Failure
    End execution at Fri Apr 23 23:28:17 CEST 2021
    Action elapsed time: 00:00:00.000

    **********CloseApplication : Successful**********
    Start execution of action(script line 4) at Fri Apr 23 23:28:17 CEST 2021
    Successful
    End execution at Fri Apr 23 23:28:17 CEST 2021
    Action elapsed time: 00:00:00.016

    **********Logout : Successful**********
    Start execution of action(script line 5) at Fri Apr 23 23:28:17 CEST 2021
    Successful
    End execution at Fri Apr 23 23:28:17 CEST 2021
    Action elapsed time: 00:00:00.000

    Total Actions executed: 5
    Total Errors occurred: 1
    Invalid functions: 0

    Reply

Leave a Comment

Your data will be safe! Your e-mail address will not be published. Also other data will not be shared with third person. Required fields marked as *

18 − fifteen =