A lightweight, modular, and even better EPM on-premises backup

Time waits for no one

Time and tide waits for no man

A while ago, like five years ago, I wrote about a lightweight, modular, and better Essbase backup.  Funny, it doesn’t seem all that long ago and yet time passes swiftly.

That code works (and in fact I just put it in at a client) but it only covers the Essbase side of things.  As reluctant (Actually not as paying bills, funding retirement, and having some money left over for fun things all come from mostly Planning and doing assessments so alas not nearly as much Essbase as I’d like.) as I am to admit this, there is more to on-premises life than Essbase.  How then, does the rest of the world get backed up?  The answer is LCM.

More than just on-premises

While this post is going to only cover on-premises, there will be a second post on how to do this in PBCS/EPBCS.  Chris Rothermel contributed (again – the guy is generous) to that one (Yes, I have it, yes he gave it to me weeks ago, no I haven’t finished my part of it, yes I suck.) and while it is conceptually the same it has a few twists.  God willing and the Creek don’t rise, I’ll have it out next week.

A note about length

Er ma gawd this post is long – 38 pages in MS Word – and hence you might think that this process belies its title.  The actual code to perform this backup process is 41 lines of code including blank lines and comments.  Take that out and you’re down to 11 lines of script code.  That’s right:  11.  Is that lightweight enough for you?  I certainly hope so.  The unfortunate post length revolves around the one-time setup.  There are a few fair steps to do that but never fear, Gentle Reader, I lay it all out step by step as simply as I can.

The concept

As with my post from as near as can be five years ago, the gimmick in this backup process is to:

1. Limit the number of on disk backups to 7 (one for each day of the week, natch)
2. Use the number of the day of the week, e.g. 1 = Sunday, 2 = Monday, 3=Tuesday, etc. as that day’s rolling backup target
3. Use seven scheduled processes, one for each day of the week
4. Parameterize everything everywhere so it’s one snippet of code for all seven days.

The only real differences in this post vs. the Essbase-only post is that:

1. The backup can include each and every object for each and every product in your LCM universe.  Want to backup HFM?  FDMEE?  Financial Reports?  The world is your oyster.
2. The backup definition is defined through Shared Services once and only once.  Modifications to the process require modifying the LCM xmls (export and import).
3. Restoring objects requires working through the LCM utility or via a manual import to Shared Services.
4. The backup needs to run on the Shared Services server or wherever you can get the LCM backup utility to run.  I’ve never seen that anywhere other on the Shared Services server but I don’t get out much.
5. BSO Essbase restructuring does not take place; you’ll have to figure out another way to handle this.  It would be easy to modify my old post to solely do restructures or you could simply run them on an ad-hoc basis.  For performance, I suggest that you run it every day, particularly within the context of a Planning application.

That looks like a lot of differences but you’ll see as we dive into the code that it’s really quite similar and in some instances easier and more flexible than an Essbase-only backup.

Let’s get started

Backup locations

One more time through the folder/day of the week relationship.  Yes, Gentle Reader, you’re familiar with the days of the week and the number of each day of the week.  I include this just in case you get a bit wobbly on the relationship of one with the other.

Number	Day of the week
1	Sunday
2	Monday
3	Tuesday
4	Wednesday
5	Thursday
6	Friday
7	Saturday

Easy peasy no big deasy.  

Creating the LCM export

Wait, you say.  Do you?  Hopefully, else you aren’t paying attention.  Wait, why is a manually driven Shared Services export required?  Actually, the export itself is not needed but what is needed is the LCM export and import xml migration definition files.  This is a one time task.  The layout is documented but I think it’s far easier to do this manually.  Remember, except for export.xml and import.xml the results are disposable.

In Shared Services

For this example I created an export of ASOSamp.Sample, Sample.Basic, and the Vision Planning application I migrated from PBCS to on-premises.  I boldface the latter because some people think it’s a Plain Jane migration to the cloud.  Nope, as ever your Yr. Most Hmbl. & Obt. Svt. has gone against the grain.

Go to Shared Services

Within each technology group select the applications to be backed up.  Remember, more than one product can be selected at a time.  Simply go from one to the other in Shared Serices.  When you’ve picked the last application then and only then click on Export.

Good ‘ol Sample.Basic

Don’t believe Jason Jones’ comment about TBC going tits up.  It is alive and well.

Note that for this database I am not exporting data.  It is being excluded for demonstration purposes.  Also, if you do keep the Essbase export process it’s redundant.  You’re going to have to think about whether you want all Essbase restores to go through Shared Services or if you prefer to do simple restores of Essbase file objects.  Also, I’d note that the exports in Shared Services are single threaded whereas Essbase’s MaxL command can and should be multithreaded.  Lastly, as previously noted Shared Services exports do not support restructures.  Choose wisely.

ASOSamp

Here’s the ASO version of Sample.Basic.  I am exporting the data in this one to show you how it’s manifested.

Vision

Here’s that PBCS->on-premises Vision application.

Calculation Manager

Planning applications contain deployed Business Rules but you’ll want the rules you can actually edit as well.

Ignore FINPLAN and SampApp1.  My renowned and well-known laziness comes to the fore and I can’t be bothered to retake this shot.  

Run the export

We’re only going to do this once and just to get the export.xml and import.xml settings files.

Working, working, working

All done.

Download to disk.

Or simply go to the Shared Services import_export folder.  Your choice.

The file objects other than export.xml and import.xml are surplus to requirements.  

Migration definitions

Wow, nine pages of instructions to get to the two files needed for this backup.  Ugh.  There’s worse (or better) to come.

Export.xml

From a get-it-out-of-the-system perspective, this is all that’s needed.  Getting it back in will require the almost identical import.xml.

Password

The export process does not include a username/password although the node for them exists.

There is, super unfortunately and super annoyingly, an issue with regards to the password:  said password (and username) gets reset each and every time the process is run.  Thank Peter Nitchke for pointing this out in his post on a different way to back up LCM objects.  Yes, I’m sort of covering blazed trails but surely imitation is the sincerest form of flattery and I do think I have a slightly different take on it.

The first time round

Assuming that you have created a suitably named backup folder in the first day and have copied export.xml to that folder:

NB – I named the target folder (and the the name of the backup to be restored in Shared Services if required) as LCM_FPCMPSSSFR to stand for FP = Financial Planning, CM = Calculation Manager, PS = Product Sales, SS = Shared Services, FR = Financial Reports.  Name this folder however you like; it will need to be modified in the LCMBackup.cmd file.

Running it from the command line

To get the suitably provisioned username/password to work, you’ll need to run the Shared Services command line tool utility.bat just once:

Use Notepad++ to capture the password

Before this runs, make sure you have that c:\automation\LCMBackup\1\LCM_FPCMPSSSFR\export.xml file open in Notepad++.  The editor does not have a lock on the file and will update export.xml.  Select Yes to have the file updated.

You’ll be promted to update the file at the end of the backup process.  Don’t as that will erase the updated username/password.  Btw, the fact that the tool does this is insanity but there it is.

After you click “No” (this is the version of the file with the encrypted username/password), save the file to C:\Automation\LCMBackup\Code\Export_with_credentials.xml.

Modify import.xml with the user/password from the export xml.  Copy it to c:\automation\LCMBackup\Code\Import_with_credentials.xml.  

NB – Import.xml won’t be required for interactive import of objects via Shared Services but is required if you are a daring sort of chap and want to run the import from the command line.

The finished product

Here it is in the backup folder with each technology type (Calculation Manager) or database/app (Essbase & Planning) folder all present and correct.

Calculation Manager

Want a rule?  See a rule.  Enjoy.

ASOSamp

Note that data is exported to the root Sample database folder.  This is interesting in light of the fact that the exported data is not in the tablespace folders.

The Tablespace folders do not contain .dat or exported files but instead contain the properties of the tablespace(s).

Sample

Good ol’ Sample.Basic is with us, this time with calc scripts on offer.

Are those really calc scripts?  Yep.

Vision

Are you bored yet with the seemingly regularity and comprehensiveness of the export?  Hopefully so else this process isn’t fit for purpose.

Note that Planning data location differs from Essbase.  Of course Planning != Essbase so it’s not surprising that data exports are in a different location.

LCMBackup.cmd

This is the whole thing.  Yeah, yeah, no error handling.  So sue me.  This is a blog, not the tool I’m putting in at your site.   Also, this stripped down code sample keeps everything easy to see.

Code so you can copy it

Don’t forget that the code wraps to the page. See the above screenshot for the correct breaks.

ECHO OFF

REM  Purpose:        Daily LCM processing

REM  Written by:    Cameron Lackpour

REM  Modified:      15 February 2017

REM  Notes:        --    Run the LCM backup for the current day

REM                --    The encrypted username and password must exist in a copy of Export.xml

REM                --    Import.xml is also required to allow an import of the LCM exports

REM                Usage:

REM                    DailyLCMBackup.cmd 1 g:

REM                    Where:    1 = folder generation

REM                    g: = backup drive

REM Make the variables pretty

SET Gen=%1

SET Drive=%2

SET LCMUtility=%2\Oracle\Middleware\user_projects\epmsystem1\bin\utility.bat

REM Clear out the target directory, removing all subfolders

REM Delete all files

REM    /F    Force deleting of read-only files.

REM    /S    Delete specified files from all subdirectories.

REM    /Q    Quiet mode, do not ask if ok to delete on global wildcard

del %Drive%\automation\lcmbackup\%Gen% /F /S /Q

REM Remove all subfolders

REM    /S    Removes all directories and files in the specified directory in addition to the directory itself.  Used to remove a directory tree.

REM    /Q    Quiet mode, do not ask if ok to remove a directory tree with /S

rd /S /Q %Drive%\automation\lcmbackup\%Gen%\LCM_FPCMPSSSFR

   

REM    Recreate the target directory   

md %Drive%\automation\lcmbackup\%Gen%\LCM_FPCMPSSSFR    

   

REM    Copy Export.xml file with credentials (username and password) to daily folder

COPY Export_with_credentials.xml %drive%\automation\lcmbackup\%Gen%\LCM_FPCMPSSSFR\Export.xml

REM    Copy Import.xml file

COPY Import_with_credentials.xml %drive%\automation\lcmbackup\%Gen%\LCM_FPCMPSSSFR\Import.xml

REM  Perform LCM backup

CALL %LCMUtility% %Drive%\automation\lcmbackup\%Gen%\LCM_FPCMPSSSFR\Export.xml >%Drive%\automation\lcmbackup\%Gen%\DailyLCMBackup.log

EXIT

Note the switches for del and rd.  It’s import to get rid of the files in the subfolders first before removing said subfolders.  Ain’t DOS grand?  This is necessary to make sure that deleted objects, e.g. calc script that was directly removed from Sample.Basic doesn’t persist.  I like to remove the folders as well just in case the export.xml files are modified.

Running LCMBackup.cmd

Again, easy peasy no big deasey.  

The syntax is:  lcmbackup.cmd generation drivename:

The example below is:  lcmbackup.cmd 1 c:

It’s all a bit difficult to follow as there are many objects to get deleted.  Happily there’s a log file that captures everything.

Breaking it down by numbered section

1. Don’t tell me how, tell me why.  This is what I consider to be the bare minimum header.  Also, for the love of Mike, when you inevitably modify this code, please, please, please put your name alongside the modify date.  I’ve had people ask me, “Did you really do X?”.  Maybe but who can tell.  I like to take the blame when I deserve it but only then.
2. Set parameters and get rid of all of the files below all of the subfolders.  Note again the high level of comments.  Some people live in “DOS” and so this is all well known.  I know the functions are there but I’ll be damned if I can recall them.
3. Remove all of the folders.  Re the comments, Ibid.
4. Remember that nonsense about the passwords?  Here’s where that oh-so-annoying deletion is overlaid.  Stupid but there it is.
5. Actually run the LCM export utility.  Huzzah, we’re done.

Automation

That’s all well and good, but what about that relationship between days of the week and their ordinal order?  Remember that parameterization that drive the generation number?  This is where it comes into play.

What I’m going to show is Windows but this could just as easily done in Linux’s chron.

Here’s my Windows 2008 server (soon to be 2012 but only for the next VM install) Task Scheduler.  Huh, I seem to have Flash installed on a server OS.  Gee thanks, Calculation Manager, for making me have one of the most hacked products ever on my server.  Get rid of that abomination Oracle EPM team.  Please.

Seven days for seven backups

Rant aside, to make this code a rolling create seven backup tasks tied to each day of the week with that parameterized backup code and that magical seven day rolling backup is good to go.

Sunday step by step

I’ve typed enough.  Let’s let the pictures tell their 1,000 words.

Create a new process

Set it to run every Sunday

Path to LCMBackup.cmd and provide parameters

You’ll be logged off so provide a password

There we go

Sunday, Monday, or always

Let’s not do that manually six more times and instead export to xml, modify, and import right back in.  Believe you me, it’s less tedious this way.

Export

Export file

Change DaysOfWeek and the appropriate numeric parameter

Monday

All together now

Modifying the LCM definition

What if you want to expand the scope of the export?  You could go through the process in Shared Services if you like or could simply perform a copy and paste.  In this case I’m going to show how to also backup Demo.Basic.

Simply copy the Sample.Basic task node.

Paste and modify to reference Demo.Basic.  Don’t forget to point to the right filePath.

Lather, rinse, repeat

Ta da, seven days of bliss after you’ve imported each xml definition.  This does seem to be the blog post all about XML but it is such a good idea and oh so useful.

Restore

This backup process is like life insurance:  you hope you’ll never need to use it.  Of course that term life policy has the note of finality where this does not.  No matter, the analogy sort of holds.

Again, we’re at 35 pages and 2,500 words.  Let’s let the pictures tell the story of restoring Saturday’s backup.  What’s that you say?  No one in your firm works on Saturday?  You’re in luck.

I suppose I don’t need to show you how to copy and paste but in the spirit of completeness…

Copy the Saturday backup

Paste within the import_export folder

Yes, you could upload this as well through Shared Services.

There it is.

Select your object(s)

Import

All is restored

R&R

Yep, it’s time to relax.  It’s done.  Follow these lightweight, modular, and better EPM on-premises backup and never worry about backups ever again.

One other note – your IT department is of course backing up all file objects so when you need to walk things back a month or three it’ll be a moderately simple task to restore that business rule of rare genius.  Right?  Right.

Be seeing you.