The Compleat Idiot's Guide to PBCS No 14, Chris Rothermel and Planning data loads

Introduction

Yes, another Compleat Idiot post, and yes another one not written by yr. obt. svt.  Aren’t you glad?

This time round we have a lament coupled with a plea by my friend and colleague Chris Rothermel.  He’s got some actually useful information and informed opinion on PBCS’ native data handling which is a bit more helpful than my “The on-premises (and PBCS) native data load functionality is so brain-dead it beggars belief.  Really, it’s just awful.  At least the two are at parity.”  quote which on reflection is quite whiny but moderately funny.  It did spur Chris to write this so it wasn’t completely without effect.

With that mea culpa out of the way, let’s dive into what Chris has to say about PBCS and data loading.

Lament:  PBCS Native Essbase Data Loads Lack Functionality

Like Cameron I’m used to loading data files using an Essbase load rule.  This option is not available in the brave new world that is PBCS.  This post describes two behaviors of PBCS Native Essbase loads I find frustrating.

1. The data load is all or nothing
2. Data load error handling is poor

The data load is all or nothing.  If you have a single erroneous record in the file none of the valid records in the file are loaded.  

Can the “Validate” button help before I try to load?  Not in this case.  Validate checks the file format, not for valid members.  In Developing Essbase Applications, Chapter 2: Slay Bad Data In Essbase, Cameron creates a process to ensure members exist in the outline before data is loaded.  Here in PBCS our tools are limited.

So know when you see a screenshot like the one below nothing got loaded, not a single record from the file.

Let’s click “View Status” to see what the complaint is about and why the file errored out.

🍗 TurkeyLeg is not a valid member in the Entity Dimension.  So my data file containing 99 other valid data records did not load.  Here is the file that doesn’t load because of a TurkeyLeg.

The following webform confirms the data did not get loaded:

So what now?  Well it is probably no surprise that “TurkeyLeg” is not a valid Entity member for this database.  It was only there for demonstration purposes.  When I update the record to the correct entity the file loads and we see the data in the system.  Hooray #1:

Hooray #2:

What if the All-Or-Nothing approach is the design?

There is an advantage to the all-or-nothing approach:  The complete data file is loaded only when it is completely perfect thus avoiding the situation of partial data loads.  So if you’re an administrator in this example you would field the complaint:  “Why don’t we see any data for April?!” instead of “We are missing some data in the system.  Is the data loaded for April complete?”

The traditional way of loading Essbase data gives you a list of kickouts.  For my example file I had 99 valid records and 1 invalid record.  The 99 valid records would have loaded and the one kickout record would have been identified.  The kickout file for traditional Essbase is in a format ready for re-loading.  

The PBCS Native Essbase Data Load is different from this familiar reporting.  A list of kickouts is not provided – only the first record is!  That 100 record file was just a sample.  I really have a 27,528 record file.  When that tries to load it fails on another member that is not in my outline but should be.  An inefficient process would be to correct the single error record and then try the re-load, find the next missing member, and repeat.   

The All-Or-Nothing approach has limited merit but the functionality of the Native Essbase Data Load falls far short from what we’re used to.  

PBCS Data Load Error Handling is Poor

Navigate to Tools/Import and Export Status and find this sorry report on the data load.  Several disappointing items:

• First it is labeled as a “Metadata Import.”  Boo.  I think the most junior programmer should be able to solve this for Oracle and correctly label this as a “Data Import.”  
• How many records were read in my file?  0?!  Oh please put a small amount of code to say the data file had 100 records.  Again, a junior programmer can easily code this.
• Records Processed:  0.  Well I guess this is fair when the data load fails because nothing was loaded.  The problem is when 100 records are loaded this is also 0!
• Record Index:  This process failed on the 23rd line in the file.  If there were multiple records that fail to load it would be great to see them as well.
  
  

My improvement request to Oracle

1. Give us the option for the All-Or-Nothing data load and realize we don’t want to be held up by one failing record
2. Give us a complete reject list similar to the old Essbase kickout file
3. Label Data Load logs as “Data” and fill in the record count details appropriately

Chris Rothermel

Rothermel Consulting LLC

Talking turkey?

Eating turkey engenders calmness through amino acids.  Turkey has tryptophan.  Does PBCS have an analogue?  

Be seeing you.