DRM Configuration File / INI file for Batch Client

Batch Client is a command line utility by which one can performs various actions in DRM in batch mode.

Batch Client Utility: drm-batch-client.exe

This utility accepts options from command line, configuration file or a combination from both. Refer to Oracle DRM documentation for more details.

In this post, I am showing a structure of a configuration file or .ini file for couple of operations as given below:

1. DeleteVersion
2. Import 

The example shown performs multiple operations.

The first section is the [General] section where you specify Operation, Username, Password and URL.

In the [Operations] section, list total number of operations you would like to perform and list names in the order you want system to execute.

Operation1 will be executed before Operation2 and so on. If any section is not defined as part of [Operations] section then it won't be executed.

Note that the name provided for Operation1, Operation2...OperationN must match the Section names you define else you will get an error.

Details of [Version_Delete] section:

[Version_Delete] is the name given to the section. You can give any name but the name defined here must match when you specify in [Operations] section.

Operation=DeleteVersion: This operation is used to delete the specified version.

VersionAbbrev="METADATA_PROD_IMPORT": "METADATA_PROD_IMPORT" is the version name which will be deleted on the execution of the script.

Details of [Product_Import] section:

This section is used to import product hierarchy in the specified version.

Operation=Import: This parameter is key and required to import a file.

Infile="product_integration.txt": Specify file name you want to import along with path.

ImportAbbrev=ADMIN_PRODUCT_INT: This is the import name to run. Note that an import needs to be defined in DRM as a pre-requisite.

ImportLogFileName="product_import_log.txt": Specify log file name.

Object_Access=Admin: If the import is not defined under Standard group then according to security this may be required.

VersionAbbrev="METADATA_PROD_IMPORT": Specify the version name where you want to import the hierarchy.

Rule / Script Performance: Condition on Sparse vs Dynamic Calc Member

Conditions in Business rules / Calc Script are quiet common but if not written correctly can take a very long time to execute (which may again depends on the database size and combinations it has to traverse).

Following is the classic example where when condition was placed on upper level of dynamically calculated dense dim member, it took substantial amount of time to execute and complete the script. Whereas when the same dynamic dense member data was copied to a temp stored member and then the stored member was compared, it executed within minutes.

Example Description:
Clear blocks depending on whether there exists any value in a mandatory assumptions and / or PAT.

Account, Period and View  are dense dimensions. The requirement is if there is no data in "Volume_Units" and "PAT" members at "Local" level, clear blocks from "Local","USD","AUD", and "NZD" members. Also, all upper levels of Account dimensions are dynamic calc members i.e. in our case "PAT" is dynamically calculated.

In the following code ,"PAT" is used in the condition and it took around 20 hours to complete.

//ESS_LOCALE English_UnitedStates.Latin1@Binary
SET UPDATECALC OFF;
SET AGGMISSG ON;

FIX (/*Year*/ "FY15",
/*Scenario*/ "Forecast",
/* Version */ "Working",
/* Currency */ "Local"
)
FIX(/*Customer*/  @Relative("Customer",0),
/*Product*/ @Relative("Product",0),
/*Entity*/ @Relative("Entity",0),
/*Currency*/ "USD","AUD","NZD",
/* Period*/  @Relative("YearTotal",0), "BegBalance"
)
"MTD"
(
IF(("Local"->"Volume_Units" + "Local"->"PAT" + 0 ) == 0)
"MTD" = #MISSING;
  ENDIF
)
ENDFIX
ENDFIX

The following code first copies "PAT" to a temporary stored member and then the stored member is used in the condition. This code just took around 20 minutes to complete.

//ESS_LOCALE English_UnitedStates.Latin1@Binary
SET UPDATECALC OFF;
SET AGGMISSG ON;

FIX (/*Year*/ "FY15",
/*Scenario*/ "Forecast",
/* Version */ "Working",
/* Currency */ "Local"
)
FIX(/*Customer*/  @Relative("Customer",0),
/*Product*/ @Relative("Product",0),
/*Entity*/ @Relative("Entity",0),
/* Period*/  @Relative("YearTotal",0), "BegBalance",
/*View*/ "MTD"
)
"TEMP" = "PAT";
ENDFIX

FIX(/*Customer*/  @Relative("Customer",0),
/*Product*/ @Relative("Product",0),
/*Entity*/ @Relative("Entity",0),
/*Currency*/ "USD","AUD","NZD",
/* Period*/  @Relative("YearTotal",0), "BegBalance"
)
"MTD"
(
IF(("Local"->"Volume_Units" + "Local"->"TEMP" + 0 ) == 0)
"MTD" = #MISSING;
  ENDIF
)
ENDFIX
ENDFIX

Loading Metadata: Improve Performance by using file compare utility

There are many reasons for an organisation to refresh metadata daily in Hyperion Planning and now a days you will generally have at least 1-2 hierarchies which could have 1000s of members. Not to mention if there are alternate hierarchies with shared members then outline load utility might take a while to delete and reload hierarchies.

There was a situation where my client had very tight schedule within which all the hierarchies needed to be reloaded and we were not able to fit that due to the source system’s variations in delivering the files.

So to resolve this issue and/or reduce the impact, it was decided to check every hierarchy whether there is any change compared to previous day’s file. If change exits then load it else skip and go to next. This was achieved by using Windows FileCompare utility and worked quiet well.

Following is the extract from the file which calls outline load utility to load hierarchies one after another.

LoadHierarchies.Bat (extract):

SET DimName=Account

SET InputFileName=%DimName%_dim.csv

SET LoadLogFile=FC_%DimName%_Load.log

CALL D:\script\Custom_FileCompare %InputFileName% %LoadLogFile%

if %CUSTOM_RESULT% EQU 0 goto :LOAD_Another

SET LoadLogFile=%LogFilePath%\%DimName%_Load.log

SET LoadExceptionFile=%LogFilePath%\%DimName%_Load.err

CALL Outlineload -f:%PasswordPath% /A:%App% /U:%UserName% /I:%DimFilePath%\%InputFileName% /D:%DimName% /L:%LoadLogFile% /X:%LoadExceptionFile%

:LOAD_Another

Other Code goes here...

In the following file “PreFilePath” is the path where previous day’s file(s) are placed while “DimFilePath” points to current or latest file which shall be loaded based on comparison.

Custom_FileCompare.bat:

SET CUSTOM_RESULT=0

SET CompFileName=%~1

SET FCLogFileName=%~2

SET File1=%PreFilePath%\%CompFileName%

SET File2=%DimFilePath%\%CompFileName%

FC %File1% %File2% > %LogFilePath%\%FCLogFileName%

SET CUSTOM_RESULT=%ERRORLEVEL%

if %CUSTOM_RESULT% EQU 0 ECHO Same files. Ignore this hierarchy load.