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.

#NoAccess

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
Comment
help
Most if not all utilities offer some kind of terse help.
Command help is what it is.  MaxL is pretty bad at this.
encrypt
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.  
login
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.
logout
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.  
uploadfile
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.
downloadfile
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.
listfiles
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.
deletefile
LCM utility, OS deletes.
Again, not a great match between the two worlds because of the folder construct.
exportsnapshot
LCM EPMExportAll utility.
This is a pretty tight match in functionality.
importsnapshot
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.
feedback
N/A
Call your friendly Planning Product Manager (not much chance of that really) or submit an enhancement request to Oracle Support.
resetservice
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
On-premises
Comments
importdata
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.  :)
exportdata
MaxL, outlineload
Ibid.
refreshcube
CubeRefresh
There are far fewer options in EPM Automate, e.g. there is no ability just refresh filters in EPM Automate.
runbusinessrule
CalcMgrCmdLineLauncher
The functionality is at parity, right down to ways to pass RTP values.
runplantypemap
PushData
The functionality is at parity.
importmetadata
Outlineload
The functionality is at parity except outlineload.cmd can read from SQL.
exportmetadata
OutlineLoad
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
On-premises
Comments
rundatarule
loaddata
EPM Automate cannot load metadata through the loaddata command.  FDMEE’s on-premises loadmetadata can.
runbatch
Runbatch
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.

22 March 2016

The Compleat Idiot's Guide to PBCS, No. 8 -- Supported Smart Forms Excel Functions

What’s in, what’s out, and what’s not official (that would be all of it)

I’ve done this before, and I’m doing this again:  this blog is (like many if not all of my posts) information that is not in any way supported by Oracle.  Do not go to Oracle whining about, “Cameron said this worked, but it doesn’t, so I hate you Oracle, blah, blah, blah, blah, blah,” as I am telling you that what you read below is unsupported, unofficial, incomplete, tied to the PBCS release of today, 22 March 2016, unknown to anyone at @oracle.com, etc. In other words, enjoy and maybe use the below and don’t have a conniption if it all goes sideways on you.

You Have Been Warned.

The warning is over, here’s the cool stuff

Could whining actually be an effective approach?  Maybe.

In my last PBCS post on Smart Forms in Smart View, I whined the following:
“Where exactly does this ability to convert Excel formulae into a Smart Form begin and end?  There a lots and lots and lots of formula functions in Excel – that’s one of its many strengths.  Unfortunately, not all formula functions are supported and if there’s a list anywhere of what works and what doesn’t I was unable to find it.”

And then went on to whine:
“Bugger.  So that’s at least one formula that isn’t supported via Smart Form.  It sure would be nice to have that list of supported/unsupported.”

Yes, I am a whiner, to the detriment and annoyance of all who know me.  And yes, whining seems to be a theme in this post.  Perhaps I have done it recently and feel guilty about it?  The Psychology of Cameron is a frightening thing.

Now to the cool stuff

How about that currently supported/but use at your own risk list of functions?  Ask and ye shall receive.
Very, very, very nice and an awful lot of them to boot.

 Function
Category
ACCRINT
Financial
ACCRINTM
Financial
AMORDEGRC
Financial
AMORLINC
Financial
COUPDAYBS
Financial
COUPDAYS
Financial
COUPDAYSNC
Financial
COUPNCD
Financial
COUPNUM
Financial
COUPPCD
Financial
CUMIPMT
Financial
CUMPRINC
Financial
DB
Financial
DDB
Financial
DISC
Financial
DOLLARDE
Financial
DOLLARFR
Financial
DURATION
Financial
EFFECT
Financial
FV
Financial
FVSCHEDULE
Financial
INTRATE
Financial
IPMT
Financial
IRR
Financial
ISPMT
Financial
MDURATION
Financial
MIRR
Financial
NOMINAL
Financial
NPER
Financial
NPV
Financial
PMT
Financial
PPMT
Financial
PRICE
Financial
PRICEDISC
Financial
PRICEMAT
Financial
PV
Financial
RATE
Financial
RECEIVED
Financial
SLN
Financial
SYD
Financial
TBILLEQ
Financial
TBILLPRICE
Financial
TBILLYIELD
Financial
XIRR
Financial
XNPV
Financial
YIELD
Financial
YIELDDISC
Financial
YIELDMAT
Financial
ISERR
Information
ISERROR
Information
AND
Logical
IF
Logical
NOT
Logical
OR
Logical
ABS
Math and trigonometry
MOD
Math and trigonometry
PI
Math and trigonometry
PRODUCT
Math and trigonometry
ROUND
Math and trigonometry
SUM
Math and trigonometry
TRUNC
Math and trigonometry
AVERAGE
Statistical
AVERAGEA
Statistical
COUNT
Statistical
COUNTA
Statistical
MAX
Statistical
MIN
Statistical
DATE
Date and time
DAY
Date and time
DAYS360
Date and time
EDATE
Date and time
EOMONTH
Date and time
HOUR
Date and time
MINUTE
Date and time
MONTH
Date and time
NETWORKDAYS
Date and time
NOW
Date and time
SECOND
Date and time
TIME
Date and time
TODAY
Date and time
WEEKDAY
Date and time
WEEKNUM
Date and time
WORKDAY
Date and time
YEAR
Date and time
YEARFRAC
Date and time
FALSE
Logical
TRUE
Logical
ACOS
Math and trigonometry
ACOSH
Math and trigonometry
ASIN
Math and trigonometry
ASINH
Math and trigonometry
ATAN
Math and trigonometry
ATAN2
Math and trigonometry
ATANH
Math and trigonometry
CEILING
Math and trigonometry
COMBIN
Math and trigonometry
COS
Math and trigonometry
COSH
Math and trigonometry
DEGREES
Math and trigonometry
EVEN
Math and trigonometry
EXP
Math and trigonometry
FACT
Math and trigonometry
FACTDOUBLE
Math and trigonometry
FLOOR
Math and trigonometry
GCD
Math and trigonometry
INT
Math and trigonometry
LCM
Math and trigonometry
LN
Math and trigonometry
LOG
Math and trigonometry
LOG10
Math and trigonometry
MROUND
Math and trigonometry
MULTINOMIAL
Math and trigonometry
ODD
Math and trigonometry
POWER
Math and trigonometry
QUOTIENT
Math and trigonometry
RADIANS
Math and trigonometry
RAND
Math and trigonometry
RANDBETWEEN
Math and trigonometry
ROUNDDOWN
Math and trigonometry
ROUNDUP
Math and trigonometry
SIGN
Math and trigonometry
SIN
Math and trigonometry
SINH
Math and trigonometry
SQRT
Math and trigonometry
SQRTPI
Math and trigonometry
SUMSQ
Math and trigonometry
TAN
Math and trigonometry
TANH
Math and trigonometry

Awesome is the only word that describes this function list.  Note that it’s 128 functions long.  Think of the Excel formulae you could write, think of the rich functionality in Excel, think of how many of these functions are not supported in BSO and then think about how many are not supported in MDX.  

Conclusion and a whine

And that Gentle Reader, is why Smart Forms are 100 (128?) times better than traditional form formulae.  Think about what you could write as one offs in a form.  No more creating a member in the Account dimension that only gets used once and is deadwood otherwise.   Instead, create the base members you need to perform the calculations and use the quite considerable power of Excel to do the heavy lifting.  Remember that Smart Forms are easily made into ad hoc forms thus keeping the calculated member in play.  Also remember that these functions are available in the Simplified Interface.  Happy times indeed.

One last whine:  when oh when oh when will we see this in on-premises?  I love, love, love the idea of Oracle writing this stuff as it is top drawer functionality but thus far it is for PBCS only.  Please Oracle, for the balance of customers who are not on The Cloud, bring this to on-premises so all of your customers can benefit from this functionality.  

Be seeing you.