Want to hire me? For availability and scheduling please email info@arcepm.com

25 August 2016

Yup, another South Florida EPM meetup and the best yet

Yup, another one and the best yet

I just attended my sixth (I think it was the sixth but it’s all beginning to blur a bit.  Fifth?  Fourth?  No matter.) South Florida meetup out of the 15 or so meetup groups ODTUG have appleseeded.  Wherever they are, meetups are in one word fantastic.  Why?
  1. They’re informal
  2. They’re grassroots
  3. They’re inclusive
  4. They’re free
  5. They’re educational
  6. They’re awesome

Six reasons aren’t enough?  One would hope so.  If you’re not convinced let me review for you what made the latest meetup on 18 August 2018 in Miami, Florida so special.

The sponsors

While meetups can run on a shoestring – and sometimes these are the best of all – this latest event was a wee bit more organized and thus took money but not to the attendees.  Yup, you read that right:  this mini conference was free, gratis, sans frais, and geen kosten.  Did I mention I like ODTUG meetups?  Why yes I did.

ARC EPM, Secure24, and Top Down Consulting all generously contributed.  Thanks, Jessica Cordova, Monica Gordy, and John Riley.  It wouldn’t have happened without you.

What happened?

Good grief there was an awful lot going on.

The kickoff

Jessica, meetup organizer extraordinaire as well as ODTUG EPM Community meetup lead, kicked it off.  I’ve talked to Jessica in my role as the ODTUG board’s EPM liaison and she’s gone from nervous neophyte to seasoned practitioner.  Let her use her experience to help you set up a local meetup.


Who says there’s no such thing as a free lunch?  ‘Cos there was.  I had the chicken.  It sure beats the PB&J I typically have whilst working at home.

Speed dating without pain

Having once witnessed that as a disinterested observer (they were going through existential angst, I was having a Rob Roy at the bar), I’m entirely happy to relate that wasn’t occurring at the meetup.  What actually was happening was geek speed networking which happily misses the aforementioned exercise’s desperation but shares the notion of enabling EPM geeks to meet fellow EPM geeks.  

There, I’ve probably insulted half of my readers although I think it’s debatable which group I’ve insulted with what.  Sometimes I amaze myself, but almost always in a bad way.  Whew.   

Clawing my way back to relevance, it was a fun and effective way of meeting perfect strangers.  My only complaint is that we didn’t have enough time to do this for everyone (not all of us could meet everyone) but it was a big group (22 not including Yr. Obt. Svt. and two others).  This is a nice problem to have.

Some of us really got into it.  And why not?

The presentations

As noted Yr. Obt. Svt. reprised the Kscope16 presentation Jason Jones and I had on PBCS administration vs. on-premises.  Jason, I mangled the bit about your PBJ API framework for PBCS but I blundered through to the best of my ability.  Here I am rehearsing that morning disheveled, unshaven, and unshowered.  Eeek.  See, Gentle Reader, I do this all for you.

The dog seemed to like it.

Ron Moore and Ludovic de Paz presented their “(Keep) Pushing the Envelope with New Calcs Features”.  I used Ron’s section on FIXPARALLEL directly after the meetup to parallelize a DATACOPY section of an administrative currency conversion fx calc script.  One can’t ask for more than that when it comes to actionable information.  I should note that long ago in another professional life Ron Moore taught me ASO in one of his classes.  I’ve presented with Ludovic as well as Paul Hoch re Calculation Manager usage.  I guess my point is both of them know me, both of them knew I would be there, and both of them showed up anyway.  Thanks, guys.

Do people value these things?

Do you see that upper left photo in the collage below?  That’s Dhaval Shah and he drove two hours to attend the meetup.  He even won my latest book.  That’s hunger for knowledge.

You’re going to be at the next one, right?  

Or start up your own in your area of one doesn’t exist?  Right?  Why not?  Reach out to ODTUG and git a goin’.

Be seeing you.

22 August 2016

Stupid Programming Tricks No 29, part 2 -- Dynamic Load Rule columns

Where we were or where are  we or most importantly where am I?

I have no idea as to the last point but then again I never do.  Ever.

Moving on, in the first part of this exciting (surely not but at least useful) series, I related how to stack dimensions in a single column.

Alas and alack, My Man In California, Glenn Schwartzberg, pointed out in the comments to that post that he had already covered this at two different Kscopes.  Oh the shame, but as a soon-to-be-ex-board member the number of sessions I get to attend is severely limited.  Sorry, Glenn.  I had to figure it out on my own.  I never do things the easy way.  Ever.  Again.  Bugger.

The Network54 use case I addressed was primarily a need to both stack dimensionality as well as selectively address more or fewer columns of data depending on data scope.

This is quite easily done in a Load Rule, indeed it’s possible in both a SQL as well as text Load Rule and it all centers around how the initial record of a Load Rule.  The Stupid Trick here is that if ten data columns are defined at design-time and only five are passed at load time, the Load Rule ignores the last five.

One might think that this would be best accomplished by changing the SQL query within the Load Rule but by doing that one would edit the Load Rule itself, this would be a design-time change , and the number of columns would be modified.  I’ll also mention that Load Rules are tricky little buggers that just beg to go FOOM! so I’m loathe to modify them.  

Instead, a SQL view that changes the scope of the columns passed to the Load Rule’s SELECT (well, you have to skip the “SELECT” but that’s the action the rule performs) * FROM viewname and ta-da, the Load Rule now selects fewer (or even more with an important caveat) data columns.

That caveat

This more-or-less Load Rule behavior is predicated on the columns that are defined within a SQL view.

I take the point, perhaps even before you’ve raised it, that modifying the SQL is a design-time change.  But with these requirements something and somehow is going to change.  Load Rule or ALTER VIEW?  One man’s meat is another man’s poison so it’s time to pick yours.

What kind of poison for the evening, sir?

I’ll have just a wee slice of SQL:
Pulling this in a SQL Load Rule looks like this:

And this:

So no different than the original fStacked table which is in turn no surprise given that the fields are the same in the view as they are in the table.

Let’s cut that view down to just Actual columns:

Et voilĂ !, dynamic Load Rules


Change the SQL in the view, no change to the rule, change the data columns and thus scope, all with no editing of The Devil’s Own.  Gee whiz, could it be that Load Rules aren’t spawn from Old Scratch?  Maybe.

Would I do it this way?

Let’s review what this approach does:
  • It stacks dimensions in a column, i.e. it allows more than one dimension to be defined for each column of fact data.  That’s not a condition of dynamic Load Rules but instead is a requirement of that post way back when in part 1.
  • It shows that removing or adding columns to a data source make the Load Rule display more or fewer columns.

The caveat to the above approach is that the definition of the Load Rule’s columns must happen before the data change and the maximum number of possible columns needs to be defined up front.  

If this last bit seems odd, think what happens when you load a poorly defined text file such as when you’re told, “There are 13 columns of data,” but in fact there’re really 14 columns 2,312 records down although not in the first 2,311 rows.  Whoops, someone forgot to mention that and because the Load Rule defines columns based on its initial 50 row read (yes, you can change this and even the starting position but you’d have to know the exact row to go to) Essbase is going to throw a rod because it doesn’t know how to handle data column 14.  The damnable thing of it is if the Load Rule can’t see the column, the Essbase geek can’t add it.  The “fix” is to create a one record file that has that 14th column, tag the column as Ignore During Data Load, and for the 2,311 preceding rows it’s as if that column doesn’t exist (remember there is no tab or comma delimiter at the end of those 13 fact field records) until record 2,312.  This is the same concept as the “Budget”,”Sales” columns defined when the data exists and then being dropped when the data source no longer contains said columns.

Whew.

So what do I do?  Benchmark.  I’d benchmark this approach, particularly the full Actual and Budget load example vs. two separate Load Rule streams, one of Actual and the other of Budget.  And as this is an ASO cube, I’d kick that latter approach off in multiple MaxL statements writing the contents to a single buffer.  Is the performance issue tied to how fast Essbase can commit a temporary tablespace to a permanent one or is it how big the pipe from the fact source is?  Dunno till tested.  

If a two stream approach worked, there’d need to be some kind of conditional branching in the batch script to decide which or both to load.  

Whew, again.  No one said that system requirements are easy.  That’s why we’re (hopefully) paid the big (even more hopefully) bucks (insert currency of your choice).

Be seeing you.

03 August 2016

Stupid Programming Tricks No. 29 -- Dynamic Load Rule columns

Preface

This was originally going to be one ginormous post on Stupid Essbase Load Rule Tricks but once I got to page 12 and realized I was about halfway through I decided to split this subject into multiple posts.  At the end of it all I’ll put in links to bring everything into a single spot.

With the warning, off we go.

Hate is such a ugly emotion

It is moderately well know that Yr. Obt. Svt. hates Load Rules.  Why?  

I hate them because they have an interface that is little improved from the days of Essbase Application Manager.  If you want a feel for what that looked like, have a read through the DBAG which (un)surprsingingly hasn’t bothered changing the screen shots of what is after all fundamentally unchanged from 1993.

I hate them because they are a temptation to those without other data integration tools to manipulate data.  I really and truly have seen instances of over 240 Replace selections in a single rule.  How does one audit that?  Understand it?  Manage it?  Know that it’s actually performing per requirements?  The short answer is notgonnahappen.  Lest I be accused of casing stones at those less fortunate (eh, those of you who have had the (dis)pleasure of meeting me may think that’s a pretty low bar), that’s what the admim had at hand, that’s what he used.  But It Just Isn’t Right when a SQL INNER JOIN would have done the same thing and in a much better way.  But I digress.

And lastly, I hate them because I have comprehensively, completely, and totally shot myself in the foot, fired to slide lock, reloaded from my spare magazine, and repeated ad infinitum.  Seriously, it’s easy to do this even when one is being careful and, Best and Brightest, I’l bet you’ve done it more than once.  Ugh.

Another Load Rule rant over.  As always, it feels so good to vent my Load Rule spleen.

A plea for healing

And yet we need Load Rules.  The latest and greatest version of Essbase, Essbase Cloud Service (EssCS), that Tim German I presented at Kscope16 used – Wait for it! – Load Rules albeit in much improved fashion.  The need to get metadata and data into Essbase (and Planning) remains.  Until that golden day when all of we Essbase geeks are using EssCS or at least enjoying the functionality that it provides in on-premises Essbase we are well and truly stuck with them.  An even better alternative would be to  use the INSERT INTO…SELECT FROM data and metadata nirvana.  A man can hope.

And now a use case

And actually, they can be at least bearable if we only didn’t have to suffer through the interface and kludginess that is a Load Rule.  How can this be done?  Quite simply really – SQL is the answer.
My Very Favorite Essbase Message Board In The Whole Wide World (MVFEMBITWWW)
Over on Network54, there was a thread where the original poster (OP) wanted to dynamically change the number of columns he’d read from a table via a single Load Rule.  Huh?   And then there was the assertation that both overloading (or stacking) single data column as well as dynamic load rules aren’t possible.  Double huh?  That just isn’t so, or at least I don’t think that’s so.

It seems odd on its face but his requirement is to sometimes load history across multiple or a single year and/or sometimes load just the latest period.  It would certainly be possible to stick the year and the period in the column but this is a set of pretty big data at least in Essbase-land – potentially up to 3 BILLION rows.  Eeek.  

A far more efficient approach would be to stick the periods in the columns.  What is likely to be faster when loading three years of data in this format ~ 83 million rows (3,000,000,000 / 12 periods / 3 years) or 3,000,000,000 (the whole kit and caboodle)regardless of the number of columns?  Exactly.

Putting aside efficiency although I’m not clear on why one would want to do that, this approach would require multiple Load Rules – at least one for just a single period and one for multiple periods – and batch processes and then a selective execution of those Load Rules.  While this isn’t an impossible task, it’s certainly annoying.

Is there another way?  You betcha.

Stacking dimensions

The first step is figuring out how to put years into the column instead of the year.  Using Sample.Basic and (gasp) a text Load Rule and yes this is possible in SQL as you will see a bit later on. Let’s see how this might be done.

Typical and atypical

Typically each column in a load rule is a single dimension as shown below.  Easy peasey no big deasy.  When I build Load Rules and when I look at what others create this one-to-one relationship is standard.


That may be the default but it isn’t the only way to peel an egg.  You can stack or overload each column with multiple dimensions.  By that I mean a column that refers to measure Sales can also be directly tied to the scenario Actual while the next column can be Budget and Sales.

This can be built in a Load Rule by selecting two or more dimensions:

Note well the double quotes around the two member names.  These are important because they act as delimiters.  If not used, Essbase thinks that the string is a single member name of Actual,Sales.  What’s desired are two dimension assignments in one column.

When selected via double clicking on the member names the result is a data column definition looks like this:  "Actual","Sales"

Unpossible!

That’s all well and good but as you might imagine I prefer to have the data describe itself.  To do that I can create a data Load Rule that reads the first record as the header:

Without data, the Load Rule looks a bit empty:

By placing the header record as the first record, and using the above Data Source Property header row definition, the Load Rule magically has the stacked dimension definitions by column.  

NB – Essbase automatically trims double quotes so using an escape character is a requirement as shown below:

Maybe an easier way to view it is in Excel:

With that, I have a Load Rule that is Actual in columns 5 though 12 and Budget in columns 13 through 20.

For the record, escaping double quotes is performed using the backslash symbol as follows:
\"Actual\",\"Sales\"

Here’s what it looks like in a Load Rule column in EAS looking just as if you’d’ve selected it by hand.

Does it work?  Yep.

As always, the proof is in the delcious pudding.  I like South African Malva Pudding  but to each his own.

Dynamic SQL your way

It’s easier in a relational source.  Yes, that’s a drum I beat again and again simply because it’s true.

I can create a simple fact table with the stacked headers I need in the column names:

Note how SQL Server (one might think that I’d switch to Oracle sooner or later) sticks [ and ] around the special character double quotes.  There is no need for escaping those characters.  Why it is required in one place and not in another is the Sweet Mystery of Life.

A simple query returns the values:

Ta da:

And a load:

Good grief that was easy.  

Two cool things thus far

We now know:
  1. Data columns can refer to more than one dimension on a column-by-column basis.
  2. Either text files or relational sources can pass that multiple dimensionality either in a header record with escaped double quotes or via a column name in a fact table.
  3. Never say never again when it comes to Essbase.

The next Stupid Trick in this series will show how to selectively turn columns on and off in a Load Rule.  

Be seeing you.

26 July 2016

Kscope16 in snaps, part 3

The days go by in a blur

Yes, this is almost a month out from Kscope16 and so, Gentle Reader, you may be wondering what took me so long.

Laziness is one of the reasons but I think I can make a case for mental and physical exhaustion.  I love Kscope but as I’ve documented over the last two posts and hope to with this one, I exercise very poor geek impulse control when it comes to this conference and the attempt to pour in 5 lbs. of coffee into a 1 lb. sack (that’s 2.26796 kg into a 0.453592 kg sack for those who live in countries that haven’t travelled to the Moon).

Don’t believe me?  Check out the geeky-cool dashboards of Yr. Obt. Svt.’s declining energy levels.

Not nearly enough sleep

Who Cameron, who, does this to you?  Ah, I do.  Bummer.
C:\Tempdir\Blog\IMG_2414.PNG

Trying to do too much, too quickly, all at the same time

Does my body respond to The Madness?  Why yes it does.  My resting pulse really is around 60.  That’s a function of good cardiovascular fitness, exercise, and sleep.  The latter two elements were not in play at Kscope.  My body responded accordingly.  And that took time to recover from.

I’m not whining (okay, yes, I am) about what I do to myself, I’m just showing you why it’s taken me so long to get back to form.

With that excuse , let’s dive into Tuesday through Thursday.

Day 3, Tuesday

I was in meetings almost all of Tuesday, so you’re going to see just a few pictures.  Meeting rooms full of geeks really aren’t all that interesting.

And the Tuesday night events that would have been interesting evince my absolutely awful photography skills.  

I can tell you I went to John Booth’s  Metavero party here:

And then Tim Tow’s AppliedOLAP party here:
Leo Gonzalez’ tweet with:  Leo, Yr. Obt. Svt., and Jason Jones at the top of the John Hancock building.  OMG what a view that unfortunately my photography skills adequately reflect.

Why were my photo’s so awful?  This might be germane.

Yes, I am jumping around a bit (I’ve jumped forward to Wednesday) but I wanted you to see why I looked like I was going to fall over on my face during meetings, presentations, etc.

I do not work well on less than four hours of sleep a day.  Not. At. All.

Here we are at Wednesday

Harry Gates and Philip Hulsebosch

Llamas?  Cameron Llamapour?  Tim Twotoes?  Oh, the puns.  I think maybe but am not sure I should be flattered by Harry Gates’ and Philip Hulesbosch’s skit.  The thought of a Dutchman playing an Uraguayan named “Phillipo” simply has to be experienced in person.  Oh yes, Harry’s cubeSavvy utilities are really cool.  I’m actually using the ASO export parser right now – it is really good -- and will have (sometime this summer) a post about it.


Btw, if you’re not keeping up, we have now reached Wednesday.  Kscope is like that – over in a snap of the fingers.

Tim and I on EssCS

Note that Tim looks animated and I look asleep.

We’ve had several requests for this session, the demos, etc. and unfortunately that just isn’t possible because of our agreement with Oracle.  Given how fast the product is moving, I wouldn’t overworry about what we showed.  Hopefully we’ll be able to show EssCS in it’s final GA iteration at Kscope17.

Waiting for Godot

Here is your board of directors and conference committee from Natalie Delemar.

And here are anxiously awaiting special event attendees:

Yves Perrot’s take on Tim and I.  He’s quite the snazzy dresser and took a bit more care than I did.  Next year I won’t have to do the fancy dress costumes as no more board.  :) or perhaps :(  

Brothers?  My Man In California is doing his best not to laugh.  When I first met him he had a real ‘tash, now it’s just as fake as mine.  I think we both look better clean shaven.

Tim Faitch catching me droop a bit.  Perhaps I should grow one as it seems to make irresistible?  Probably not.

Dance crew in action.  I was, unsurprisingly, not asked to participate.  Thanks really, truly, and sincerely Natalie as I would have said “no”.  Really, my dancing skills are best left to the imagination.

Supermarine Spitfire

The Battle of Britain Memorial Trust is well worth your support.

And one of the best scenes in one of the best movies ever about planes, Spitfire or no.

If you can’t tell, Spits are my favorite plane ever, anywhere, always.

eSSbase 2.1

What, you want me to return to the real world?  How about Ron Moore’s vintage copies of eSSbase 2.1.  I came into the Essbase world in 3.1 or 3.5 – I no longer remember.

We argued about whether this ran on OS/2 or Windows NT.  It was OS/2 as I remember well Essbase (eSSbase?) crashing hard and corrupting the OS.  Fun times rebuilding OS/2 2.0 from floppies.  

The last day

Tim and I officially put out to pasture


Natalie very nicely thanked Tim Tow and I for our six years on the board.  We aren’t quite done yet but the time for others to take our place will soon be upon us.  I know that those who follow us will take ODTUG to new heights.  It was an honor to serve.

The standing ovation (I think mostly for Tim but I’ll bask in the reflected glory) was just amazing.  I’m sure I haven’t done anything to deserve it but I thank you all for your sentiment.

One note – it was MMIC GlennS who led the applause.  Glenn, I owe you once again.


One last note:  we’re both term limited and have to wait two years before we run for the board.  I can guarantee you that won’t be either of us.  It’s time for new people to have their place in the sun.

Tanned, rested, and ready

Proof?

I’m already thinking about Kscope17’s abstracts and if I will get in.  This will be the first time in six years that I don’t get a guaranteed hotel room or conference pass.  Wish me luck.

Be seeing you.