Stupid Programming Tricks No. 20 -- Load Rules, header rows, and confusion

Load Rule hate

Yes, I do go on and on and on about Load Rules, but those little bastards have wasted quite a few hours of my professional life.  I have to believe that Load Rules were written by Old Scratch hisself.  They are evil, evil, evil things masquerading as useful.  Except they aren’t.  Don’t agree?  Join me on this tale of woe.

Loading data

Load Rules are a simple (yet evil and yes there is some personal bias in this but it is my blog after all) interface for loading files and tables into Essbase as either data or metadata.  Every schoolboy knows this, or at least every Essbase developer.

Header records

And everyone knows what a header record is – it’s that first row (or potentially rows) that define metadata not in the body of the data file be it column names or dimensions not defined in the columns.  Header records are dealt with in the Load Rule Data Source Properties dialog box.  

Here’s an example from the export of Sample.Basic.  Pay attention because There Will Be a Quiz Later.  That bit in the red box is the somewhat-useless header.

Data Source Properties

Below are the default settings – skip zero rows, no header records, no data load field names, and no dimension building field names.

In the case of the above export file, the Number of lines to skip parameter is set to 1 because the header record as defined is useless for columnar import.

Data Load Settings

A common way to add a missing dimension to a data Load Rule is to use Data Load Settings and in the Header Name field type in the desired dimension.  This post’s use case doesn’t use this approach and is  noted only for \completeness.

The review on header records in Essbase is now complete.

Enter the bug

Here is Sample.Basic clear of data in Smart View:

Here’s a simple Load Rule without an exclusion of the first record.  Remember that first record that is an artifact of a columnar export from Sample.Basic that starts off with a tab, then Jan, then Feb, then Mar, etc.?  That’s Essbase’s Period header record although since it doesn’t line up with any column it’s pretty worthless when it comes to loading.

Let’s skip the header:

Here’s the Load Rule.  Note that, unsurprisingly, all 12 months are being loaded.  This Is A Clue.

What happens?  Two things:

1. The data loads.
2. A data load error file is generated.

Loaded data in Smart View.

All is good.

Let’s not skip the header:

And what happens?

The error we expected.  

All is well and good and expected.

Bull in the china shop

But what happens when all data columns but the first are ignored and the Period is set to Jun.

Less data is loaded, but no error.  

Just as expected, only Jun data is loaded although in reality it’s from the January column of data.  Regardless, this is expected behavior.

O Horror, O Misery, O Despair

But what happens if the header row is not excluded?  It worked in the 12 month example and just threw an error file so obviously the same would happen with one month.  Except it doesn’t.

Whaaaaaaaaaaaaaaaaaaat?  

I can almost observe a pattern in the data, or at least I would if cell C3 was Jan, cell D4 was Feb, cell E5 was Mar but as you can see from the above it doesn’t work that way.  Mr. Toad had it right, O Horror, O Misery, O Despair.  As he said, right before everything went pear shaped, “O, how clever I am! How clever, how clever, how very clev----”  

Nope, I’m not clever and just like Toad when he was At Her Majesty’s Pleasure, I am, “…a helpless prisoner in the remotest dungeon of the best-guarded keep of the stoutest castle in all the length and breadth of Essbase Load Rules.”  The last bit may not quite have been how Kenneth Grahame wrote it, but you get the idea.

“I’m such a clever Toad”

If all of the above doesn’t convince you in the strongest possible way that SQL should be the only data source for Essbase, I don’t know what would.

Be like wise old Badger, or Rat, or Mole, or even the sadder but wiser Toad, but definitely not like yr. obt. svt. as I wasted several otherwise well-spent hours as I tried to figure out what was giving me such weird results when I only wanted one month for testing purposes.  

So frustrating.

Be seeing you.

ODI, Planning, Essbase, and the power of petitions

Update

Chris tells me that this support is now OFFICIAL.  Check out My Oracle Support document ID 1969035.1 for the real, honest-to-goodness, it's from Oracle statement of direction.  

Introduction

This is a guest post by Chris Rothermel, a very talented and passionate Oracle Data Integrator geek.  

Chris’ passion led him to (politely) take on Oracle Product Management and win.  That takes a fair amount of bravery and an awful lot of cheek.  Chris led, and Oracle listened.

As I’ve written before, I’m often pleasantly surprised at how an absolutely humungous multinational corporation actually listens to the opinions of its users and developers.  We’re lucky that Oracle has that culture.

With that, Chris take it away.

Victory!

I just closed this petition on Change.org requesting Oracle announce support for the Hyperion KMs for Oracle 12c.  The purpose of this guest blog is to share a little more of the history of our little grass roots movement.

It all started at KScope14 on Sunday June 21, 2014 in a large conference room in the Seattle Conference center.  In this large room I reconnected with clients, former colleagues, and several other people I’ve gotten to know through Network54, OTN, or ODTUG.  I was great to renew friendships in person and for the first time meet in person people I only know virtually like Cameron Lackpour.

Oracle’s PowerPoint presentation began with a slide declaring safe harbor.  They let us know they’re about to tell us some things about the direction of the EPM products but don’t bank on it or consider this conversation legally binding.  They talked about DRM, HFM, and Planning and there seem to be no great revelations or surprises.   

Next came the surprise statement, which went something like this:  “The Hyperion Planning and Hyperion Financial Management Knowledge Modules for ODI will not be supported in the next release of ODI.  However the Essbase Knowledge modules will definitely be available as it’s a key component used by BI for Essbase.”

Shock and awe, but mostly shock

I wondered how can this be?!  It seemed like just yesterday I read the product announcement for Oracle 11g which highlighted the adapters for Hyperion Planning, Financial Management, and Essbase.  What about Oracle’s statement that ODI is “a best-of-breed data integration platform focused on fast bulk data movement and handling complex data transformations.”  What ever happened to this talk that ODI is the integration tool of choice for Oracle products?  

Now have we been wrong to tell our customers to use it?  Let’s answer that for our customers:  No we have not been wrong as it really was the only choice Oracle had for classic Hyperion Planning.  It was the choice that was bundled with the product.  How can you make the wrong choice of integration tool if this is what the application solution provided for integration?

ODI and Planning’s history

Let’s now have a brief history of integration options for Hyperion Planning starting in with version 9.3.1 circa 2007.

• Hyperion Application Link (HAL), a product by Vignette licensed and branded by Hyperion Solutions.
• July 2009 HAL was removed from Oracle premium support
• June 2010 HAL was removed from Oracle Support

• Data Integration Management (DIM) aka Informatica’s PowerCenter rebranded by Hyperion before Oracle acquired Hyperion
• This goes quickly out of fashion after Oracle acquired Hyperion and after Oracle buys Sunopsis

EPMA for Hyperion had some challenges in the early days and many people reverted back to Classic administration and used ODI with Classic.  The Hyperion ODI adapters can’t be used with EPMA, they’re designed just for classic.   

What is the frequency, Kenneth?

There are other products and frankly Oracle’s EPM Integration strategy has been very confusing.

Dismay, despair, and then hope

When we heard the Hyperion Planning Adapters would no longer be supported that added to the confusion and the frustration of the ever changing integration choice for Hyperion.  After the open session one of my clients approached me and said, “What will happen to all of the integrations you developed for us just a few months ago now that the Hyperion Planning Adapter is going away?”  I wondered the same and thought what a waste for clients like these who have invested the time to develop, test, and use ODI for their Hyperion operations.

I attended many sessions at KScope that week and connected with customers using ODI, consultants who use it, and even old friends from Oracle.  After one session the presenter and I talked about the disappointing news and other attendees got into the conversation.  We though maybe Oracle was not aware of the in depth use of the product and we should tell them.  So I collected some business cards from clients and also asked consultants which of their clients used the products and would be negatively impacted.  

Armed with the list of clients that would be impacted I let Oracle know that these clients would be dissatisfied.  I also joked that I would start a petition on Change.org.

Petition for redress

One day I got asked the question, “Where is the petition? We can’t find it.”  So maybe there was a need.  I created the petition with the deadline of December 31, 2014.  We got over 100 votes and generated some excitement with the comments.  

Our friends in Brazil, Rodrigo Radtke and Ricardo Giampoli, supported this petition in their blog DEVEPM.com.  They also discovered how to port the Hyperion Planning KMs to Oracle 12c.  

This taught us that having the Planning KMs in 12c wasn’t a technical issue.  So what’s the reason?  One issue is Oracle’s great interest in Cloud at the exclusion of others.  Another is Oracle’s lack of visibility to the product’s use because it is packaged as a limited use license with Hyperion.  Another is the excellent work that has been done with the OutlineLoadUtility (command line and web).  

Still it seems odd to have the KMs for Hyperion Planning and not for Hyperion Essbase.  Perhaps because Planning and Essbase are managed by two different departments each department made a different decision.  Furthermore ODI is in a different department as well.   To say Oracle has a lot of products would be an understatement.

I closed it because we just may have success for Hyperion Planning.  Here’s what I wrote earlier this month:

Hope for the future

I’m not Oracle and it will be interested to see exactly when this comes to fruition or if it does at all.  

I’d like to take this opportunity to thank Cameron for encouraging me to pursue this and for this guest blog opportunity.   As Cameron says, “Be seeing you.”  More specifically I hope to see you at ODTUG’s KScope15.  Last year was my first KScope and I was blown away.

So where do we go from here?

It’s awfully gratifying to be the platform for this announcement.  ODI is a great tool and I’m very glad that it will remain part of the EPM toolkit.

Chris Rothermel, Oracle ACE

As for yr. obt. svt.’s role in this, I think Chris is giving me entirely too much credit.  He came up with the idea, he organized it, and he brought it to Oracle’s attention.  Chris worked for you, oh Best and Brightest.  Personally, I think this kind of advocacy is the kind of act that suggests that Chris is Oracle ACE material.  Yes, Chris, I know, you’re a modest guy.  Don’t be, at least in this case.  I wouldn’t write this if I didn’t think you were more than qualified to be an ACE.

Chris used a petition as a way to get Oracle to listen.  Can we use the comments section of this blog to get OTN to listen?  If you agree, leave your vote below and I’ll forward it to the relevant person.  No guarantees of course as the decision is entirely Oracle’s, but part of my job as an ACED is to identify Oracle’s advocates and evangelists and I think Chris is a fantastic candidate.

Chris, try not to blush.  :)

Be seeing you.

Taking 11.1.2.4 for a spin in the cloud

Giving thanks

As most of you know, or should know by now, yr. obt. svt. is a complete, full, and unreserved infrastructure idiot.  As most of you also know, or should know by now, that the 11.1.2.4 EPM suite has just been released.  And with this, I am faced with a conundrum:  I need (well, I want) to play with the latest release, but I will need about a month (yes, really, if I do it after hours) to get the !@#$ thing to install on my VM.  So here I am, between trapped between Scylla and Charybdis.  What to do, what to do, what to do?  I want to do a test drive, but I’ll get to it done in time for the first patch.  Ugh.

Metavero to the rescue in the cloud

John Booth has solved the problem – he has created another of his EPM Amazon Web Services AMIs (Amazon Machine Instance).  Get an AWS account and fire up John’s AMI  (see Tim German’s post on how to go through the steps on this), and off you go to EPM 11.1.2.4-land.

This blog post is not going to be much about connecting to the cloud as Tim German will be covering the mechanics (and quite a bit more) over on his blog; I’ll just note the highlights and the some of my stupid mistakes intrepid adventures in the new AMI.  This post will also cover an interesting thing I discovered about Planning 11.1.2.4 and Hybrid.

As an aside about the cloud – as you know, Oracle is making a huge push into that area with many of their products.  There’s Planning and Budgeting Cloud Service which has been a runaway success, surprising many in its popularity, and I expect there will be many more like services.  What is somewhat amusing is that John and I presented on EPM in the cloud at Kaleidoscope (as it was called then) 2010.  And we had…about 30 people in the room.  It’s not often I get to lead the market, but apparently when I do, no one cares.  :)  They do now.

Running the new Metavero image

What it looks like

OMG, it is Windows 2012.  I cannot (well, I can and am) tell you how much I hate this interface.  And no, it is not because it is unfamiliar, it is because it sucks.  Hey, why not break 20-odd years of interface so a server can look like a tablet?  Sounds like an excellent idea.  Only not really.  Okay, rant out of the way and I’ll show you how John mitigated this less than stellar approach.  Here’s hoping that Microsoft update the UI to match the rumored not-completely-awful Windows 10.  Fingers crossed on this one.

Again, the whine is over and here it is:

As you may note, John has put the most important icons onto the desktop so you can get going as quickly as possible.  If you hit what-passes-for-the-Start-menu on the 2012 instance, you will see all of the detailed shortcuts you need.  You have heard my rant about this subject so I willl spare you any more of my vitriol.

Actually running EPM

The AMI does not automatically start up the “compact” EPM service.  You must do so by clicking on the helpfully numbered 1. Start Test Drive icon.

That will bring up a dialog box asking if you want to run some who-knows-what-but-I-don’t-care (I am an Essbase/Planning guy, not a bean counter – no offence intended towards those with a green eye shade) portion of the EPM 11.1.2.4 suite.

You will then see a cool graphic John created to let you know that the instance is Working, Working, Working.  It will not be overly fast, alas, but that’s hardly his fault..

Creating a new database for Planning

While that is slowly starting up, let’s create a new Oracle database (note that SQL Server is gone, gone, gone) user instance.  Navigate over to EM Express Login either on the desktop (I went to this because when the starting image is showing the desktop icon is blocked).

Lauch that, log in as sys using the common epmtestdrive123 password making sure you tick the

“as sysdba” box:

Go to Security:

Click on the HYPPLANAPP2 user name (for us SQL Server types, this equates to, sort of, a database) and then click on Create Like.

Name your user and give it a password.  For sanity’s sake, I used the epmtestdrive123 password.

Follow the dialog box, taking the defaults each time.

Again, the defaults:

Ta da, you have just created a new user in Oracle.

Hello, Workspace and Essbase

And now that you’ve done that, EPM 11.1.2.4 should be ready to go.  Go ahead and log in as epm_admin/epmtestdrive123.

If you want to be wild and crazy, go ahead and launch EAS off the web as well:

Don’t do this

Do not use epmtestdrive, ‘coz that:

Will lead to this:

Oh agony.

Do this

Instead, use epm_admin/epmtestdrive123:

Success!  Boil in bag!

Back to Planning

Creating a Planning data source is no different than in 11.1.2.3.x.  I’m not going to bore you with any of it save the Oracle settings for us SQL Server types as it is a bit different:

Make sure that you don’t use the default 1521 or you will spend a long time cursing and whining and moaning.  Not that I could possibly tell you what that is like from personal experience.

Once you have created the sample Planning application, go on and log into Workspace.  It looks pretty much the same as does the Vision app from 11.1.2.3.x:

Playing in the Cloud, or at least looking like it

Of course, if you want to pretend you are on PBCS, simply go to http://metavero/HyperionPlanning and see the lovely new UI:

I couldn’t seem to view the Vision sample application, so I deleted it through the pretty UI and then created it again:

Where in the world…

And…I couldn’t see any forms:

Nor could I see any Dashboards (I have to admit, I’m not totally sure what that equates to in Workspace):

But I could see Tasks:

And Rules:

No Approvals:

But Reports:

And the Console:

And Settings:

And Academy:

And Navigator:

So what happens when I go back into Workspace?

I see Forms:

And Approvals:

So that’s a weird one that I am putting down to some kind of bug or some kind of idiocy on my part.

The good news

Hybrid aggregations?  They’re there both in the web UI and Smart View:

Here’s my starting number.

And a change in Product X’s Units to 999 from 451.

Do a submit and the Computer Equipment total is changed.

So what happens if I do it in Essbase?  Same change of data.

And…nothing.  Bummer.

And what gets more interesting is that the Essbase.cfg does not have the ASODYNAMICINBSO setting for the Vision application.

I can only surmise that this is because of the requirements of the cloud which do not allow manipulation of the Essbase.cfg.  I believe this means that all 11.1.2.4 Planning applications are Hybrid without any choice.  I have to read the documentation a bit to see if this is a correct guess or not.

Stop the World – I Want to Get Off

John tells me that it’s a really good idea to stop the Oracle database in a graceful manner.  Happily, there is an icon – 2. Stop Test Drive….  Go ahead and click on it.

That will automatically shut down Windows:

Thankfully this takes care of Shut Down, as I couldn’t find the !@$%ing Shut Down link.  See, I am sparing you (most of) my Windows 8 and Windows 2012 Server rant.  You’re welcome.

Make sure you have no running instances in AWS:

You will still get billed for storage, but not for running costs.  

NB – This instance shuts down at 3 am automatically.

The end for now

I strongly urge you to get your feet wet on John’s AMI.  There’s lots of stuff that I’ve just barely scratched the surface, especially as the whole Financial Close product suite is really terra incognita to me.

Personally, I will be installing 11.1.2.4 on a VM both to save costs and because I like wearing a cilice as penance for some kind of technological sin that I am not actually aware of.  I’m sure I’m guilty of whatever it is.

Putting aside my manifold neuroses and insecurities, John has give the EPM community round the globe something to really spend time on, and in, as we learn the intricacies of how 11.1.2.4 really and truly works.

Thanks again, John, for making this available.

Be seeing you.