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.