I will be available on 3 August, 2015. Planning forever! Or something like that. Contact me on LinkedIn if you're interested.

24 August 2015

Stupid Programming Trick No. 25 part A – Hybrid allocations the Wild but Crazy way

Let’s talk about allocations of the most simple kind

Before I go any further, I must give credit to Tim German aka Cube Coder aka @CubeCoderDotCom. He and I came up with this approach as part of our Kscope15 presentation ASO, BSO, and Hybrid Calculations:  What’s Fastest, How Hard Is It, and Which One Should You Use?.  Never fear, this isn’t merely a rehash of that presentation as I go into quite a bit more detail in this post.  Also, this is the Fast, Cheap, & Out of Control straw man proposal.  The next post will be (hopefully) quite a bit more sane.

To set the stage, this allocation post will:
  • Use a Hybridized version of Sample.Basic
  • Allocate the indirect Budget cost Distribution from a pool amount
  • Use a driver based on Actual Sales level 0 Product and Market as a percentage of Actual Sales Total Product and Total Market

I (and the world+dog of Essbase developers) have written allocations like this since the Year Dot.  I purposely chose this example because it is so basic and yet pervasive.  Never underestimate how easy Essbase makes this sort of logic.  I think sometimes we lose track of the still-revolutionary functionality of Essbase.  But I digress for the eleventy billionth time in the life of this blog.

Danger, danger, danger

The title of this post contains “Wild and Crazy” and what I will show you will make your eyes pop out albeit not in a SNL 1970s Golden Age kind of way.  Nope, this one made my brain hurt when I wrote it.  I suppose that’s all a long way of saying that what I going to illustrate you works but you almost certainly should never, ever, ever use this technique.  Think of it as a laboratory experiment that shows an interesting approach but is impractical in the real world.  I have been burnt by too many other consultants’ “genius” to not want to touch this approach with a 40 foot barge pole and I don’t want you to go this way either.  

I write this post to illustrate how this could be done and only that.  And then to provide an approach to contrast in the next not-completely-insane-approach blog post.

You Have Been Warned.

But first what is the logic?

The goal is to allocate an indirect cost, in this case the Budget cost of distribution, to all level 0 Products and Markets.  Yes, this is Sample.Basic yet again, and why not?

Expenses are entered at a level 0 in a pool member

The Budget data points to be allocated are at level 0 in each dimension. Note that No Market, No Product, and Pool are non-consolidating members specifically used to contain the data and not aggregate.

Calculate percentages

The allocation percentages are an interim step, normally never viewed.

The allocated and aggregated results

Ta-da, Budget Distribution is now fully allocated.

With the logic out of the way, let’s delve into how this actually works across the various engines.

The BSO way

Outline

Surely you are familiar with this.  Certainly the readers of this blog are.

Assuming an already aggregated Sample.Basic, the code is as below.

Broadly, the code is as follows:
  1. FIX at level 0 Product and Market as well as period.
  2. use the percentage calculated from level 0 Product and Market sales divided by Actual Total Market and Total Sales.
  3. Aggregate the Market and Product dimensions.

As I wrote, easy peasy lemon squeezy.

The problem with Hybrid

Hybrid is great, Hybrid is fast, Hybrid is the best of BSO and ASO, Hybrid is the bee’s knees, Hybrid is the berries and yet at the same time, Hybrid sucks.  What?  Mr-OMG-I-love-Hybrid has a problem with the tool?  Actually, yes I do.  As Maxwell Smart would say, “Missed it by that much”.

What’s the problem?  The whole raison d’etre behind Hybrid is that it uses an ASO engine wherever dynamic sparse member calculations exist; those are then dynamically aggregate via ASO.  This gets around the the problem BSO has with scaling in the form of calculation time and disk space, the  gruesome-to-some MDX, and the opaqueness of ASO calculations scripts.  

Disk size isn’t much of a big deal today in the face of multi-terabyte hard drives (if this blog is around in five years, how we’ll all laugh at that as “big”) but a large collection of .PAG files is indicative of a long aggregation time; time is a constant as measured by us on Earth.  BSO’s genius is its retrieval speed (none of the engines beat it save for attributes) but its Achilles Heel is its inability to scale to larger databases.
If the ASO component of Hybrid is the solution to scaling, what’s the problem?  Those upper level Hybrid-enabled queries only work in a retrieval tool (Smart View, FR, etc.) and in MDX; FR only takes advantage of Hybrid if MDX is the retrieval language.  Where they don’t work is in calc scripts as of 11.1.2.4.  IOW, if you were to write a calc script that requires an upper level cross-dimensional reference that is retrieved via the Hybrid engine, i.e., just like this allocation process requires, the calc script will validate, and will work, but when that calc script refers to that upper level member, Hybrid reverts to true BSO sparse dynamic calculations.  This can be A Very Slow Thing (almost as long as that lovely run-on sentence above) and most certainly Something You Don’t Want.

As of 11.1.2.4.00x, there are only two ways to get ASO-drive Hybrid upper level data out of Essbase:
  • Run a query in Smart View/FR (again using MDX only)/etc.
  • Use MDX to get the data out – remember, neither DATAEXPORT or Essbase report scripts will export Hybrid upper level data.

I never promised you a rose garden when it comes to a clean approach.  This is a Stupid Hack after all.

What to do, what to do, what to do?

The answer is decidedly blue.  (If you’re not a fan of the American Songbook, and you clicked on the link to hear that, and you’re still not a fan of the American Songbook, there is really no hope for you.  And if you aren’t a fan of the Velvet Fog and King Cole, well, I’m so flabbergasted I can’t even rant about it.)   

It’s all in the wrist

The Stupid Trick is to load upper level data into level 0.  By that I mean: retrieve data from an upper level intersection and then load that data into a level 0 bucket.  Then when the allocation process occurs, the calculations are all level 0 BSO.  The beauty of this approach is that no aggregation (other than what Hybrid performs via its ASO engine) is required so in a budgeting scenario, the turnaround between  input and allocation and retrieval of the spread data should be very quick.

Peas in a pod

The only difference in the Hybrid world is the upper level sparse members.  Where they were stored in the BSO Sample.Basic in SampleH.Basic those members are set to dynamic calc.

Getting it out with a MDX crowbar

MDX is many things – powerful, flexible, a language with a fetish for parenthesis, square brackets, and curly braces – but one thing it is not is a good data extractor.  It works, but you’ll not be well pleased with the result.

Before I go into that particular rant – no worries, it’s coming – let’s take a look at this query.

Not that bad

I am, to be charitable, not very good at MDX but even yr. obt. svt. was able to figure this one out.

After setting display properties (and giving you my VM’s username and password), the query:
  1. Selects Sales
  2. Puts periods in the rows
  3. Restricts the retrieval to Actual, total Products, and total Market.

Quite bad, actually

If I had a $, £, ¢, R, etc. for every time I heard someone complain about MDX output over on Network54 I might not be a very rich man, but I’d at least be able to afford a better brand of Scotch.

Look at this.  Lord love a duck, this is gruesome.  And the gruesomeness is about to get much worse.

I don’t want header information, or the query repeated in the spool file, or column headers, or query success messages.  This isn’t too much of a good thing, it’s just too much and most if that I don’t want.

I want doesn’t get

If Essbase is going to read the file it must be cleaned up and made fit for purpose via a parse of the file.  I am going to use a commonly used language in the EPM world – VBA in the form of VBScript to do so but as you will see it’s not an ideal tool.  Read it, marvel and its awfulness, and think about how you would do it in Perl or Groovy or Jython or just about anything else.

VBA est omnis divisa in partes tres


Unfortunately, I am not as pithy as Julius Caesar but I do my bit.  Also, I wish I taken Latin classes, Classical (not Demotic as that might actually be practical) Greek, and more than just a few courses in philosophy.  At least I get to be a singularly focused autodidact in practically every aspect of my life.  And have a job.

A fish rots from the head down

Per Microsoft,
A Windows script (*.wsf) file is a text document containing Extensible Markup Language (XML) code. It incorporates several features that offer you increased scripting flexibility. Because Windows script files are not engine-specific, they can contain script from any Windows Script compatible scripting engine. They act as a container.

In the case of this script the header defines two required parameters:  the name of the MaxL file to be parsed and the name of the output text file.

‘Cos I can be a bit of stickler when it comes to code, I’ve included the optional <example> node.

Lastly, the script language, VBScript, is defined.

The Mystery Meat

The MaxL MDX query output is beat-it-with-a-stick ugly.  So is this code albeit better formatted.  At least I destroy all of my instantiated objects at the end of the code.

Feet do your duty

Interestingly (to some at least), the actual execution of the code is controlled at the end of the script.  Why?  Why not at the top?  Damfino.

In any case, the footer reads the arguments, ensures that they are valued, and then calls the Main procedure.

Call it names

Running it isn’t so awful and once written, runs a treat.  I’ve even named the parameters and echo their values.  Ah, that pat on the back feels so good.

The output we’ve all been waiting for

An awful lot of pain for not much return.  At least it’s readable, although it still requires a tweak or two for Essbase.

But at least the stupid thing is readable by Essbase.  Almost.

Glenn, this is the moment you have been waiting for

Does success taste best when served cold?  Yes, MMIC, I am going to use an Essbase Load Rule and I am going to use it to do ETL.  Oh the shame, oh the ignominy, oh the hack, oh the hypocrisy.  

Yep, there it is – I’m hardcoding (gasp) the dimensions that aren’t defined in the from-the-Hell-of-MDX-query file to load those upper level members into level 0 buckets.

And now the calc

My oh my oh my we are finally here.  The upper level data from that MDX query has now been loaded to level 0 buckets and the allocation code – which looks almost exactly like the BSO code ‘cos that’s what it is – can use those totals to perform the allocation.

No aggregation is needed as this is Hybrid.  ASO on top of BSO is the dream come true.

Wot’ll she do, mister?

While all of this has been great fun, this level of complexity better be worth the candle.  Is it?  After all, Sample.Basic isn’t exactly a large database – there is no Slow with that wee beastie.

Using the same Essbase database that Tim German and I have used for the past two years – 40,000 customers against 70,000 products along with the typical Planning dimensions, the Hybrid numbers speak for themselves.
Engine
Performance in seconds
BSO
117
Hybrid
6

Yowsa.  Remember that 117 seconds assumes an already aggregated database.  So if there were to follow the typical Planning approach of enter, agg, allocate, agg we’re looking at something more like four minutes.  Four minutes vs. six seconds is an eternity when a business rule is run on form save.

In the Tree, part of the Tree

So is this the way forward?  Yes and no.

Is this fast?  Yes.

Will you ever use this particular approach?  No.  At least I hope so.

The next post in this Stupid Trick will show a much saner, much simpler, and much more likely approach to allocations in Hybrid.

Be seeing you.

23 July 2015

Developing Essbase Applications: Hybrid Techniqus and Practices is now available for preorder!

New and improved

How can something be new and improved at the same time?  If it’s new, it isn’t improved – it’s brand new.  And if it’s improved, it’s just a modification of an existing thing.  It makes my somewhat-logical brain hurt when I try to wrap logic around language.  And yet, and yet, and yet…

Not improved, but vastly new

Developing Essbase Applications Hybrid Techniques and Practices  (there is no way I am ever going to remember the full name and besides I am a lazy typist so henceforth it shall be named DEA Hybrid) is not improved; it is brand spanking new.  Disregard the US Amazon description of it as a second edition – all of the content is new.  

This book is new, new, new (is the message getting through?) and contains these chapters written by this august gaggle of geeks:
  • Essbase on Exalytics and the “Secret Sauce” – John Booth
  • Hybrid Essbase:  Evolution or Revolution – Tim German and Yr. Obt. Svt.
  • The Young Person’s Guide to Essbase Cube Design – Martin Neulip
  • Essbase Performance and Load Testing – Tim German
  • Utilizing Structured Query Language to Enhance Your Essbase Experience – Glenn Schwartzberg
  • Copernicus was Right:  Integrating Oracle Business Intelligence and Essbase  -- Mike Nader
  • Managing Spreadsheets (and Essbase) Through Dodeca – Cameron Lackpour
  • Smart View Your Way – William Hodges

Did I mention this is all new content?  It is.

DEA Hybrid clocks in at 502 pages – 57 pages more than Developing Essbase Applications:  Advanced Techniques for Finance and IT Professionals.  I’m not sure you should judge a book by page count but it is indicative of an awful lot of effort, all of it good.

Preorder today

In the States?  You can buy it at Amazon or Barnes and Noble.

I am an internationally-minded geek (I have actually lived on three continents) and it’s currently available at Amazon Canada, UK, France, and Germany.  It is, oddly, not available on Amazon’s Australian, Italian, or Spanish sites.  

When oh when oh when will you actually be able to hold it in your hands?

It should be available in the September to October timeframe.  There’s nothing on the US Amazon site but the European sites show the middle of October 2015.  Order today and get your copy as soon as possible.

What about Kindle?

A Kindle edition will be published.  Last time round, it lagged the print copy by a month or so.

An awful lot of Essbase awesomeness

Despite Yr. Obt. Svt.’s contributions, DEA Hybrid is written by some of the most talented thought leaders in our industry.  There’s no other way to access a collection of such diverse, advanced, and thoughtful content.

There’s nothing like this series of books out there and once you read DEA Hybrid I think you’ll agree.

Be seeing you.

15 July 2015

AppMan is dead, but should it live on?

Nostalgia or functionality?
If you’ve ever seen this:

Then you’ve certainly used this:

What is that?  This refugee from the 1990s is none other than Esbase Application Manager aka AppMan.  I am guessing that most extant EPM practitioners today came to Essbase post 7.x, but if you were around before that, this will be a tool you know, love, and probably miss quite a bit.

As I wrote before, my Ride or Die Girl, Kscope15 conference chairwoman, @EssbaseLady, also known as Natalie Delemar said, “What?  I want that!” when she saw a screen snapshot (literally, I held a camera up to the screen of Someone Else’s Laptop and took a picture) of Essbase Application Manager.

AppMan is so loved there are copies of AppMan binaries floating around out there that people trade like samisdat books.  Does anyone pass around obsolete versions of EssCmd?  (Okay, it’s still there in the product, but does anyone actually even notice it?)  There’s something about AppMan that speaks to Essbase geeks.  What is it?

Getting under the skin

Does Oracle read this blog?  Google Analytics says, “Yes”.  I can’t tell who, but this gives me a hint:

I’m taking that as a request from product management (could be development, could be a consultant, could be someone completely-not-from-Oracle) to provide feedback.  

AppMan isn’t coming back, but Essbase as a Service (EaaS) is on its way, and we know that EAS isn’t going to be the interface.  It’s still early days for EaaS and its developer interface – this is our collective chance to let Oracle know what we need.

Let’s do some research

I have an (un)lucky group of fellow geeks that I reach out to when I either have an appallingly stupid thought fantastic idea/cannot for the life of me figure out the most basic of tasks have a question for the Best and Brightest.

Having seen the above comment to last Kscope15 blog post, I asked this question:
What should that interface have that EAS doesn't have?  What new features should it have?  How will developers love EaaS' interface?
 

Here are the responses:
  • General use of and integration with Windows
  • Copy and paste from and to Excel
  • Opening an outline file without being logged in – great for disconnected times like on an airplane.
  • Faster and reliable copy and paste within the outline
  • Simple and easy to install
  • Multi select members and change any/all attributes (UDAs, Unary Operators, Attribute assignment, etc.)

Here’s a particularly cogent comment:
I don’t have too much more to add here. I think others said it best in regard to general look and feel, and usability; and the use of standard windows features! Seems like anything web enabled in Oracle stack is cumbersome and takes a year to render/repaint (including EAS) – but I guess that can be/is environment specific? On a side note…. I am shocked that there are this many old-timers on your list that even remember AppMgr. I truly had forgotten it. Loved the tool. And loved the thread.

Aw shucks on the last bit, but the person who wrote that is particularly thoughtful.

Now it’s your turn

The past is another country but EaaS is the future; what comes to EaaS will come to on-premises.  What do you want to see in the EaaS console?  What do you need?  What do you think is likely?  Can we ever get back to the ease of integration of AppMan?

Microsoft is a direct competitor of Oracle in many ways – I am happy to relate that Oracle kicks MS’ butt in the EPM/BI world.  One thing they do right is focus on the developer experience.  Talk to a .NET developer and he’ll wax poetic about the IDE, the tools, and life in the MS universe in general.  

Why can’t Essbase be the same way?

Use the comments section of this blog, please, to voice your opinion.  If Oracle reads this blog for something more than the amusement factor (although that may in fact be the only reason), your comments have a direct line to the people that decide what EaaS will look like from a developer perspective.  

Comment, won’t you?

Be seeing you.