HFM

Using JHAT to automate HFM tasks

byNguyen Lê on 6 September 2017

12775 Views

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…).

Smets September 12, at 14:44

very interesting !
Nilesh February 28, at 09:17

May I know as to which Hyperion versions that this will be compatible with?
Thanks!
nizar February 28, at 17:52

Hi Nilesh,

This API is available in all HFM versions.

Regards
Abhishek Khurana March 20, at 15:49

Hello,

Do we have any Oracle provided documentation on JHAT utility?

Thanks,
Abhishek

Cédric Reynaud March 20, at 16:17

Hello,

Unfortunately Oracle doesn’t provide any documentation on jhat (or we are not aware of it).

Kind regards,

Cédric.

Abhishek Khurana March 20, at 15:50

Hello,

Do we have any Oracle documentation to work with JHAT utility?

Thanks,
Abhishek
Tom March 20, at 16:06

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

Tom March 22, at 10:19

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.

Guillaume Slee June 23, at 12:55

Nugyen/Cédric, very interesting. I didn’t know this existed, thanks for sharing!

Cédric Reynaud June 24, at 10:53

With pleasure Guillaume!
Cheers

Daniel December 07, at 19:52

do you have an example on how to call the function ExtractData? I cant figure out the parameters
nizar December 10, at 21:23

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

Eric Snow December 17, at 10:18

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
Samaresh Jana May 04, at 16:51

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.

nizar December 17, at 18:58

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.
Bhuvaneswari December 19, at 12:05

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.
nizar December 19, at 16:50

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.

Bhuvaneswari December 20, at 10:14

Hi Nizar,

Thanks for the reply, it’s working!
Previous I have not used flower brackets for member list, after using the brackets it started working.

Thanks.

Eric Snow December 21, at 15:39

Hi Nizar,

Thank you, that’s very helpful.
Unfortunately there is no documentation about all function and how to use them.

Best regards,
Samaresh Jana May 04, at 16:44

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.

Saurabh July 20, at 05:14

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();

nizar July 22, at 19:52

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.

Samaresh August 12, at 16:46

Hi Nizar,

I have run it from the server. But getting the same error.

Thanks
Samaresh August 20, at 17:03

Hi Nizar,
I have used jhat.bat file from the server without any modification. But still getting the same error.

Thanks,
Samaresh

ABC September 16, at 14:11

Hi Sameresh,
Were you able to solve your java error?
dhibat September 17, at 16:35

Hi,

Did you initiate the EPM_ORACLE_INSTANCE_FOR_JHAT parameter ?

When you double click on JHAT.bat do you still have the error ?

Tahina July 23, at 17:04

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

dhibat September 17, at 16:32

Hi,

Using JHAT is not possible.

You can get those informations directly from the database.

Ken August 05, at 20:48

Hi All,
Where can I find a list of the supported API functions?

dhibat September 17, at 16:30

Hi Ken,

You have to decompile the jar file to extract all the possible functions.

Samaresh August 21, at 16:50

Hi Nizar,
I have used the Jhat.bat file directly from the server. But still I am getting the same error.

Thanks,
Samaresh
ABC September 12, at 13:25

Hello,

Good Post.
Can this utility be used for all HFM functions like, Consolidation, Translate, Extract Journals?

nizar September 17, at 16:29

Hi ABC,

Yes you can do all HFM tasks ( calculation, consolidation ..)

ABD September 20, at 13:02

Thanks Nizar,
Can you please post how to write command for generating IC report using JHAT?

BR
ABC

Astha Patni September 17, at 16:10

Hi Nizar,

I’m getting a null pointer exception issue with the Extract Data function. Any insights?
Astha Patni September 17, at 16:18

Hi Nizar,

Getting null pointer exception in ExtractData function. Using the AddItemToList to define the list as well.

Thanks
Astha
Abhi November 18, at 05:32

How to trigger Extended Analytics extract from this?

Best,
AB
Abhishek November 18, at 05:38

How to trigger Extended analytics extract using JHAT utility.
nizar November 18, at 10:46

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)

Abhishek November 18, at 22:04

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

Peter February 17, at 05:23

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 ?
Russ March 03, at 23:35

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.

Harijs May 08, at 12:10

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

The same for the “-O” switch.

Stephanie Disher April 15, at 21:22

Does anyone know what ExtractDataExtDim function is needs for the POV? Or show me an example? I am wondering if the is like the entire setPOV being placed in it.

Nguyen Lê April 16, at 11:27

Hello Stephanie,
To use ExtractDataExtDim function you need to set the 12 dimensions in the POV.

Kind regards,
Nguyen

Stephanie Disher April 16, at 18:03

Thank you Nguyen. I will do that.

PRANAY May 05, at 20:20

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
manoharan August 19, at 15:42

Hi,
Can you please share the details for loadmetadata function ?

Thanks,
Manoharan

Nguyen Lê September 24, at 10:15

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

shivank September 19, at 18:02

Can you please give one working script of ExtractData. EAExtract.? Tried all the suggestions you replied on this post, but some how its not working

Nguyen Lê September 24, at 10:25

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

Johnny October 22, at 20:39

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 ?

Nguyen Lê October 23, at 19:05

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

Giovanni October 23, at 04:15

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.

Nguyen Lê October 23, at 19:08

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

Using JHAT to automate HFM tasks

How does it work?

JHAT usage

55 Comments

Leave a Comment

Click here to cancel reply.