Want to hire me? For availability and scheduling please email info@arcepm.com

17 January 2017

The Compleat Idiot's Guide to PBCS, No. 19 -- Delete Multiple Files with EPM Automate


I have, somehow yet again convinced others to write my blog for me.  I must be getting good at this, or others must be losing their minds because this really good post came to Yr. most hmbl. & obt. svt. spontaneously.

Perhaps my mental anguish/neuroses/laziness re my awesome (ahem) and still incomplete (double ahem) PBCS rolling seven day backup post came through the ether to fellow geek Chris Rothermel.  

Or perhaps he fell under the hypnotic gesture of CameronMandrake The Magician and just wrote it ‘cos he was compelled to as shown below:

Alas, I have neither the snazzy convertible (actually I do have a blue 1993 Miata MX-5 not altogether unlike Mandrake's) or the tophat (I do have one of these which are fantastic for keeping the deer ticks off in the woods along with this) or the cape or alas the trimmed moustache (or any moustache at all) or the power of the supernatural and hence the ability to make bad guys drop rifles on their heads which, given the CRACK, had to really hurt as evinced by the “OU!” grunts of the two goons.  I do however have this blog so there’s that; I’ll bet Mandrake doesn’t have one of those.

And with that run on sentence out of the way, take it away, Chris.

Delete multiple files with EPM Automate

The purpose of this document is to share a solution which allows for the deletion of multiple files using EPM Automate.  This solution simply extends the functionality of the deletefile command from deleting a single file to deleting multiple files with the assistance of a file list and recursive calls to the deletefile command.

The heart of the solution is found in lines 36 through 49 below where the deletefile command called for each record in the file.  Additional echo commands are used to display the activity of the job.
Error handling surrounds the core solution.  Distinct messages are reported if there was an error, if no filename was provided, or even if the file provided was empty.

Execution and Testing

Create a file to be used as the list of files to delete.  Using the listfiles command we find a list of valid files.

Four of the file names are selected and a fifth bogus filename is also saved in the filelist of files for deletion.

The batch script is invoked using the file “MyDeleteFile.txt” as the input.

Using the listfiles command again we confirm four of the requested files have been deleted.

Searching for a log file in this directory yields a log file for this error containing various details of the error.

When examined the file contains various debug statements for this error and the last line echoes the same error seen earlier.  A separate file is created for each file that has trouble processing.

Chris’ conclusion

Use this batch script to delete a list of files from the cloud.  Remember to first have an active session with the cloud server and to provide a file to the batch command.  Enjoy using this script and I hope it saves you time managing the files in your cloud environment.



@echo off
REM ---------------------------------------------------------------------------
REM  ea_deletefiles.bat
REM  Author:  Chris Rothermel
REM  Date:    2017-01-12
REM  Usage:      ea_deletefiles <filename containing one filename per row>
REM  Purpose: This script recursively calls the deletefile command for each
REM           record in the file provided.  There are other checks on the
REM        file such as checking it exists and checking to see if there are
REM           any records in the file.
REM  Input:    The input file should have no quotation marks and no trailing
REM           whitespace.  The record delimiter is a new row (<CR> <LF>).
REM  Pre-Req:    This script assumes an epmautomate session is already in use.
REM           Use the epmautomate login command to initiate a session.
REM ---------------------------------------------------------------------------

REM Reset %erorrlevel%
verify > nul

REM Check if a filename parameter has been provided
IF [%1]==[] goto :Usage
REM Check if the file exists
IF NOT EXIST "%1" goto :InvalidFile
REM   Check if the file is empty
for %%A in ("%1") do if %%~zA==0 goto EmptyFile

REM Recursively call deletefile for each record in the file
REM   Trim trim any left whitespace; beware whitespace on the right
REM   No quotes allowed in the records
REM   One filename per row
for /F "tokens=* usebackq delims= " %%a in (%1) do (
    call echo Deleting "%%a%%"
    call epmautomate deletefile "%%a%%")

REM * Successful Completion *
ECHO Requested epmautomate delete commands submitted
ECHO Verify and check for deletefile.log files
exit /b %errorlevel%

REM ---- Warnings and Errors ----
ECHO Warning:  No records found in the file provided

REM Use the special %0 variable to get the path to the current file.
REM Write %~n0 to get just the filename part.
ECHO You must have a session with EPM Automate active to use this.  Use the epmautomate login command.
exit /b %errorlevel%

ECHO Warning: File "%1" does not exist
exit /b 30

echo ERROR: %errorlevel%.
exit /b %errorlevel%


My conclusion

Yet again another excellent post from Chris.  We owe him a debt of gratitude for giving his knowledge away for free.  Yes, yes, that’s the point of this blog and others but you (we) ought to reflect on the community the EPM world has.  It is possible for people to be altruistic.  You see that in ODTUG and you see it here.  Thanks again, Chris.

Be seeing you.

20 December 2016

Boston meetup is go!

EPM in Massachusetts

Another EPM meetup, another success.  Monotonous, isn’t it?

Here’s Mark Rinaldi and Norman Williams kicking off the meetup.  We had almost 20 people attending.  I (and others) think if it hadn’t been 6 Fahrenheit/-6 Celsius we may have had more.  No matter, those who attended had a fantastic time.

Here we are, we happy few:
C:\Tempdir\Conferences\Meetups\Boston meetup\IMG_0013.JPG

As promised, if you weren’t there you missed:
  1. Lunch (always important)
  2. An EPM game.  Yes, really.  And it was awesome.  We’re sad.
  3. Presentations
    1. Oracle’s Mark Rinaldi:  “EPM Product Roadmap & Strategy”
    2. Yr. most hmbl. & obt. svt.’s “Hybrid:  Theoretical promise, real world success”
    3. TopDown Consulting’s Ron Moore:  “Delivering Business Transformation with PBCS”
  1. Information about What Must Be The Very Best Oracle User Group In The World aka WMBTVBOUGITW aka much more sensibly ODTUG’s Kscope17 early bird specials and deadlines as well as cool ODTUG swag.

Dine well

The food was pretty good.  If I were in charge of things we’d be eating The Elvis.  Good thing I’m not in charge.
C:\Tempdir\Conferences\Meetups\Boston meetup\IMG_0008.JPG

Let the games begin

The room was split into groups of three.  We were given a single sheet of requirements – just about on par for real world requirements documents (I kid, I kid), given 15 minutes to come up with a single page design so just about on par with most design documents (I’m not totally sure I’m kidding), and then had to present our design.

Here’s one of the teams figuring out their plan for world domination a fantastic solution.
C:\Tempdir\Conferences\Meetups\Boston meetup\IMG_0015.JPG

Then a lucky chap got to present his team’s solution.  One thing I can say about EPM geeks is that we’re not afraid of speaking in front of others.

Even when we’re mostly wrong.
C:\Tempdir\Conferences\Meetups\Boston meetup\IMG_0010.JPG

With the icebreaker out of the way, the true knowledge sharing began.

All present and correct

Here’s Mark Rinaldi explaining Oracle’s EPM roadmap.  It’s exciting stuff, particularly for on-premises customers who have been waiting, waiting, waiting for good news.  It is almost upon us.
C:\Tempdir\Conferences\Meetups\Boston meetup\IMG_0025.JPG

What does work?  Not me.
C:\Tempdir\Conferences\Meetups\Boston meetup\IMG_0022.JPG

Here’s a picture of Steve Liebermensch graciously not correcting me on my Hybrid Essbase presentation.  Could I have been that boring or that wrong?  Could be.
C:\Tempdir\Conferences\Meetups\Boston meetup\IMG_0040.JPG

And finally Ron Moore telling us about a functional (gasp) view of PBCS. It was a refreshing change of pace.  I’ve known Ron for literally decades (just two or so) and as always he delivers excellent work.  Seriously, you need to hear Ron present on this – visionary is the way I view it.
C:\Tempdir\Conferences\Meetups\Boston meetup\IMG_0016.JPG

Planes, Trains, and Automobiles

One of the many joys of business travel is the ever-present risk of flights being cancelled.  Shall I mention the airline that left me stranded at Logan?  Why yes, here they are.  Bastards.

Instead I got to sample All Aboard With Amtrak’s service from Providence to (almost) home.  Would you believe that the train doesn’t leave till 10 pm?  And yet arrive at o’dark thirty?  Not that I was awake for it, but apparently there’s a two hour layover in Penn Station.

Rhode Island is Famous for You

Steve was nice enough to drive me to the Providence, RI Amtrak train station.  Thus another state is crossed off my bucket list of visiting every one of the lower 48.  I can’t tell you much about the state other than its train station is pretty small.  

At the same time, OMG taking the train is nicer than a plane.  I think next time, I’ll take the train.

And then I was in beautiful Trenton, New Jersey, my home state.  To be fair, there are some fantastic places in NJ, e.g. Baldpate Mountain.  However, I was not there but pondering how something could be “anytime” and yet be closed at 4:30 am.  A mere $40 cab fare home (Who at 4:30 am is going to complain about cost?  Not me.) and I was home.  

And so to bed for 40 winks before my 9 am development touch point meeting.

So what do we have?

Beyond my whining?  What we have is yet again a fantastic manifestation of the passion for learning and sharing that defines ODTUG.  It’s just a fantastic group of enthusiasts about all things Oracle.  

Your EPM community…


ODTUG is here to help you get that meetup off the ground.  Why wait?

Be seeing you.

11 December 2016

Passing 300

It all began one summer

It seems so long ago (2,770 days or 396 weeks or 91 months or 7.6 years – but who’s counting?) that I first put pen to paper – Yes, I did.  Really.  I’ve now moved on to word processors for drafts and am thus so 21st century. – and started this blog.  And why the (re)counting?  Because this little corner of EPM inanity has hit 300 posts.  That’s an average of 39 posts of Stupid Programming Tricks, Compleat Idiot, Stupid Shared Services Tricks, Stupid Planning Tricks, and other sundry bits of EPM frivolity per year.  I pity you for reading this dreck.  Come to think of it, I pity myself for writing it at such a pace but on balance I think I feel worse for you.  

But it is a landmark of sorts and an opportunity to reflect on why this blog continues when so many contemporaneously launched blogs are moribund or nearly so.

So yes, 300 posts and yet some of you are still here.  Why?

Don’t know much about Essbase/PBCS/Planning/FDMEE/etc.

I seem to be forever chasing Oracle’s EPM seemingly ever-expanding products – how do I do X, how did someone else do Y (and how can I “borrow” their approach), why doesn’t that !@#$ing Z work?  Some of my fellow EPM practitioners seem to glide from tool to tool and solution to solution with nary a show of effort (Glenn, Celvin, TimG, TimT, Dino, and Pete I’m looking at each and every one of you.  With envy.).  I assure you that yr. most hmbl. & obt. svt never, ever, ever gets from A to B without a fair amount of pain.  Solving the problem is always fun, staring at it (best of course when in front of other people, the more senior the better) in complete incomprehension not so much.

So are you this?

Or this?

Everything I've Got Belongs To You

There are the greats in this industry – any industry really – and then there are the rest of us.  Is that so bad?  We’re not the smartest guys in the room but at least we get to be in the room.  Yes, I think I just insulted every one of you, Gentle Readers, but my point being that this blog’s primary purpose to help you and me get from A to B.  Maybe the fact that you read work-related blogs (obv. not just this one), read EPM books, follow EPM geeks on Twitter, and read and post on messageboards means that in fact you’re amongst the smart set.  Surely the smart ones use resources to solve their problems; surely the dumb ones don’t.  See?  I just rescued myself from having exactly zero readers.  Hopefully.

All kidding aside, this blog as it exists today would be pointless without you.  Thank you for putting up with what has been described as an idiosyncratic (read:  long winded with detours into obscurity) approach.  I hope you take the time to click on all of my laboriously-gathered links.  Goal one of this blog:  make you better EPM geeks.  Goal two of this blog:  make you all wish it was 1967 aka peak American popular culture as it’s a giant wasteland after that.  Let’s turn the clock back.  At least you’ll appreciate what your parents or grandparents (or in some cases great-grandparents) grooved to.

I’ve got your number

Google (Blogger and Google Analytics) is funny and by funny I mean inconsistent.

Here’s Blogger’s numbers:

Huzzah!  I’m closing in on a million page hits.

And then there’s Google Analytics:
Not-huzzah because it’s telling me that I’m closing in on half a million page views.  

It’s a riddle

A couple of interesting notes about the above:
  1. People don’t read this blog around Christmas.  Not a huge surprise there.
  2. My readership is going – slowly – down.  Why?

For the first, it’s nice to know that people have lives.

As for the decline (and it is real, alas) is I think based on two things:  number of posts per year (I hit my high in 2014 of 52 posts and readers vs. 40 the year after – less new content = less readers) and competition from other posts as well as Twitter and other social media.  I haven’t tried to count the number of EPM-related blogs extant today but it surely has to be about 50.  When I started it out the number was more like 10 although as noted most of those are dead, dead, dead.  YouTube, Facebook, and Twitter are yet more avenues for those who want to learn.

Or this blog sucks and is getting worse all the time.  You decide.

Why shouldn’t I

I like to think that actually the blog is getting better.  I’ve purposely hit on a combination of series posts such as the Compleat Idiot series on Planning in the cloud, Programming Stupid Tricks for unrelated Essbase, Planning, whatever-they-are tips and tricks, and community outreach posts such as live (sort of) blogging of Kscope, OpenWorld, and now meetups.  

You may have noticed that I’ve switched to a longer and more in depth approach in my Compleat Idiot cloud series.  There’s an awful lot to learn about Oracle’s cloud products.  Lots of innovation, yes, but also lots of work learning the tools and then keeping up with them.  I can’t think of how to do this except through this detailed way as so much innovation is coming out of the movement to the cloud.  Love the cloud or loathe it, money is being poured into the products in a way that simply hasn’t existed before.  That means the products change and expand constantly and that likely means the Compleat Idiot series won’t either.  That also means my life won’t get a lot better because some of these posts are over 50 pages when written in Word.  Ouch for both you in the reading and me in the writing.

While solutions to problems are what we’re all after, there is more to life and a career than code.  I’ve used this blog as a soapbox to encourage you in the strongest terms to get involved with our little community.  As an example, my involvement with ODTUG has utterly transformed my professional and personal life.  If it happened to me, it can happen to you.  Grasp the ring.  Reach.  Blow your horn.

Where I can, I’ve tried to also impart what little wisdom I’ve picked up in 20+ years of consulting in a 25+ year EPM so-called career.  Sometimes I shake my head at the folly of others when it comes to solutions (hubristically complex), code (ugly, hardcoded, slow, wrong – sometimes all four at once), and even social interactions (Is there anyone more awkward than a geek?  Thought not.) and then realize that I almost certainly did the same thing at one point or another.  Smart people learn from others’ mistakes.  Think of this as a plea to be smart and occasionally listen to me as I’ve made every mistake there is.  

The other bit of advice I’d give you is don’t be afraid to be a contrarian.  That of course doesn’t mean you’re always right, but reflect on why people say what they say.  Is a technical recommendation for the good of customer or is for the benefit of the speaker?  Is product X the solution that everyone follows because a vendor is pushing it or would some other simpler and cheaper approach work just as well?  

In a word:

Try to See It My Way

Have I fulfilled this blog’s mission?  Here’s what I wrote on 10 May 2009:
What about the “hacking” in the name of this blog? Hacking can mean all sorts of bad things and that’s what villains do. Good hackers are more interested in taking an ordinary tool (but so cool) and doing out of the ordinary things in a geek chic way.

To that end, I’m going to try to share with you some of the dumb things I’ve done and how you don’t have to do them, how to make Essbase do things it “can’t” do, and generally make Essbase dance.

Lastly and most importantly, I’ll also share code/techniques/approaches. I welcome your comments (constructive please, I have an average ego and it is bruised when pummeled) and most of all your suggestions for improvements. I’ve never written a piece of code that hasn’t been improved through examination by a fresh set of eyes and as a consultant if I can’t fix where I wrote it, I’ll make it better next time.

And, despite the title of this web site, I won’t limit the scope of my postings to Essbase. I’ll include anything else that touches Essbase, from Planning to Dodeca, to who knows what.

That, for good or ill, is pretty much what this blog is all about.  Through the passage of time I’ve forgotten about “geek chic” and shall henceforth casually drop it into conversation.

All kidding aside, I’ve tried very hard to live up to my vision of education and outreach and I think on balance I’ve managed to do it.

Watch what happens

So where does this blog go from here?  Will there be another 300?  Will I lose my ever-lovin’ mind and actually do this again?  Maybe.

So long as I’m involved in this little industry, I feel I have no choice but to keep learning.  Whether that’s through this blog, speaking at conferences, writing books, or in some other completely-monetarily-uncompensated form, I’ll keep on learning and sharing.  One day, hopefully not too (actually, yes, hopefully given what that entails) long from now, I’ll retire and this blog will come to an end.  I’m not dead yet and I’ve got a lot of livin’ to do so expect more of Cameron in one form or another.

Because of you

So yes, this blog exists because I use it as a mechanism to teach myself but making it public with a readership that rounds down to zero would be pointless.  Thank you for your support, your comments and corrections, and your continued readership.

Call me

Want to see a topic?  Have a question (hopefully) answered?  You can reach me care of this blog or via Twitter or via LinkedIn or reach out to me in person at meetups, Kscope, and Open World.