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

30 August 2010

11.1.2 zombies ate my brain, and how I got it back, part 1

I am an installation drama queen

Corvus brachyrhynchos is a delicious meal, especially when garnished with a rosemary sprig.  Why would I eat crow?  Cast your eyes to this triumphant post.


Yes, 11.1.2 installed just fine, and without (mostly) incident, and it even ran.


There was one small problem – it consumed:

1)    All of the memory in a 32 bit Windows Server 2003 VM (4 gigabytes)

2)    All of the memory in a 64 bit Windows Server 2008 VM (6 gigabytes)

3)    All of the memory in a 64 bit Windows Server 2008 dual booted laptop (8 gigabytes)


Are you noticing a trend?


And when I mean consumed, I mean *all* of the usable memory.  Oh, 11.1.2 might start out with 20 or 30 megabytes of free memory, but any activity dropped the available memory to just about zero – the hard drive light stayed a nice solid blue as Windows continuously paged to disk.  I wouldn’t call 11.1.2 the undead, but it certainly ate my laptop’s brain. 


NB – 11.1.2 does run in this use-all-the-memory mode, but it is excruciatingly slow and just isn’t going to be viable for testing, learning (okay, maybe that), prototyping, proof of concepting, etc.  I do those tasks all the time with running on a 4 GB 32 bit VM.  Sigh.


Along the way, an epic Network54 thread ensued, as did a less painful one on OTN .  I also hijacked a thread on LinkedIn.  I am pleased (?) to write that everyone’s consultant/tire kicking/Wot’ll she do, Mister? install of 11.1.2 is the same – it runs, but oh my goodness, does 11.1.2 use a lot of memory. 


Yes, it has occurred to me that perhaps Oracle didn’t architect 11.1.2 with a single laptop install in mind.  Having acknowledged that, the issue remains – I make my daily bread off this technology, 11.1.2 has lots of cool features, and I need access to it.

What to do?

Based on my discussions (I thank you all for your calmness in the face of my blind panic), the consensus is eight gigabytes isn’t enough memory for an 11.1.2 installation that uses:  Shared Services, Workspace, EPMA, Calc Manager, Planning, HPS, EAS, and Essbase.  I’d also like:  FDM, Financial Reports, and Dodcea


So then the question becomes – how much memory is enough?  I have heard approximately ten to tweleve gigabytes, depending on who you talk to.  Alas, my nifty (and nearly new) Latitude 6500 maxes out at eight gigabytes. 

Another laptop?

There are laptops that address more than eight gigabytes.  Tim Tow pointed out that Dell now makes a laptop that can take 32 gigs of RAM.  Er, not at $14,000 for this particular consultant.


Dell also makes a Precision 4500 laptop.  With 16 gigabytes of RAM, it’s a mere snip at $6,700.


Jake Turrell noted that Lenovo sells the same basic laptop as the 4500 but it costs “only” $3,000.


Why Dell’s offering costs so much more (yes, I did go through and custom configure both laptops to the same chipset, drive, screen, blah, blah, blah and the Lenovo still came with more features) than Lenovo’s I couldn’t say. 


A couple of thoughts about laptops:

1)    Another $3,000?  I just bought this laptop three months ago for $3,000.  Eeek.  Yes, welcome to technology in the 21st century, but it still hurts.

2)    How do I know that my even more super-duper laptop will make it to 11.1.3 or 11.2 or 12.1 or whatever the next release of Oracle’s EPM stack will be called?  I don’t mind buying new equipment more frequently than I’d like, but this is getting ridiculous.

3)    Even if I have a 16 GB laptop, will it be powerful enough to handle the 11.1.2 EPM stack when I do real work on it?

4)    Dell (I’ve owned Dell servers, workstations, and laptops since the mid 1990s), your pricing is way out of line and you’re going to lose sales with at least this geek.

Go to the Cloud

Angie Wilcox made a really good suggestion (it’s there in the Network54 thread) that I take a look at cloud computing instead of endlessly beating myself to death over trying to make the gallon of 11.1.2 fit in the pint of eight gigabytes of RAM.  And, as I truly suck at installations (okay, that’s my interpretation, Angie didn’t write that), why not a company that has already done all of the hard work?

There’s interest out there

I have received emails about my Network54 thread asking me to blog about my cloud computing experience.  No one has ever written to me before, urging more exploration/stumbling about/desperate grasping at straws about a question I posed on any board.

What this blog is not about

Remember, I’m not coming at cloud computing from the perspective of hosting production applications – I’m strictly here for a 11.1.2 sandbox as I am but a humble Oracle EPM implementation consultant. 

Other views

But even if you work for MegaCorp GmbH, NSULC, N.V., S.A., S.p.A., LTD, Ltd., Ltd (Pty), p.l.c., (Pty) Ltd, or Inc., my journey should help you figure out if cloud computing is of value to you and your company.

A training teaser

One last thing I’m not going to write about in this post (but likely will in future) – the opportunities cloud computing offers for training are immense. 


I have taught classes at clients where the install was woefully inadequate for a whole variety of reasons – this didn’t exactly make the class easy for me or all that valuable for the students.  Yes, everyone marching up to the front of the class to get five minutes at a tool and then switching places is an interesting approach in resource sharing but is not exactly good value for money. 


That won’t happen to me (nor should it happen to you) in the future – I’ll rustle up as many of these sandboxes as required and go from there.  As you’ll read, an EPM stack in the cloud is powerful medicine.

Who are the players?

There are many players in the cloud computing world, but only two that specialize in commercial Sandbox offerings for the Oracle EPM world:  EPMCloud and Full360.


Interestingly, both companies use Amazon’s EC2 platform to provide a fully installed Windows Oracle EPM 11.1.2 single server stack that is *exactly* what I need.


Could I contact Amazon directly, create/buy a Windows image, and roll my own?  I could, but I’ve already wasted more than a month of my free time trying to get 11.1.2 up and running.  My agony is your good news, but no more, please.


With the avoidance of further pain in mind, I emailed EPMCloud and Full360 to see what their offerings are; EPMCloud reached me first, so they get written up first.  I fully expect/hope/pray that I’ll be writing another post about Full360 in the near future.


EPMCloud is an EPM cloud installation (hence the name) offering from a company called HyNote


HyNote have an interesting business model.  I’ll quote directly from their web site:

Who we are

Founded in 2006 by software industry veterans, HyNote introduced an innovative approach to community-based solution delivery with the launch of it's Orchestra Partner Network; a global network of independent IT services and consulting firms with the ability to operate as one virtual company.  HyNote helps mid-sized and large enterprises implement and support Enterprise Performance Management and Business Intelligence applications.


EPMCloud is a cloud-based application delivery platform used by HyNote and our partners to take cost, complexity and risk out of enterprise application deployment and operation for our customers.


Well, I’ve got a bellyful of cost, complexity, and risk.  Can EPMCloud end my 11.1.2 indigestion?

The EPMCloud experience

I contacted EPMCloud, spoke with Tommy James, and before I knew it, was at the wheel of a 15 gigabyte quad virtual core sandbox.  That is exactly what I need, don’t have, and can’t get on my laptop. 

What is it?

It’s a virtual server.  It can be persistent, or not, between sessions.  You can run it for a long time, or not, although cost does enter into that as I’ll discuss in a little while.


On this virtual server, you are a Windows administrator and you can pretty much do whatever a Windows admin might do, including installing software.  For instance, I am a Dodeca fan and that’s one of the first things I would likely install on this box.  I also am a big advocate of ODI – onto that box just as quick as I can manage. 


One thing that’s kind of neat – when you download software onto that server it’s happening at the throughput of the virtual box, not your local connection.  I have a slow DSL connection (although it was more than adequate for using the EPMCloud server) but saw this kind of download speed when I was kicking the ASO Procedural Calc tires in the latest issue of Oracle Magazine.

What it isn’t?

EPMCloud’s sandbox is not for production use.  Testing, learning, prototyping – these are all Good Things.  HyNote consult on offering true production hosting.  Why not contact them and ask them all about it?

How big is the sandbox?


What’s installed?


Is there something missing?  Remember, this is a Windows server image – you can install anything you like. 


And you get utilities as well:


Logging on

The dramatic side of me wants to tell you that getting onto the server was a kind of Herculean task that took hundreds of years and cost millions of lives.  Nope, all I did was run Remote Desktop Connection, put in the IP address given to me by Tommy, and away I went.


The anticlimactic is climatic

By Jove, here’s something more powerful than most laptops:


NB – I was erroneously given their Extra Large server with 15 GB of RAM.  The normal EPM sandbox is a 17.1 GB box with two virtual cores instead of four.  No matter, CPU speed for this kind of stuff isn’t the issue, it’s the amount of memory.

Remember, we’re in Windows

Familiar, isn’t it?  I was practically bouncing up and down in my chair at this point.  Can you tell this is what I’m looking for?

The Start menu


It’s so normal I could cry.  Well, not really, but it is really nice to see that, and have had nothing to do with its creation.  I leave that to the EPM installers of this world, bless ‘em all.

Client connections

EPMCloud’s 11.1.2 instance does not include Microsoft Office.  However, I happen to have a Windows XP VM just waiting for 11.1.2 client software.  You can download the Classic add-in and/or SmartView from Oracle directly or through EPMCloud. 


I chose to download the good old add-in from EPMCloud:


And then ran an install:


Yes, the 11.1.2 add-in install first has you run a binary to install, and then when you start Excel there is a second step.  Weird, but there it is.


Magic, isn’t it?



I ran out of time to really performance test this thing but I can certainly provide some subjective findings:

1)    Is this faster than a laptop?  Unless you’re talking about one of those ~$15,000 laptops I mentioned above, you bet.  It is significantly faster than my poor eight gigabyte laptop.  Everything I did was practically instantaneous instead of the click, pause, pause, pause, display, click, pause, display, click, etc experienceThat was the whole point of this exercise, so a definite win.

2)    Is it as fast as a fully architected multiple server installation running on SSDs, Fibre Channel connected servers (not so likely, eh?), etc., etc., etc.?  Nope, but that’s hardly the point of the Sandbox product. 


Yes, this is the $64 (oh, the punning, 64 bits, and dollars, and I get to work in an old radio show) question. 


Is this price effective?  My usage model will likely be:

1)    An hour or two before work and at night (yes, I am sad)

2)    A few hours every weekend


The above pattern will be amplified when I need to learn something new for a project/client, and then usage will die off as I get involved in something else/have finally learnt the particular technology/try to have a life.  I could easily see touching my persistent EPM sandobox seven days a week, maybe even twice a day.


Alternatively, as I build prototypes and/or proof-of-concept systems, I’m likely to use the sandbox eight or ten (yes, that sad bit, again) hours per day, for maybe five days in a row.

So how does much does EPMCloud cost?

These prices are subject to change.  The prices below reflect the pricing model as of the writing of this post but this blog is not the price sheet.   For that, go right here.  I have been informed that the pricing you see below is not on the EPMCloud website but will show up soon.


Having stated that disclaimer, here’s what I know now:

1)    There is a $25 fee each time EPMCloud starts up a new sandbox from one of their base images (you know, the one you didn’t spend any time installing, testing, whining about how you can’t get it to work, etc.).  This startup includes the first four hours of sandbox time.

2)    Remember, you can stop a sandbox at any time.  Restarting that sandbox will cost you $5.  No hours go along with that restart.

3)    Once you go past the first four hours on new sandbox startup in #1, or any time you use on the restart as in #2, the regular hourly rates apply. 

Those hourly rates

The hourly rate for what EPMCloud calls a HM Extra Large sandbox, with 17.1 GB of RAM, 6.5 ECUs, and two virtual cores is $1.15.


NB – An ECU is an Amazon’s EC2 Compute Unit.  A single ECU is equal to a 1.0 to 1.2 GHz 2007 Operton or 2007 Xeon processor.


So let’s look at a use case of CL, a typical (?) consultant who has no large infrastructure group to hand him that great big 11.1.2 VM.  A side note here – I have spoken with multiple consultants about their company’s 11.1.2 sandbox as surely they must have it, be kicking the tires on it, learning more than I so they can kick my derriere when it comes to engagements, etc.  And do you know what?  Not a single blessed one has it.  Oh, they will, sooner or later, but yr. obdnt. srvnt. believes in taking his training and prototyping destiny into his own hands.  You must either be hammer or anvil.  You choose.


Now that I’ve scared/shamed you into further reading, let’s see what we have.

Cameron the consultant’s use case

Given the $25 startup cost the new start costs, and the $5 restart, and the $1.15 per hour charge, here’s what doing some tire kicking/prototyping might look like in a typical month.  As always, your usage may differ, and this could cost less or more.  I encourage you to contact EPMCloud and fully discuss your requirements. 


























Tire kicking













































Demo to client





Tweak prototype





Demo to client















Blog writing





























































Montly total

$ 80.00











Total cost:




Is $229 a lot of money for a month’s worth of 11.1.2?  Well, if your name is Rich Uncle Pennybags, maybe not.  Not even if you’re a humble consultant as you won that job and are going to bill 200 hours on the project.  You’ve made your money over again and again and again.


I won’t bore you (Now he writes this?  Too late.) with how much time I wasted trying to get a usable 11.1.2 environment going – time I could have spent learning more about this release and doing work that will lead to billable hours.  Playing around with stack settings in the Windows registry is amusing, but not actually helpful.


For those of you lucky enough to eat and sleep in your own house every night, throw out billable hours and substitute project done on time and the big promotion that results. 


So there you have it, a complete 11.1.2 installation, running correctly (and quickly), a mere network connection away.


The amazing thing is there is nothing amazing about the EPMCloud Oracle EPM experience.  It just works. 


Should you give serious consideration to EPMCloud’s Sandbox?  Absolutely.  I am.

People I’d like to thank

My journey to a usable 11.1.2 install isn’t over yet, but I’m a lot further along than I thought I would be.


I bugged so many people out there, and so often, for help with my 11.1.2 installation woes that it’s impossible to work most of you into the narrative of this blog.  The best I can do is to list your names alphabetically; if I’ve missed anyone, let me know and I’ll add you to the Roll of Honor.  Thank you all for your patience and forbearance:

Jay Aleck

Indra Bangsawan

John Booth

Charl Du Toit

Tommy James

Tim German

John Goodwin

Jeff McAhren

Dan Pressman

Glenn Schwartzberg

Minash Shah

Tim Tow

Jake Turrell

Ben White

Angie Wilcox

22 August 2010

The Mighty Listbox and Treeview -- part 2A

The best member selectors you’ve ever seen #2A

We’re back to the best front end tool, ever, ever, ever.  Why the delay?  The most awesome Oracle EPM conference ever, semi-interesting stuff such as I changed companies (back on my own, and loving it) and laptops, 11.1.2 installation struggles and other lame excuses.
Who cares about excuses, it’s time for some pretty cool stuff in Dodeca.  As it’s been a while, so long, in fact, that Applied OLAP came out with another release since I last wrote, and some of the SQL functionality has changed, this post will highlight the new way to do SQL and a really cool piece of built-in Dodeca functionality.  The really cool and semi-complicated stuff will go in the next and (hopefully) final post on member selectors where I’ll cover Workbook Scripts.
I am trying, albeit somewhat unsuccessfully as I review this post, trying to make these beasts a little smaller.  If you think they take a long time to read, imagine how long they take to write.

What are we trying to do?

Here’s what I wrote so long ago:
The next subject of combining Essbase, Dodeca metadata, and SQL to build report and user specific METAREAD filters without ever touching Essbase.sec, gets a little, mind, just a little, involved and I want to keep you focused...

To reiterate, Dodeca is going to produce user and report specific METAREAD filters using a simple SQL table, the Essbase username, and the name of the report.  In other words, depending on who you are, and what report you’re in, you’ll see different views of the same dimension.
Esoteric?  Not at all – think about the times you’ve wanted to limit, say a Planning form to just a few members for different people but wanted the dimension wide open on other forms.  How would you do that?  The best I can think of is multiple copies of the same form with the dimension tied to user security and limited members on the form itself.  This would necessitate multiple copies of the form, form security (can’t have CallMeTex see Biff’s metadata and/or form and vice versa), and a maintenance headache.  And that’s just one form.
We’re going to do this in a decidedly simpler (and may I add, significantly cooler) fashion. 
Remember, in this post, we’re just going to essentially duplicate a METAREAD using the username as that is built into Dodeca.  While this is cool, there is better stuff to come.

METAREADs by Username

This is so easy to do that, I actually forgot that I didn’t show you how to do this.  It’s all built into Dodeca so I guess you could call this Yet Another Cool Dodeca Feature For Free (YACDFFF or yak duff – hmm, that doesn’t sound appetizing but I promise this is good stuff).
I’ve already reviewed how to create a SQL Passthrough DataSet in Dodeca.  As this is a new laptop (VM actually) and a new release of Dodeca we’ll do a fast review.  For the sake of speed/my sanity/my sleep, I am not going to go through every step of creating this side of the SQL as it all pretty adequately covered in that previous post.

A simple SQL table

Note that the Report field is NULL – we’re going to ignore it for now; it will come into play in the next post.

A simple SQL connection

These are now defined in a separate menu selection instead of being buried in the SQL Passthrough DataSet.  This allows you to reuse the data connection instead of having to define it multiple times and maintain it multiple times.  Do you get the idea that Applied OLAP thinks about their product?
Go to the Admin menu and select SQL Connections.
And here it is:
Notice how much simpler this approach is than the way it was in Dodeca 4.x as shown below:
Dodeca only gets better.

Backwards compatibility

If you want to (I can’t imagine why you would except if you have legacy SQL Passthrough DataSets) you can still define the connection in the DataSet itself as shown in the red box:

A simple SQL Passthrough DataSet

This works the same way as Dodeca 4.x. 

The SQL DataSet

And here it is. 

Query properties

Now click on the query ellipses to see the more details behind the query.  There are specific SQL properties (queries) for the four actions:  DELETE, INSERT, SELECT, and UPDATE.  Are you getting the idea that Dodeca speaks SQL pretty well?  It does.

The query

And finally, click on that last ellipses button to see the actual query.  Here it is in all of its glory.
That bit highlighted in red is very important.  Recall that there is no row in the EssbaseUser column that has a value of USERNAME.  Why hardcode that into a query, especially when the desire is drive selections by Essbase username?  Here’s a hint, that isn’t a hardcoded value, even though it looks like one. 

Simple Selector List

Now go to the Selector List to change what looks like a hardcoded value to a parameter driven one.


Dodeca kindly keeps track of who is logged in, and can pass that information to the SQL Passthrough DataSet.  When it does that, it replaces whatever the matching string is in the SQL statement.

A gotcha

Watch out, I originally created a field called Username. 
SELECT Market from hMarket WHERE Username = 'USERNAME'
Became this:
SELECT Market from hMarket WHERE hypadmin = 'hypadmin'
Dodeca treats the whole string as available for substitution.  That was a bit of a head scratcher till I sussed it out.
I annoyed Tim with this, and he helpfully pointed out that changing the token to something like %%USERNAME%% to avoid this issue.

Magic ensues

Let’s log in as Biff, our effete East coaster who whines about the price of arugula at his yuppie market.
And what does our preppy friend see?
Now we’ll log in as CallMeTex, a rugged individualist from somewhere west of the Pecos.
And is the treeview different?  Oh yes indeed-y. 

A TINCR moment

Think about what you’ve just seen:  you have driven, by username, what dimensionality shows up in Dodeca.  This is essentially duplicating a METAREAD filter.  Big deal you say.
Ah, but remember:
Unless you count a SQL statement as code that is so easy that even I could write it.
Dodeca did the rest.

Next time

As I wrote in the introduction, we’re going to dive into Workbook Scripts to grab the report name and then tie the username to the report to come up with those report/username METAREADs that aren’t available in any other tool.
Hacking Essbase indeed.

07 August 2010

Stupid programming tricks #1


This is going to be one of an occasional, short (can it be true), little techniques that I’ve picked up along the way.  This one isn’t even original, but it works so well I just have to write about it.

I had to do an inventory of calc scripts, rule files, and report scripts.

There were lots of them.  Lots.  More than you could ever imagine.  And, I needed to get a list of all of them quickly.

Oh, I could have used the Most awesome excellent Language ever, and entered:
display calculation all ;

But that would only have given me the calc scripts.  What about reports?  What about rules?

I could have used MaxL again with a different display command, display object, but the syntax for that command confuses my simple brain.

A better way

It occurred to me that those items were all available at the OS level as they are all files with .csc, .rul, and rep extensions.

How could I quickly, painlessly, consistently, etc., etc., get this information into a text file so I could hoover it into SQL or Excel to report on?

Why good old DOS, of course.  Yes, I am old enough to remember DOS 1.0, and DOS-compatible PCs (which were not the same as PC compatible), and CPM, and when dinosaurs walked the earth with me and my good buddy down at the quarry, Fredrick Joseph Flintstone.  And I like DOS, heaven help me.  Succinct, powerful-ish, and cryptic.  What’s a hacker not to like?

And guess, what, DOS has this nifty little recursion function called FOR.  If you care to read all about it, type FOR /? at the command prompt on your PC or check out this nice blog series that I wish I had found before I figured this out.

What’s it look like?

So simple it hurts:

for /r %d in (*.msh) do echo %d >> c:\temp\MaxLOutput.txt

Enter that from whatever directory you care to start in and FOR /R will do the rest for every single MaxL script that’s in the descendants of the launching directory.

And here’s the output (I launched it from c:\tempdir):
C:\Tempdir\ODTUG_2009\9.2 metaread\Copy of
C:\Tempdir\ODTUG_2009\9.2 metaread\Metaread\deprecated\filename.msh
C:\Tempdir\ODTUG_2009\deprecated\Copy of sample3.msh

How about calcs?

for /r %d in (*.csc) do echo %d >> c:\temp\CalcOutput.txt

And here’s the output (I launched it from c:\tempdir):
C:\Tempdir\NOTTELLING\NOTTELLING\Bad order FinPlan4_GLTest\ActCmbnd.csc
C:\Tempdir\REDACTED\Phase I\Adjustment\Adj_calcs\$calc.csc
C:\Tempdir\REDACTED\Phase I\Adjustment\Adj_calcs\$test.csc
C:\Tempdir\REDACTED\Phase I\Adjustment\Adj_calcs\71937158.csc
C:\Tempdir\REDACTED\Phase I\Adjustment\Adj_calcs\AdminOH.csc
C:\Tempdir\REDACTED\Phase I\Adjustment\Adj_calcs\AggCMth.csc
C:\Tempdir\REDACTED\Phase I\Adjustment\Adj_calcs\BaseAct.csc
C:\Tempdir\REDACTED\Phase I\Adjustment\Adj_calcs\BB2YTD.csc
C:\Tempdir\REDACTED\Phase I\Adjustment\Adj_calcs\BR2.csc
C:\Tempdir\REDACTED\Phase I\Adjustment\Adj_calcs\CComp.csc
C:\Tempdir\REDACTED\Phase I\Adjustment\Adj_calcs\ClrAdj.csc
C:\Tempdir\REDACTED\Phase I\Adjustment\Adj_calcs\Comp.csc
C:\Tempdir\REDACTED\Phase I\Adjustment\Adj_calcs\Fringe.csc
C:\Tempdir\REDACTED\Phase I\Adjustment\Adj_calcs\Pipeline.csc
C:\Tempdir\REDACTED\Phase I\Adjustment\Adj_calcs\RevCycle.csc
C:\Tempdir\REDACTED\Phase I\Adjustment\Adj_calcs\Revenue.csc
C:\Tempdir\REDACTED\Phase I\Adjustment\Adj_calcs\Seed7699.csc
C:\Tempdir\REDACTED\Phase I\Adjustment\Adj_calcs\SeedCalc.csc
C:\Tempdir\REDACTED\Phase I\Adjustment\Adj_calcs\Taxes.csc
C:\Tempdir\REDACTED\Phase I\Adjustment\Adj_calcs\Transfer.csc
C:\Tempdir\REDACTED\Phase I\Adjustment\Adj_calcs\WSGrant.csc
C:\Tempdir\REDACTED\Phase I\Adjustment\Adj_calcs\YTD2BB.csc
C:\Tempdir\REDACTED\Phase I\Adjustment\Adj_calcs\YTDAct.csc
C:\Tempdir\REDACTED\Phase I\Adjustment\TUSMFRBS_PandL\$calc.csc
C:\Tempdir\REDACTED\Phase I\Adjustment\TUSMFRBS_PandL\$test.csc
C:\Tempdir\REDACTED\Phase I\Adjustment\TUSMFRBS_PandL\$test2.csc


And that’s it – this isn’t Oracle EPM based – it can be for any kind of file.

Not exactly hacking Essbase, but it’s a little out of the ordinary and a *huge* timesaver if you’re trying to get lists fast.