What Is Dodeca And Why Should You Care?

Everyone knows Tim

You know who I’m talking about, don’t you? 

Over the years, you could hardly have missed him at oh, Arbor Dimensions , Hyperion Solutions, Oracle OpenWorld, OAUG Collaborate, and ODTUG Kaleidoscope.  He’s omnipresent, and gives freely of his time, expertise, and knowledge.  He’s also one heck of a nice guy.  And kind of a rock star.

He’s been deeply involved with ODTUG's board and is currently on the ODTUG Hyperion SIG board.

Did I mention that he has this company called AppliedOLAP?

Did I also mention that when I met his mother, I (for real and for true) told her that I wanted to be just like Tim when I grew up?  (This was at the tender age of 38, I best get cracking).  I’ve never wanted to be like anybody else, ever.  Not in the real world, at least. 

Did I mention that Tim and his team have graciously, generously, and for free taken over maintenance of OlapUnderground’s Outline Extractor, Advanced Security Manager, and Substitution Variable Manager.

Lastly, he’s an Oracle Ace Director.  That honorific isn’t exactly handed out like popcorn at a movie.

So, have you guessed yet?  I hope so – if you haven’t, I wonder if you have a Hyperion pulse.

The drama ends, and for many of you, there wasn’t a bit of a guessing game – the Tim I’m talking about is none other than Tim Tow.

And he has this product that I’m going to spend the next few blog posts reviewing in some depth.  I’m going to do a John Goodwin on this – come back to the same product again and again from different angles.  I think it will be fun.

NB – This won’t be a marketing review of the product, but rather a practical guide to the product and how you can use to build your most awesome Oracle EPM/Hyperion system ever.  E-v-e-r. 

Dodeca – what is it?

Here’s AppliedOLAP’s official description of Dodeca:

Dodeca is software that supports interactive analysis, planning, and reporting activities performed by business users and decision makers at all levels within an organization.

And here’s how I see it:

1)   It is the best Excel that Essbase ever met.

2)   It isn’t Excel.

Simple, eh?  And of course that description doesn’t touch all of the frankly amazing work under the covers that makes it such an awesome tool.  It’s all cool stuff, but from the sharp end of the stick, while it’s important to understand how Dodeca works and why, the most important part is what it can do for you when you implement it.

I’m just wild about Excel

Excel is wonderful, great, powerful, flexible, extendible, and customizable.  It is arguably the most widely used part of Office bar Outlook and more value sits in Excel worksheets than in annoying emails.  I love Excel.  Everyone who works in Accounting/Finance loves it.  It is warm, and cuddly, and just all around great.

And the problem is?

Alas, the “powerful, extendible, customizable” attributes are what causes so much trouble when Excel is used as a reporting tool against Essbase.

I’ve seen (and written) three kinds of Essbase/Excel reporting systems:  templates, templates with a wee bit o’code, and a super-duper locked down environment.

There are problems, big problems, with all three of these approaches, and I’m probably guilty of perpetrating all of them.  Former clients – I’m sorry, I did my best, but all I had was Excel.

98 lb weaklings, or have you thought about protein shakes

These are just templates that users retrieve into, or lock and send from.  Can this be broken?  Oh yes, and how.

The basic kind of “system” is an Essbase database, however derived using Excel files with high level retrieves.

Somehow this workbook, or series of workbooks, is distributed to users.

In turn, they connect to Essbase, retrieve/send, and change the report to their needs.

The Good

• Simple to create.
• Simple (sort of, your users have to understand the add-in or SmartView) to use.  Simple for you, at least.
• No code, so quick to create.
• Er, that’s about it.

The Bad

• The templates can get out of date and need to be redistributed.
• There isn’t a tremendous value in simple reports that need to be manually and uniquely modified by the user.
• It’s easy to break the Excel report with a pivot (or the flip side of not being able to pivot at all) – that will generally blow up the entire report and may really get ugly if you have hidden retrieve ranges and nicely formatted presentation ranges.
• The reports also break (or worse, still retrieve, but give different information than you anticpate) if rows or columns are deleted or even a member in a POV is deleted and replaced with a top-of-dimension member.

Spends time at the gym,  but not enough

Templates again, but with some automation, like an automatic log on and retrieve.  These templates are still rickety and prone to user error.

The Good

• Connecting to an Essbase database is a controlled act – at least you can be sure your users are connecting to Sample.Basic instead of some other database.
• You get to exercise your coding skills.  Do more, and one day you’ll be the Mr. Universe of programming.  This attribute may actually fall under The Bad – one programmer’s dream is another’s maintenance nightmare.
• Did I mention that this stuff is custom code either in the workbook or in an add-in – it has to be distributed, somehow.
• Once you get past the crude automation, the actual creation of reports isn’t too bad.

The Bad

• These reports are still wide open, and are easy to break for all of the reasons listed in the 98 lb weakling section.
• The system is just barely one step above templates against Essbase – your users have to be proficient with retrieves, sends, report writing, etc.  Honestly, was it worth the candle?

Dude, lay off the steroids

A completely locked down Excel environment.  In fact your users wouldn’t even know you were in Excel, save for the grid.

The Good

• You are the Mr. Universe of programming.  Nice code, nice approach, nice functionality.  This is cool stuff.
• Excel is locked down tighter than a drum head.  No user errors are going to happen here.  I don’t even see the File menu.
• You have accounted for every bone-headed/honest mistake a user can make.  This system is solid.

The Bad

• You’ve got to be a really, really good VBA programmer, and spent a lot of time thinking about how retrieves and sends are going to work.
• Did I mention you wrote a lot of code.  A lot.  And it took time.  A lot.  Maybe way more than you anticipated.
• The cool code you wrote is probably a bit specific to the database.  Did you plan for portability to other applications?  Whoops, you forgot.  Yeah, me too.
• Uh-oh, your company just migrated from Excel 2003 to 2007.  Toolbars don’t really work any more, nor do custom menus (okay, they work, sort of, but not the way they used to).  What are your thoughts on the Ribbon UI?
• ·Whoa, you just got hit by a SEPTA bus.  You are hors de combat.  Does your company have a good succession plan?  Who’s going to maintain your ultra cool application?

If you think Excel is good, check this out

No, this is not one of those “Hold my beer and watch this” moments.  Instead I’m going to list the ways Dodeca fixes the above problems.  I’ll end this post with just a list of what Dodeca can do for you with this post – the next one will dive deep into specific features.

Oh, and lest anyone with a reddened neck takes offense to the above link, let me tell you about my time in 4-H.  And the unpasteurized milk (how oh how did we survive?) we used to get from the beefalo that tasted like onions when she ate onion grass.  And all about the tour of Hatfield Quality Meats to see where our 4-H pigs and cows ended up.  That was certainly an education.  Now I know why you never name the animals you eat.  :)  In short, I may look like a geek, but I am from Pennsyltucky, and proud of it.

Okay, so a Yankee redneck (really just pinkish nowadays, I don’t even have a garden) is telling you to forget the beer, hold my coffee, and see how Dodeca solves the problems I listed above:

Distribution

Dodeca pushes Excel templates across the web.  If you’ve got Internet Explorer, you can use Dodeca.

Bulletproof

We’re talking Level IV, baby.  These reports can be just as protected as you like.  Or not.  The power is in your hands.  Did I mention this product is awesome?

POVs, dimensions, subsets of dimensions – these are all customizable and the Dodeca user can have just as much or as little control as you want.

Automation 

Logins, disconnects, multiple databases, user types, sends, calcs, dimension selectors (Ever write one of these?  Somewhat painful, isn’t it?) – anything you can think of, it’s already been thought of by Tim, et al., and frankly, it’s better than what you could do.  A painful blow to the ego for this particular programmer, but I have to bow to superior intellect and effort.  (Be honest, even if you thought you could write a better mousetrap, would you?  Really?  You have nothing else to do with your time?  Yes?  If so, I envy/pity you.)

Code

Nope, Tim, Amy, and the rest of the talented crew at AppliedOLAP wrote lots, and lots, and lots of code so that you can create template reporting systems with no code, unless you want to. 

There is an code framework called workbook scripts that allow you to customize behavior, and the entire product is extensible, but I’ll bet you never have call to go that far.  Unless you want to.  Again, I mention the awesomeness of Dodeca.  It is an implementer’s dream.

Excel Versions

Nope.  Did you miss the bit where it uses Excel templates but doesn’t use Excel?  Magical, isn’t it?

Hard to believe?

Are you a little scared of the time commitment to do all of this?  You shouldn’t be.  It’s still the same Excel templates that you know and love.  Incredible, isn’t it, because you aren’t using Excel one little bit when you’re in Dodeca.

Coming next time

So I’ve made big promises about what Dodeca can do, and why it’s the best Excel you’ll ever tie to Essbase.  Talk is cheap. 

The next post is going to provide a basic view of what Dodeca looks like from a developer’s perspective, how to hook it up to Essbase, and what simple template reports look like.

In the following posts we’ll dive down, down, down into the product and show you how you can make it sing.

This product is the best, and I’m determined to make sure that every Essbase hacker out there knows it, covets it, and implements it.  Thank goodness I don’t set unrealistic goals.  :)

Kaleidoscope for the money and time challenged

Are you skint, broke, busted, busy, unappreciated but still want to go to ODTUG Kaleidoscope?

The question

Can’t experience the full ODTUG Kaleidoscope experience because your company has no budget/that big project tragically coincides with the Conference of the Year/your boss doesn’t appreciate your super genius and won’t cough up the dough?

The answer

For those short of time, budget, or both, you can now attend the ODTUG Hyperion symposium.  What is it?  Tsk, didn’t you read my last blog post?  Of course you did, because you are a super genius, just like me.  

Returning to what you actually care about, Sunday 26 June 2010’s Hyperion symposium is the day where big Oracle acts like a tiny little startup and you’re the Venture Capitalist.  Oh yes, the power feels so good.

What do I mean?  That Sunday will be where Oracle gives we lucky attendees (and that can now include you for c-h-e-e-p, just keep on reading) a preview of where Oracle EPM is going, and if the magic repeats from last year, an opportunity for you to share your Essbase/SmartView/FDM/whatever opinion directly with the people who manage the products.  Perhaps I’m overemphasizing the point, but Sunday may be an unparalleled opportunity for you to talk directly to the product staff.  Speak now or forever hold your peace.  And your office isn’t even on Sand Hill Road.

Setting expectations

Just a bit of a disclaimer – not every product presentation last year was this kind of freewheeling session, but I am hoping to gently nudge all of them into this position through the awesome reader base that this blog commands.  So that would be my mother and Glenn.  And I have to ask her.  I know Glenn just does it for the chance to heckle me and tell me that I’m overly verbose.  Which is true.

Continuing down the may-not-happen-so-don’t-hold-me-to-it theme, Oracle will state in these presentations that they are committed to nothing they present and that whatever they do present is subject to change.  I like that – it’s honest and realistic.  Product futures aren’t written in stone and besides, if Oracle is asking you questions, the whole point is that the direction will change.  Regardless, the peek into the future makes it all worthwhile.

So, what’s your excuse?

Even Scrooge had to give Sunday’s off, and I appreciate that dipping into your pocket hurts (I know people who took vacation or unpaid time off, paid their own way, and shared rooms just to attend last year.  This conference inspires sacrafice.) ,  but look at it this way, you’re getting access that only the largest and most important customers/partners get for the mere pittance of $325 if you sign up before 24 March 2010.  And you don’t even have to miss a single day of that unbounded joy we call work.  I hope to see you there.

More cool stuff at Kaleidoscope 2010

Give back and get from Kaleidoscope

Kaleidoscope 2010 is coming (which reminds me, I need to write my presentation, but soon, gulp) and there are two awesome prequels to the conference itself, which is no slouch when it comes to awesomeness.

Schooldays

Every Kaleidoscope I’ve ever attended – a grand total of two, but give me time –kicks off with a volunteer day.  I wasn’t able to attend last year because of work pressures, but my bosses’ bosses’ boss was kind enough to take my place (thanks, Edward) so the very worthy alien plant eradication could take place.

These days are really a lot of fun (I did the one in New Orleans, so this isn’t mere puffery) because in addition to donating muscle to a worthy cause, you get to meet people you won’t be seeing during the conference proper as you obsessively shuffle between all of the cool Hyperion content here and here.  You won’t be?  Tsk.  I will.  But that’s my neurosis, so perhaps you should be grateful you don’t share it.  It is a heavy burden.

Yes, Oracle seems to own other products

As hard as it is to believe, it is my understanding that there are actually other technology tracks on offer at Kaleidoscope, although why anyone would leave the warm and cozy world of Oracle EPM is beyond my comprehension.

If you are similarly narrowly focused in your love for all things formerly owned by Arbor Software, this might be your only chance to meet the great people that use those other, obscure, products like PL/SQL…I wonder what it does.

And oh yes, give freely of your time.  See, virtue is its own reward, just like your parents told you.

A Descartesian Three

Nope, this isn’t the Wrong Kind of Join (see, I am not completely ignorant of the black art called CeeKewEl), instead you have three choices, all good.

Unleash your Inner Librarian on the Dewey Decimal System

Oddly, at my dear alma mater, Wossamotta U, the IT degree program was offered in the school formerly known as Library Science.  I am trying to imagine a major that would be less likely to get you a date on Friday night than the double whammy of geek + librarian but I am coming up short.  Of course I can be smug because I got my obsolete degree in MCIS in the business college.  And because I have been told many times that I am the living embodiment of Gary Cooper, Cary Grant, and William Holden rolled into one alpha geek.  At least I think that’s what they mean when I get called Walter Mitty.

In atonement for giving librarians (they are so going to revoke my library card and *all* library privileges, forever) and fellow geeks the raspberry, I will likely be that geek librarian and help them sort their books.  So now you know which task to avoid.

Tending to their garden

In keeping with the theme of great Frenchmen of the Enlightenment, perhaps instead I’ll help beautify the grounds at Ronald H. Brown Middle School.

My favorite part of the schoolday, recess

Or relearn hopscotch (in my case, learn it for the first time) and help paint on the blacktop the games of our halcyon youth.

You won’t regret it

It’s a great way to give back, ODTUG makes the transport easy, gives you eats, and you get to rub shoulders with people in very different parts of the technical world.  What’s not to love?

And now the amazing stuff

Would you like to know where Oracle is taking the EPM stack?  See the latest products and not suffer the pain of a beta?  Get product managers to ask for your opinion?  I can think of two ways to do this:

1)    Get a job at Oracle.  Get promoted at a pace so meteoric that the phrase “meteoric pace” is inadequate.  Take John Kopcke’s job.  Now you know all.  Can you do that in one day?

2)    Returning from Cloud Cuckooland, you could instead come to Sunday’s Oracle Hyperion EPM and BI Symposium and have it handed to you on a platter.  No, no thanks are required, this blog is here to help.

Oracle > Hyperion

Yes, Oracle is many, many times bigger than dear old HYSL ever was.  Yet somewhat unbelievably, they act like a small company, at least at Kaleidoscope.

I don’t know if it’s because Big Red really is a warm and fuzzy group of people, or that ODTUG is awfully good at getting to the right people at Oracle, or if it’s just the right alignment of stars, but the mix of altruism, self-interest, and personalities comingles into magic.  By that I mean Oracle comes to show you where they’re going in the near future, where they want to go in the mid to far future, and oh by the way, what do you Kaleidoscope attendees think about it?

We (or at least I) are not worthy

Think about that – Oracle is this enormous company that is asking you what you think of their product plan.  I’m not saying that they will do your every bidding, but it’s pretty astounding to me that they ask and act on comments from the hoi polloi of EPM geekdom.  That’s awesome.  Yes SmartView product team, I’m talking about you.  Thanks again for last year’s freewheeling session.  If I could just think of a word that was awesome * 2 and then apply it to last year’s symposium, I would.

Solutions != Kaleidoscope

I went to (and paid for) every Solutions conference there ever was.  I can’t remember anyone from Hyperion asking me for my opinion on a product.  Ever.  I know that much of the product development staff is the same so I’m going to attribute this welcome change to Oracle’s culture and Kaleidoscope’s awesomeness (there’s that word again).

It doesn’t stop on Sunday

And of course Oracle’s involvement doesn’t end there – they’ll be in sessions all through the conference.  About 1/3 of the presentations are given by Oracle employees.

You are going to be there, yes?

This is *the* conference to go to – amazing content from Oracle, partners, and customers, all those great (ahem) Werewolf games at night, and the chance to give back to the DC public schools.  You can’t miss it.

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.

ODTUG 2010 is here! (Well, almost, but you can dream, yes?)

Introduction

Oh joy, oh rapture, it’s finally here.  And you can preview 2010’s schedule here with the proposed speakers.  Enjoy the incredible (Really, it is incredible, and if you’re reading this blog, you will enjoy it.  I promise.) quality and diversity of sessions. 

The sessions are fixed; the speakers assigned are provisional pending their agreement to present.  Speakers – you proposed these presentations, now you have to live up to your promise.  I guess I’m trying to say, the speaker names may change, a little, but hopefully not too much.

Thanks

How did this amazing schedule happen?

Well, there’s this guy named Edward Roske (who has this blog you may have heard of) the ODTUG Hyperion SIG’s content leader (I think I got that right) who literally worked through the night to put together a draft schedule, and of course the rest of the board that endlessly (or wait, was that me who wouldn’t shut up?) questioned, probed, kicked the tires, and generally massaged the schedule into the awesomeness you see now.  Being part of it was really exciting, especially hearing the passion and excitement that all board members brought to the process.  The quality of the board members (guys, I am not listing names here to preserve privacy – I may revise this blog and put in names if I get your okay) is incredible and it shows in the quality of the schedule.  Yes, of course without the speakers and their abstracts, there would be no schedule, but there were many submissions, and few spots to present.  Choosing who would actually present was not an easy task as the quality of the abstracts was so high.

NB – Edward, you are too public to get the privacy waiver, and besides, you did way to much work not to get the credit.

Abstract submitters -- thanks goes to all of you for submitting your ideas.  Please know that the quality of the presentations gets better and better each year and the number of submissions gets higher and higher.  If you didn't get in this year, please don't be discouraged.  There's always next year and it would be a shame if those who were rejected never tried again.  FWIW, I'm on the board and only one of my abstracts out of three got accepted and it got merged with another presenter.  So much for my cronies on the board.  :)

Next?

Okay, you’ve got the schedule.
 
Dream away.  Figure out what sessions/how you’re going to pay for/where in Washington, DC you’ll stay when you attend 2010’s ODTUG conference.

Isn’t this the best Winter Solstice present ever?  Ever?