EPMA weirdness and the kindness of strangers

Why I don’t do installations for a living

Oracle’s Enterprise Performance Management Architect has been giving me pain, agony, and general agita because I just couldn’t get the darn thing to work.  Nope, not what you think – it wasn’t what it did to a Planning database, I couldn’t even get it running enough to whine about its bugs.

Brief history – I needed to install the Oracle EPM stack – Shared Services, Workspace, Essbase, Studio, EIS, EAS, Planning, Calculation Manager, HFM, Financial Reports, and Web Analysis on my Windows 2003 Server VM.  No big deal, right?  Installations are much easier in 11.x, right?  Even an idiot (like yr. obdnt. srvnt.) could do it, right? 

Did I read Tim Tow’s blog post on how to install 11.x?  Oh, yes.

Did I read the instructions?  Oh, yes.

Did I bug people I know and respect deeply?  Oh, yes.

Did their combined wisdom help?  Oh, yes.

Did I need to zap my VMWare instance about 10 or 12 times, despite reading these documents and generally annoying people to no end with questions?  Oh, yes.

Some call it success

But after zapping 10 or 12 installations (oh, thank you, VMWare for not making me reinstall the OS on bare metal) I finally got the extraction order right for everything I need.  Hallelujah, praise be, & c.

Except for EPMA. 

Just for the record, this is one of those simple-stupid installations where I took the absolute default on everything.  That means:  SQL Server Express, install all of the Oracle EPM stack into a single SQL Server database, use admin/password and sa/password (yes, I know, stupid from a security perspective, remember, I used that adjective before), let the configurator do its work however it wishes.  Did I mention that several prayers were sent up to the Oracle at Delphi (that’s Greek mythology, not a mix of technologies) that all would work correctly?

I suspect it was one of those a-million-monkeys-type-for-a-million-years-and-come-up-with-Shakespeare moments, but somehow, everything worked but EPMA Process Manager.

What was EPMA’s problem?

As the Great Stoneface would have said, had he talked, “Damfino”.

Here’s a screenshot of the Event Viewer error message for the Process Manager’s failed service start:

 What on earth does this mean other than the @#$%^&*()_+! service doesn’t work?  Damfino.  Did I mention that I don’t do installs for a living? 

However even I can guess that there’s something wrong with SQL Server – it’s right there in the message.

People were anxious to help

I post from time to time on OTN (this is a joke, by the way, my obsession with OTN/Network54 almost matches my coffee addiction), and the Great and Good John Goodwin took time out to try to answer my question on OTN to no avail.  And I hijacked a thread on Network54 to try to get some help.  And that help happened.

And here’s where that help happened

Henceforth I am going to have to refer to John Booth as the Great and Good John Booth.  So, that would be the Great and Good Johns?

Somewhat incredibly, he took 30 minutes out of his Saturday (today, to be precise) to set up a GoToMeeting web session and fix my problem.

I’m amazed and humbled by his generous help – as you’ll see in a moment, I wouldn’t have figured this one out in, oh, eight or nine million years.  He went above and beyond and I really, really appreciate it.

Gentle readers (all three or four of you, and “Hi, Mum”), think about this for a second – a total stranger – someone I’ve only bumped up against a few times on the web, helped, a lot.  I think I may no longer be able to be a Card Carrying Cynic.  Sniff.

So, again, John, thank you so much.

The fix

Part of me is embarrassed, the answer is so simple.  But of course it’s simple when you know how to do it.  In case you haven’t deduced this by now, I think Mr. Booth knows what he’s doing.

So simple a developer could do it

Tim Tow mentioned it, sort of, in the Network54 thread.  Or maybe he did provide the answer, and I was too dense to understand. 

The fix I was shown was – go to SQL Server Configuration Manager, open up the SQL Server 205 Network Configuration, and then right click on TC/IP.

But before you get the fix, see why Jason Jones is not the world’s greatest fan of SQL Server Express.  And neither am I.

And here we come to a difference between SQL Server and SQL Server Express

By default, SQL Server Express uses a dynamic TCP port.  By default, at least on my VM, the TCP ports were set to 0, which means dynamic.  This is (I guess) good, because SQL Server Express can then go against any port that is open.  I’m no SQL Server dba, so I’ll leave it to one of those explain why this is good.

SQL Server Express 2005

Here’s the TCP setting on my SSE install:

See the yellow highlighted lines?  These are the culprits. 

SQL Server 2005

I have a real copy SQL Server, not SQL Server Express, on my laptop (not the VM, but the laptop all of this madness runs on), and guess what?

Note the difference on the TCP ports – these are already set to 1433, SQL Server’s default port.  And the dynamic ports are turned off.

That’s all there is to it

Yup, here it is in all its glory.  Set the TCP Port to 1433, click OK, and restart the SQL Server Express service.

Launching EPMA Process Manager

John then told me to try starting EPMA Process Manager.  The VM whirred and clicked and ground away and..ta da, it ran, and brought up the Job Manager, Event Manager, and Engine Manager services as well!  I believe my mouth was hanging open in disbelief.

Magic

He also recommended that I restart Workspace.  After I did that, we went into Workspace, navigated to the Dimension Library – and there it was, in all of its completely unused glory.  It was almost, but not quite, anticlimactic.

The conclusion

Thanks again, John

So to John Booth for taking a half hour out of his weekend – thank you so much.  You’re going to see links to this blog in both the Network54 thread and on Essbase OTN giving you full credit which of course you deserve 100%.  I hope to see you at Kaleidoscope 2010 and buy you the beverage of your choice.

The very least all can do in the meantime is check out his latest post on how to install the 11.x Essbase add-in without the multi-gigabyte nonsense that EPM installer puts you through.  Now that is a cool hack.

But no thanks to you, whoever you are

To some unnamed programmer(s) at Hyperion or Oracle – why on earth did you make EPMA communicate differently with SQL Server from the rest of Oracle EPM?  Nothing else required this setting change.  Once made everything worked, but why?  Maybe you wanted to drive sales of the real SQL Server?  Or drive Essbase hackers crazy?

The end of this post

So, hacking Essbase per the title of this blog?  Not exactly, more like hacking SQL Server, but diving into this stuff is always interesting, and when you have someone who is a hacker in his own right it’s always fun.

And gratifying when it all comes together.