Automate HFM Extract Audit Task and Data via command line utility

There are two tables in HFM that keep track of what the users are doing. These tables are grouped to contain user tasks and data entry changes. They are called

Table 1 :  <APPNAME>_DATA_AUDIT

Table 2 : <APPNAME>_TASK_AUDIT

The Data Audit log records the changes to the data and time-stamps the change. In addition to tracking the data changes a user makes in the system, the Data Audit log tracks the server, the username, and an activity code.

The Task Audit table is used to log tasks performed by a user on the system. 

HFMAuditExtractUtility :

HFMAuditExtractUtility is used to extract and perform the maintenance tasks related to Data audit and Task audit records from an existing HFM database. The records are extracted to a user specified CSV (comma separated values) file for use in external tools or for archival purposes.

There are two ways,

1.GUI (graphical user interface) wizard that walks you through the extract process.  It is straight forward method as you have select the UDL file and Path where you want to extract.

2.Command-line utility that is useful for batch processing. 

Both versions come in a 32-bit or 64-bit build. Only use the 64-zit build if your HFM application server machine does not have a 32- bit database client installed.

Note: It is recommended to keep the data audit and task audit tables record count not to exceed 5,00,000. Otherwise it will impact the performance of the HFM application.

In this Block I am going to explain the steps involved to Extract Audit Task and Data from DB table to CSV file using Command line utility and to automate the process via Batch file.

Command Prompt Utility path,

D:\Oracle\Middleware\EPMSystem11R1\products\FinancialManagement\Utilities\HFMAuditExtractCmdLine_x64.exe

My case HFM installed path is 

D:\Oracle\Middleware\EPMSystem11R1

Step 1 :

Create a Notepad and save with .bat extension,

eg., HFMAuditExtract.bat

Step 2 :

Below are the script which does Extract Audit Task and Data from the Tables and put  

into an particular folder and then truncate the data from the Table.

Script :

Part 1 :

echo %DATE%

echo %TIME%

set datetimef=%date:~-4%%date:~3,2%%date:~0,2%_%time:~0,2%%time:~3,2%%time:~6,2%

echo %datetimef%

set destfolder=HFMAudit_%datetimef%

echo Destination folder created is %destfolder%

mkdir D:\HFMAuditExtract\%destfolder%

Part 2 :

D:\Oracle\Middleware\EPMSystem11R1\products\FinancialManagement\Utilities\HFMAuditExtractCmdLine_x64.exe -d D:\HFMAuditExtract\%destfolder% -u D:\udl\HFM.udl -a DEMOHFM -s 01/01/2000 -e %date% -x Enabled -t Enabled -r Enabled -k Enabled

Part 1 script does, It will create a folder under the following path D:\HFMAuditExtract\ with date and time so that if script runs multiple times in a single day it will create new new folder to make sure there will not be any loss of data.

eg .,  D:\HFMAuditExtract\HFMAudit_080416_102016

Part 2 :

Path of the executable:

D:\Oracle\Middleware\EPMSystem11R1\products\FinancialManagement\Utilities\HFMAuditExtractCmdLine_x64.exe 

Destination Folder : -d D:\HFMAuditExtract\%destfolder%

Folder where Extract and logs need to be saved.

My case, New folder create with data and time format.

Path UDL File,

UDL file contains Database credential to connect HFM DB of Audit Tables 

-u D:\udl\HFM.udl  

HFM Application Name :

-a DEMOHFM

Start and End Date :

-s 01/01/2000   -e %date% 

Period of data which needs to be taken into account.

Extract Data Audit Data : -x Enabled  (Default : Disabled )

Extract Task Audit Data : -t Enabled  (Default : Disabled )

Truncate Data Audit Data : -r Enabled  (Default : Disabled )

Truncate Task Audit Data : -k Enabled  (Default : Disabled )

 Default Delimiter of the export file : semi colon;
if you want to change (to use : ) just modify the script (Must be single character ) as below

D:\Oracle\Middleware\EPMSystem11R1\products\FinancialManagement\Utilities\HFMAuditExtractCmdLine_x64.exe -d D:\HFMAuditExtract\%destfolder% -u D:\udl\HFM.udl -a DEMOHFM -l : -s 01/01/2000 -e %date% -x Enabled -t Enabled -r Enabled -k Enabled

Note : Part 1 and Part 2 as single batch file.