Yes, finally. You, oh Gentle Reader, and me, yr. obt. svt., are at that stage of finally tying together the different threads needed to perform the ostensibly simple monthly load of actuals into a Planning application. As I’ve covered functionality I’ve made an effort to illustrate how to perform a given task in both PBCS and on-premises. Arguably, that approach made the posts longer than perhaps needed but I, and I suspect you if you’re following along in this series, come to PBCS from an on-premises perspective and I wanted to highlight the differences. No matter, it has been quite a bit of fun as well as hopefully educational.
And there is quite a bit that is different: Jobs, Inbox/Outbox, Application Management, and most especially the epmautomate utility as will be covered in this post. If you need a refresher or simply have far too much free time on your hands, I encourage you to peruse the below posts:
There’s quite a bit of content there, or at least awful lot of screenshots. There are 200+ screenshots on just my contributions to this particular Compleat Idiot theme of monthly actuals. That’s not counting my other posts as well as skipping over the contributions of Philip Hulsebosch and Chris Rothermel. Good grief that’s a lot of PBCS content. Some of it may even be correct, most likely the stuff Philip and Chris wrote.
It seems so simple
It is. Let’s recap what needs to happen:
- Current application state is backed up to the local drive
- New metadata is loaded and the Planning application is refreshed
- The current month’s actual data is cleared
- Current month data is loaded
- The current month’s data is aggregated
And yet it is so different
The actions and concepts are exactly the same in on-premises and PBCS. It’s those damn details that make it interesting.
When it comes to automation, the real difference is the number and type of utilities that are needed to perform identical actions in both platforms.
Step the first – backup current application state
I showed in Compleat Idiot No. 13 how to use on-premises LCM and PBCS’ Application Management to manually back up applications and while the UI approach is largely the same the automation tasks are not.
As with PBCS, one must first create an on-premises LCM export definition via the Shared Services console. It’s a fair number of clicks but in the end it all makes sense:
LCM will write out the contents of the exported artefacts in one shot.
If you have access to the Shared Services’ import_export folder, the exported objects are there for the taking:
Failing that, download the File System object from Shared Services and then look inside the zip file:
Whatever the approach, a file called Export.xml is created – this is the file definition of the export process.
Export.xml describes tasks which in turn define what is to be exported when the LCM utility is used:
The quintessence of eponymous
It isn’t often that EPM makes me laugh but this tickles my sense of humor: what’s the logical name for LCM’s command line utility? Why, “utility” of course. Thus utility.bat exists and you can read all about it in the Lifecycle Management Guide.
There are two things you must keep in mind, both of them fairly maddening.
Utility.bat (I am actually falling out of love with that name because if everything else I use at the command line in EPM-land is a utility, and they have names like outlineload.cmd and refreshcube.cmd thus I actually have a hint as to what they must do, a utility named “utility” doesn’t have a lot of utility. Groan. Really, the name is confusing.) must be run on the Shared Services server. Actually, strike that – it has to be installed in some gawdawful way that is far beyond Infrastructure-Allergic Cameron’s abilities. On my all-in-one VM, it’s not a big deal but it might be in the real world. Check with someone who has a clue, IOW not me.
On initial run, utility.bat will ask for a correctly provisioned Shared Services username and password, which it will use to encrypt those fields in Export.xml. And then it…deletes the file with the just-input and just-encrypted password on execution. Double, no triple, no quadruple bugger. You can catch it, just, in an editor like Notepad++ or TextPad if the xml file is already open and then not update the file when the editor catches the file change. And then you make a copy of that file and thereafter copy that copy on top of the copy actually used. OMG, it’s too awful to even type – just read Peter Nitscke aka @EssbaseDownUnder’s excellent post on it. I thought I was losing what little mind I had left when this happened to me. I think we can all agree that ain’t no feature.
Here’s the file somehow encrypted:
And here’s what you end up with right back at the beginning:
Maddening I tell you. Maddening.
The answer is as Peter noted: simply create a copy of the encrypted username/password file (btw, you can’t pass parameters to utility.bat – do it the utility’s way or do it all by hand) and copy that on top. It doesn’t have to be sophisticated:
Did you enjoy the additional mini rant in the comments? And how annoyed I was when I wrote it, i.e. the somewhat tortured grammar? See, it is worth writing those things out if for the amusement factor alone.
Calling the code itself is then easy peasy no big deasy:
This will write the export content to the Export.xml (remember, it’s really the just-copied-over Export2.xml but hey, who’s counting?) location:
I am happy to report that PBCS’ approach is much, much, much faster both to code and to describe.
Remembering that just like on-premises’ backup a manual backup must be created to act as a template for LCMing (or App Managing) the application objects.
Step 1 – cause a complete backup of the Vision application:
Step 2 – download that backup to the local disk
And here you go in zipped format (on-premises creates uncompressed folders by tool although the ultimate content is the same):
Step the second – load metadata
On-premises is easier this time, thankfully, as it couldn’t get a whole lot harder. There are eleventy billion possible switches to that utility, most of which I have thankfully never used.
Here’s the same file I used back in Compleat Idiot’s Guide No. 10:
Outlineload.cmd must be run from the Planning bin folder. Good luck in gettting IT to go along with that when it comes to automation. It has always been one of my more painful conversations during implementations.
If you can get past that (and really, what other choice is there), running it is a doddle. I’m using Windows environment variables to make my code a wee bit easier to read but otherwise it’s 100% stock:
You’ll note that there’s an encrypted password file which, unlike LCM, does not get deleted on run. Huzzah!
There’s output, lots of output from this command. Chatty is one way of describing it although I suppose the proper Comp Sci way of describing it is “verbose”.
Here’s the output piped to a log file:
There’s also an error file which, whether it has any readable content or not, always exists, and always has at least three bytes of content, thus making it imperative that any automation process not only look for that file but look inside that file to see if something went badly or not. At least it keeps us employed…
I could have used the /C switch in outlineload.cmd to force a refresh but I like to spell everything out so I’ve used refreshcube.cmd. It, like outlineload.cmd is easy to invoke:
Would you believe it isn’t quite as easy? There are certainly more steps but in fact they’re quite a bit simpler:
It’s just four steps:
- Make sure the file NewProduct.zip isn’t in the InBox
- Upload the file NewProduct.zip to the InBox
- Use the job ImportNewProduct to load NewProduct.zip. Remember that NewProduct.zip is the same file that the outlineload.cmd utility uses.
- Refresh the database.
I’ve gone into the overloading of zip files in Jobs for metadata (and a bit later data) before. I encourage you to read Compleat Idiot No. 10 for all of the gen on how to do that.
Once the file has been uploaded to the Job called “ImportNewProduct”,
Just a note about epmautomate.cmd’s logging – it’s errorlevel return codes or nothing:
Operation completed without errors.
Operation failed to execute because of an error
Operation is terminated by the user.
Service is not available.
Invalid user name, password or identity domain.
Service is not available.
NB – The above is cribbed directly from the docs – I really do encourage you to read (and hey, steal give full attribution to sources) and learn from them – as they have improved quite a bit from the on-premises version.
Step the third – clear out the current month Actual data
We have yet another utility on offer: calcmgrcmdlinelauncher.cmd Isn’t this fun? It really is an example of how on-premises has evolved. Remember that before 220.127.116.11 (18.104.22.168?) Hyperion Business Rules were written in EAS. If you’ve really been around, business rules had its own horrific desktop tool. Ah, the bad old days that are best forgotten.
In any case, there is a command line tool to launch business rules. I’m not using rtp files or any of the other functionality. See the docs here for more information.
NB – If you’ve ever wondered why I put in so many references to the documentation it is both because I am lazy (yes, I have been called that and haven’t quite decided if I’m flattered or insulted; I’m inclined towards the former) and because Oracle do a better job with the details than I ever could.
In any case, here are the parameters: password, application, username, plan type, rule name.
Unfortunately, there is no echoing of status. Within the context of a extraordinarily simple batch script sans error checking I threw in an echo statement to at least tell me that it’s running. Before you get excited about this lack of rigor, remember that this is a blog post, not an actual implementation.
It’s good old epmautomate.cmd to the fore:
Again, additional parameters are possible. See the PBCS documentation for details on how to parameterize the command.
Step the fourth – load data
As I wrote in Compleat Idiot Nos. 9 and 11, Planning’s native file format is brain dead. Yes, I can sort of see the point when loading text or even Smart Lists (see, Peter, I do sometimes listen), but for the purposes of this series the purposes of most data load use cases, it’s beyond useless.
And, if PBCS supports the Essbase data file format, and on-premises’ outlineload.cmd doesn’t, what’s a geek to do? The answer is spelt M-a-x-L.
The code to call MaxL is simple:
Here’s the MaxL code as called by the above batch script. You now know the username and password to my 22.214.171.124 VM. Don’t do this at home.
Did you spot what’s missing? There’s no load rule. None. In that way it’s the same as PBCS loading Essbase’s data format.
The data file fully describes the outline layout.
"BaseData" "FY15" "Forecast" "Working" "410" "P_000" "4110" "Jul" 2001
"BaseData" "FY15" "Forecast" "Working" "410" "P_100" "4110" "Jul" 6184
"BaseData" "FY15" "Forecast" "Working" "410" "P_110" "4110" "Jul" 6807
"BaseData" "FY15" "Forecast" "Working" "410" "P_120" "4110" "Jul" 6425
"BaseData" "FY15" "Forecast" "Working" "410" "P_130" "4110" "Jul" 6778
"BaseData" "FY15" "Forecast" "Working" "410" "P_140" "4110" "Jul" 5198
"BaseData" "FY15" "Forecast" "Working" "410" "P_150" "4110" "Jul" 3129
"BaseData" "FY15" "Forecast" "Working" "410" "P_160" "4110" "Jul" 3750
"BaseData" "FY15" "Forecast" "Working" "410" "P_170" "4110" "Jul" 1500
If there is an error on load, a file is created.
Are you getting tired of references to empautomate.cmd or does it fill your heart with joy? Hopefully the latter is true as it makes things so simple.
There’s no separate language such as MaxL. There is the requirement to load the data file to the InBox after first making sure that the file isn’t already there. If it is there, you’ll get a lovely error as there is no overwrite option.
Once that is done, as before it’s simple:
Step the fifth – aggregate the latest month
Step the sixth – exit
There really isn’t anything to do with the Planning command line utilities as each one is self-contained.
I’m not going to repeat a screenshot but the MaxL script has a logout command. There, that was fair wasn’t it.
Strictly speaking this isn’t really necessary but the OCD/conscientious programmer in me insists on this.
We have reached the point in the show where all of the code is brought together in one delicious goulash.
The pictures tell a thousand words and as I’ve typed over two thousand thus far let’s leave it at that.
In the dimension editor
Metadata and data in Smart View
In the dimension editor
Metadata and data in Smart View
I’ve enjoyed (?) counting the steps and using that as a way to measure complexity.
In the case of on-premises I say complexity is a function of:
- Number of scripts
If you’re counting that’s: 12 steps, five tools (LCM’s utility.bat, outlinelineload.cmd, refreshcube.cmd, MaxL, and calcmgrcmdlinelinelauncher.cmd), and two scripts. Add ‘em up and we get 19 discrete objects so long as your definition of object is sufficiently elastic. Work with me on this – there are worse metrics out there.
The main DemoBatch.cmd code:
The auxiliary Essbase MaxL LoadForecastData.msh code:
There are more steps with PBCS’ epmautomate.cmd primarily because of file management.
Counting the downloads, deletes, and uploads along with all of the other steps there are: 15 steps, one tool, and one overall script for a value of 17.
Do we have a winner?
Even though Cameron thinks Math Class is Tough, especially for him, 17 is only two less than 19. Is that a fair measure of PBCS being just a wee bit simpler than on-premises or quite a bit more or something else? The metric I used in the other posts in this series focused on the number of steps in the user interface to complete a task. Writing an automation script isn’t focused on clicks but instead commands and parameters.
Perhaps a better way to measure simplicity is to make that score a product of the number of utilities by the number of possible commands or switches. Assuming that more is less, there’s a fairly obvious winner and loser.
On-premises uses five different utilities with a total of 85 possible parameters and switches while PBCS uses one utility with 17.
From an ease of understanding, writing, and managing within a script, PBCS is the clear winner unless you have a strange love for arcane and little used parameters.
When will we have epmautomate for on-premises Planning?
This is part six of a thankfully six part series on comparing on-premises versus PBCS administrative task processing in both interactive and now batch form. To do this I had to document what seemed an almost maddening level of detail but if I want to understand, really understand something, I simply have to do it. I now have that basic level of knowledge and I hope that you’ll be able to use these posts as your initial guide to PBCS when (I think we will all be PBCS customers sooner or later) you make the switch.
I haven’t exhausted the subject of PBCS and given the development cadence of the tool I have to wonder if I ever will. Yes, the title of this blog contains the word Essbase and I hope to get back to that most excellent of databases but it might not be till the fall. Really.
As for this administration use case I’m not done with this subject. Look for an expansion (Yeah, I know, how could that possibly be but it will be, I promise.) of this at the soon-to-be-here Kscope16 where I’ll present On-Premises Planning vs. PBCS: Common Administrative Tasks Compared, Contrasted, and Recommended with Jason Jones on Monday, 27 June 2016 at 12:45 pm.
For those of you who don’t know Jason, here’s a recent photo of the two of us. I’m on the right.
Be seeing you.