LCM Backups for Fun and Profit…

G’day,

So – from my first few posts you’ll see I’m exceptionally lazy efficient when it comes to back end processes. If it can be scripted/automated, then I’ll generally do that rather than keep a manual step.

Err. Where was I.

Right – as part of a I’ve-got-a-few-hours-spare-this-week-piece, I rewrote an overnight process. Primary goals for the rewrite were to remove hard coding where possible, segment the overnight process out to discrete tasks\components and add a hefty backup component. Obviously the Essbase side of a backup process is reasonably simple and flexible. Database copies\dataexports\file system copies – you’ve got a plethora of options available.

What about the ‘rest of it‘?

In addition to the standard relational backups, I decided to see if I could automate an LCM export of the Planning application side – the theory being that between the LCM backup and the Essbase backup I could recreate any combination of an app.
How does this all work then?
LCM is quite an elegant tool – certainly compared to the earlier alternatives (I’ll give you a moment while you recover from the nightmarish memory of dataform migration pre v9!) Basically you first define a migration framework of the artifacts you wish to either export or import and then trigger it.

So – a very basic export framework is shown below.

This is going to export the entire application (Finance), including the Dataforms, Dimensions, Global Settings, Security & Relational data. I’m building this on 11.1.2.1 – hence it’s not going to include the BSO Essbase data – however later versions can also include a data export.

Note: If you’ve built a fairly standard Planning application this is going to include pretty much everything you need, including essbase calc scripts, rules files and partitions. However! If you’ve used HSP_UDF to define member formulas directly in essbase, that’s not going to be included and you’ll need to backup the otl file separately. Don’t say I didn’t warn you.

If there are items you don’t want to export (Security for instance) you can simply edit the lines out of this file directly. That being said, a simpler method of building this is to define and run an LCM export of the application with exactly what you’re chasing. Then download and open the export folder, and at the very top of the folder structure should be the Export.xml file.

One final thing to notice in the Export (and Import Files) is that the Username and Passwords are cleared out every time they are run. This is important later when we try to automate this step.

Introducing the…UTILITY
Now you’ve got your export definition sorted – the next step is to look at the automation steps. The LCM outline extraction utility (named, entertainingly, Utility) is held in the Foundation bin folder. On a standard install it should be Oracle\Middleware\user_projects\FOUNDATION1\bin.

To save you looking through the detail of that batch script, the string required is really basic:
Outline.bat ExportFolder\Export.xml

Unfortunately, this is one of those scripts that cannot be called directly from an external server (it internally attempts to parse the drive and folder locations), so you’ll need to use psexec to call it across servers.

You’ll notice that in the string above you don’t have to define an output folder, just the location of the output. Sadly this isn’t because you’ve got some flexibility to add that to the XML file, or alternatively set an option somewhere in shared services, instead it’s because the LCM export will be directly to the filesystem location of the Export.xml file. Yep – wherever you’ve stored it is where the data will end up. In an upside, that can be across mapped network drives, so you store the files somewhere centrally rather than clogging up your foundation server!

Hooking it all together

So – obviously with all of these back end scripting languages available we should choose something exoti….nah. This time we’re sticking with DOS batch. They didn’t call it the Quick and Dirty Operating system for nothing!

Okay, so we’ve got to handle the following items:

• Usernames and passwords disappearing at runtime
• Needing PSExec to run the utility (and requiring write access back across a network share)
• Needing some steps to zip it all up and maintain only an appropriate backup
• Having it all parameter based and scalable for multiple apps

Taking it from the top
Firstly, folder system setup:

I’ve created a nice simple structure. The folder LCM_Exports is empty and will be built up in the batch code. In LCM_XML I’ve got a copy of the XML export files, updated with the username and password (encrypted works, but you can also type them unencrypted). They are renamed, prefixed with the name of the application ie:
LCM_XML\Finance_Export.xml

Now the code:

Nice and simple – we’re calling the name of the application as per the naming convention in the LCM_XML folder. In addition the variables include a file system user and password to use with PSExec to make sure the Utility can write back to wherever you had the export.xml saved. If you’re running it directly from your foundation server and saving the LCM Exports  you should be able to skip this – but it’s not going to hurt.

Where the magic starts. Here the batch code removes and rebuilds the top level folder name under LCM_Export using the %App% variable name, and copies in the export file, renaming it to Export.xml. The final step is to trigger the Utility using PSexec.

After the Utility is finished, 7zip is used to create a datestamped version of the LCM Export. As discussed earlier, I’m using 11.1.2.1 in this example, so I added compression it up in this step because by default it comes down uncompressed. Later versions (11.1.2.2+) are already compressed (or are they??), so compressing them further would likely only be useful to update the naming convention. The final step in this section will delete any of the zipped that are older than 3 days, but this could be updated to 30 (or more!) – just make sure you keep on top of your storage space requirements.

Final steps, some basic cleanup and error handling – you could also add some error handling for messages directly back from the LCM Utility which will return the following error codes.

REM - Return values -
REM - 1) 0 - Success
REM - 2) 1 - Failure in processing input parameters passed to CLU
REM - 3) 2 - Failure in CLU execution

 

Conclusion
And that’s it! A scripted and fully-automated way to take backups of LCM files. This can obviously be extended to other apps or even other artifacts (think Reporting\Shared Services\EPMA) by creating individual export.xml files, dumping them into the LCM_XML folder and simply calling them.

For bonus points – the next step is automating the import side – thus giving you the ability to script a true sandbox\QA environment every night. A topic for another post!

Full version of the code available below.

:: Name: Hyperion Planning Backup Script
:: Purpose: Perform LCM Backups of the Planning applications
:: Author: Peter Nitschke - M-Power Solutions
:: Change Control
:: When Who What
:: 19/07/2014 PN Created

:: INSTRUCTIONS
:: Script is built to backup Planning LCM Extracts
:: Script will copy the export XML files from the LCM_XML Folder to LCM_Exports Folder and run the LCM Extract Utility
:: Due to a UNC limitation in the LCM utility, requires using psexec to run remotely

:: To create LCM Export XML files, trigger a standard LCM Planning Export, this creates an Export.xml file in the defined folder
:: This export.xml file needs to have the User Name and Password updated and be renamed as follows below

:: NAMING CONVENTION
:: This script will look for files of the following format in the LCM_XML Folder
:: %AppName%_Export.xml
:: It will then copy them tp the following folder and rename before triggering
:: Backup\LCM_Export\%AppName%\Export.xml

:: Similar to the other overnight scripts, this script requires run time parameters
:: Variables
:: $1 = fsUser
:: $2 = fspassword
:: $3 = Env
:: $4 = Application

SetLocal
echo off

:: If no parameters exist, fail and return an error code of 1
if [%1]==[] goto NoParam

:: Define Runtime variables
set fsuser=%1
set fspassword=%2
set essbaseserver=hyp-es-%3
set webserver=hyp-web-%3
Set app=%4

:: Set the Batch variables
for /F "tokens=1-4 delims=/ " %%a in ('date /t') do (set datestamp=%%d%%c%%b)
Set LCMUtil=d:\Oracle\Middleware\user_projects\FOUNDATION1\bin\Utility.bat
set backupfolder=\\%essbaseserver%\d$\overnight\backup

:: Remove and recreate the LCM Exports Folder
RD ..\Backup\LCM_Exports\%app%_LCM\ /s /q
MD ..\Backup\LCM_Exports\%app%_LCM\

:: Copy the %AppName%_Export.xml file and rename it to Export.xml
Copy "..\Backup\LCM_XML\%app%_Export.xml" "..\Backup\LCM_Exports\%app%_LCM\Export.xml"

:: Trigger an LCM Export
Call "psexec.exe" /accepteula \\%webserver% -u %fsuser% -p %fspassword% %LCMUtil% "%backupfolder%\LCM_Exports\%app%_LCM\Export.xml"

:: Zipping Process
:: Delete the backup file if it already exists - shouldn't happen unless script is run twice
IF exist ..\Backup\AppBackups\%Datestamp%_%App%_Backup.7z del ..\Backup\AppBackups\%Datestamp%_%App%_Backup.7z

:: Run the 7z command line
:: Add the LCM Export
7za.exe a -t7z -r ..\Backup\AppBackups\%Datestamp%_%App%_Backup.7z ..\Backup\LCM_Exports\%app%_LCM -m0=LZMA2 -mx1 -mmt=8 -md=64k -mfb=16

:: Delete any of the 7zip files that are older than 3 days
:: Can be changed, but watch the space requirements!
forfiles /p ..\Backup\AppBackups\ /m *.7z /d -3 /c "cmd /c DEL /Q @file"

ENDLOCAL
EXIT /B

:NoParam
@echo Usage: Requires following parameters to run
Echo Runtime variables: "fsuser" "fspassword" "Env" "app"
ENDLOCAL
Pause
exit /B 1