Want to know The Truth About CPM?

31 March 2016

The Compleat Idiot's Guide to PBCS No. 9 -- The A to Z of EPM Automate

The sum of more than its parts

In the on-premises Planning world we have access to a variety of EPM script languages:  Essbase’s MaxL (probably the most commonly used), Shared Services’ LCM utility, and the many Planning utilities.

In the PBCS world we have precisely none of that.  No MaxL, no LCM utility, no suite of Planning utilities.  How oh how oh how are we supposed to manage PBCS except through Workspace or the Simplified Interface?  The answer is EPM Automate.

Death of a thousand cuts aka Yet Another EPMA Rant by Cameron

I am happy to relate that the first four characters of EPM Automate do not in any way, manner, or form refer to some kind of zombie-like continuation of EPMA.  That product, as worthy as its genesis may have been (and I do actually think the idea of a single metadata editor across multiple products has quite a bit of merit), its execution has been an unending source of pain, confusion, and anguish since the first day I (and practically everyone else I’ve met) set eyes on it back in 2008.  Whew, that was quite a rant and whoever owns it at Oracle, I’m sorry, but your product has caused quite a few of my grey hairs.


Pointless but deeply satisfying rants aside, there are several problems wrt PBCS automation:  there’s no way to actually connect to the server OS – no terminal, no OS user id, no OS password, no view of the server file system, the lack of live connectivity means that automation has to run from a client machine, not the server itself, and lastly and most importantly the suite of automation tools we use on a regular basis doesn’t exist.  

What are we trying to do

If we are to consider a typical Planning administrative use case of a monthly load of actuals into a forecast scenario it will require the following steps:
  • Partially clearing a Plan Type of a forecast out month(s).
  • Loading metadata, pushing out a refresh
  • Loading data
  • Running a calculation uses,

Those steps in turn require:  
  • MaxL to clear via a calc script,
  • outlineload.cmd to load metadata,
  • REFRESHCUBE.cmd to refresh the Planning application
  • MaxL again to run an administrative calc script (yes, you could do this with a Calc Mgr rule but I’ve never seen it).

All of that can be done in EPM Automate and you’ll see this bit by bit over the next few weeks but first we (or at least I) need to understand some kind of mapping between the on premises and cloud world.

The universe of commands in the other tools is too large to manage or even comprehend – I’ve been working with Planning since 2001 and I’d guess at least 30% of the command line grammar is still terra incognita to me – I’ve never used SortMember or TaskListDefUtil or DeleteSharedDescendant – let alone all of the many, many, many commands in MaxL.  Instead what we’ll do is look at what EPM Automate can do and match it to the commands that exist in the on-premises world.  From that I’ll have another post on what it actually takes to follow that use case with a few surprises along the way.

With that, let’s do that compare and contrast exercise.

EPM Automate commands

A quick note about documentation – Oracle has done sterling work with their PBCS documentation and EPM Automate is no exception.  I encourage you to peruse Working with EPM Automate for Oracle Enterprise Performance Management Cloud in addition to the genius (ahem) below.

Applicable across all services

NB – These seem awfully PBCS-specific despite the documentation’s claim otherwise.  Having said that, as I’ve never used anything other than PBCS, I could yet again be 100% wrong.  Those who take glee in correcting me, please write a comment to this blog.

NB No. 2 – If I only RTFMed, I’d have seen that there’s a whole section on Account Reconciliation Cloud that does have commands of its own.  Okey dokey, that means these are global commands.

EPM Automate
On-premises equivalent
Most if not all utilities offer some kind of terse help.
Command help is what it is.  MaxL is pretty bad at this.
Planning has its own PasswordEncryption utility, MaxL has for ages had it’s approach.  
Whatever you do, if there’s a plain text file that is used to create the encrypted password, move it off the server/client to some secure place.  
Again, duplicated in Planning utilities and MaxL.  There’s but a single password for EPM Automate.
In on-premises it’s possible – likely even – to have different passwords for Planning and Essbase.  That isn’t the case with PBCS.  You’ll have to decide if that’s a good or bad thing.
A lot of the Planning utilities are run-from-a-command-line and there is no logout after the process is complete.
MaxL requires either a logout or exit command.  
Load metadata via outlineload.cmd, data via Essbase/MaxL.
The concept of uploading data or metadata to a folder for further processing doesn’t apply to on-premises.  One could look at OS file system folders to do the same functionality but they are not intrinsic to Planning.
DATAEXPORT BSO calc command, LCM utility
Again there is no concept of a folder in the on-premises world.  Up to three different utilities are required to duplicate EPM Automate’s functionality.
OS folders only.
You could use file system folders in the OS to sort of, kind of duplicate this functionality but really there is no direct analogue.
LCM utility, OS deletes.
Again, not a great match between the two worlds because of the folder construct.
LCM EPMExportAll utility.
This is a pretty tight match in functionality.
LCM EPMImportAll utility.
Again a pretty good match.
recreate –f
CubeRefresh utility, sort of
Cuberefresh can recreate the Essbase outline whilst getting rid of any hard-earned calc scripts, load rules, etc. (Why would anyone ever want to do this beyond the first go round?).  What it can’t do is delete the app as recreate –f can.
Call your friendly Planning Product Manager (not much chance of that really) or submit an enhancement request to Oracle Support.
Bounce the Planning service.
This is a complete PITA in on-premises because it will affect all Planning apps.

PBCS-specific commands

NB – Now we really are getting to functionality that only makes sense within PBCS.

Two notes

A note about the oft-referred to Jobs.  Jobs are actions that are used to support EPM Automate functions, e.g. a job to import data into an application.  This concept just doesn’t exist in on-premises.

As with on-premises Planning, Calculation Manager Business Rules are used to perform all calculations.  Essbase calc scripts are not available in PBCS.  There’s no reason to use MaxL to call a script as there are no scripts to call.

EPM Automate
MaxL, outlineload
The on-premises (and PBCS) native data load functionality is so brain-dead it beggars belief.  Really, it’s just awful.  At least the two are at parity.  :)
MaxL, outlineload
There are far fewer options in EPM Automate, e.g. there is no ability just refresh filters in EPM Automate.
The functionality is at parity, right down to ways to pass RTP values.
The functionality is at parity.
The functionality is at parity except outlineload.cmd can read from SQL.
The functionality is at parity except outlineload.cmd can read to SQL.

Data management specific commands

As you may have noted in my rant on importdata, to state that the native data load to Planning is flawed is to be excessively kind.  I can’t think of one system I’ve ever come across that uses it and I’ve been working with Planning since 2002.

To that end, and because FDMEE is the data integration tool of choice for EPM, PBCS uses FDMEE.  The EPM Automate commands reflect that approach.

EPMA Automate
EPM Automate cannot load metadata through the loaddata command.  FDMEE’s on-premises loadmetadata can.
On-premises requires a username and password in addition to the batch name.  This isn’t required in EPM Automate because batch execution takes place within an already-logged in session.

That’s just the beginning

You now have yr. obt. svt.’s take on EPM Automate.  I rather like it because I no longer have to mash together a bunch of tools to get a common Planning automation working.  I really like the fact that I don’t need to run the Planning utilities from the Planning server itself.  Explaining that to a very skeptical/hostile IT department is not one of my favorite Planning tasks.

I’ll walk through some, but not all, of these commands in my next post as I do a compare-and-contrast approach to my on-premises version of PBCS’ Vision application and the real PBCS deal.  

One note:  pity my younger, smarter, not-in-any-way-from-the-same-parents brother and fellow ACE Director Celvin, as I asked him to confirm a few statements.  I did warn you that I am a Compleat Idiot when it comes to PBCS but I thought it’d be nice if I didn’t spread misinformation.

Be seeing you.

No comments: