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.
