There’s an Essbase Cloud Service in your future
Waiting for EssCS just like I am? You betcha. But what’s a geek to do when it’s not actually available (as of the middle of November 2016)? Roll your own. How oh how oh how? With PBCS, natch.
What’s that you say? PBCS != Essbase. But it is, under the covers as is any Planning application. And you can create a poor man’s EssCS by using PBCS’ Plan Types for fun and profit. Okay, it is fair to say that the approach I’m going to outline might be viewed as bit excessive for a single application but remember that PBCS supports multiple Planning application types: Simple and Standard and if you use the latter application type, there are multiple Plan Types and thus – at least potentially – multiple Essbase databases in the cloud. It ain’t perfect and EssCS will in future eclipse this approach but for the here and now this approach is what it takes to get Essbase in the cloud.
Arguably, and only if you’re licensed for it, there’s also EPBCS although that last type of PBCS application type is actually not terribly suitable for what I’m about to show as its value is in predefined Plan Types.
In any case, to keep things simple I’m going to use PBCS’ Lite application type. A Lite application supports but one Plan Type and it is ASO-only. The planning and budgeting use case for this has to be vanishingly small – a pure rack-and-stack level 0 only budgeting application. I guess it’s possible but given the limitations around ASO and Planning (Tim German and I presented on this at Kscope14) as the sole budgeting tool (I am not counting reporting cubes built into Planning as part of a larger mixed BSO and ASO implementation)it seems unlikely that many customers would use the Lite application type. There must be someone, somewhere, who asked Oracle product management for this; I’d like to meet him.
However, the purpose of this post is to show how PBCS can be used for analytical, not budgeting purposes and for that the Lite application has the benefit of being a good fit cloudifying my secondmost MVFEDITWWW aka ASOSamp.Sample.
I should note that it’ll also talk about a very cool way to seamlessly and quickly export data from ASO in column format. I need to write about this in a separate blog post but this will have to do for now as an introduction.
If you have access to an Essbase server, you almost certainly have ASOSamp. Notwithstanding Jason Jones’ announcement that The Beverage Company has gone tits up, Essbase’s sample applications can actually be quite useful when testing out hypotheses and I believe TBC has emerged from bankruptcy court.
Moving to Planning
How does one move an on-premises Essbase database to the cloud? An Essbase database is comprised of its dimensions, calculations, and data. Planning – on-premises or Cloud – requires dimension (including member formulas) and data extraction from Essbase and conversion to Planning’s format.
Dimensions go Up, Up and Away
Many, including yr. obt. svt., have written about Olapundergroud’s (really Applied OLAP’s) Next Generation Outline Extractor (NGOE). Tim Tow tells me that it’s been downloaded over 80,000 times (yes, really) and the most common domain name (you must register to download it) is oracle.com. In other words, NGOE is, or should be, a standard part of any Essbase hacker’s toolset.
Given that, I’m not going to walk you through how to extract dimensionality from ASOSamp and selecting the Hyperion Planning 11 Text Writer option. Just know that this is the way to get dimensionality out and (mostly) in perfect shape for importation into either on-premises Planning or PBCS.
Just to prove that NGOE does export in Planning format, see below:
The output for all dimensions plus the fruit of the next step in this process:
And as extracted:
Note that there are three Plan Types in columns in O through Q. NGOE doesn’t know how many Plan Types there are so defaults to the three out of the box generically named ones. I had to change Aggregation (Plan1) to Aggregation (Sample) and get rid of the Plan2 and Plan3 columns.
But what about data?
Essbase does many things wonderfully well but unfortunately exporting data, particularly from ASO, in a nice neat columnar format is not one of them. Gary Crisci has written about using MDX to get data out of Essbase, yr. obt. svt.’s most popular blog post ever is all about data extraction. All of these approaches are painful although in BSO-land things are easier thanks to the calc script command DATAEXPORT and of course MaxL’s (and EAS’) option of exporting in columnar format. The fact remains that ASO simply doesn’t export in columns although again MDX or Essbase Report Scripts can suffice for a limited data set. NB – MDX has a technical limit of the number of addressable cells and Report Scripts have a real world performance imposed export size. In other words, getting that data out is a PITA.
What happens when we export in native format?
Ah, that sucks. It isn’t fun to read and even more importantly PBCS will not import this.
This is what we need (this is what the sample PBCS application Vision requires but you get the idea):
What to do, what to do, what to do?
That is a tab delimted file with data at the end. As noted, a Report Script or maybe MDX can extract this, maybe, but there is another way for ASO: Harry Gates’ cubeSavvy utility will extract ASO data in column format and it’s fast, fast, fast. It is, without exaggeration, a kind of a holy grail to many of us. I will pass over why Essbase doesn’t have something like this in-built and leave that puzzle for you, Gentle Reader, to ponder.
After downloading it (registration required) upon installation and execution the following appears. Note that there are two tabs that are relevant or at least seem relevant: Outline XML Parser and ASO Export Parser. The latter allows custom MDX to define the export while the former deals with all of the database through an exported outline in XML format.
NB – As I wrote, using this XML format outline file is the fastest way to extract dimensionality in the NGOE. I cant’ think of a reason to use any other approach.
Because I am an infrastructure idiot/I am too lazy to set up my path for everything to work, I had to use the following command line to run cubeSavvy. May you be better at this than I, but just in case we’re in the same boat, Gentle Reader, here’s what I used:
c:\oracle\middleware\jdk160_35\bin\java -jar cubeSavvyUtilities.jar
Once, somehow, executed, cubeSavvy appears in all its glory.
Happily, this is easy to get via MaxL:
With the native export file and outline xml defined within cubeSavvy, all that needs doing is providing an output location for the columnar export.
And of course click on the “Parse on columns” button:
10 seconds. That’s all it took. Brilliant!
And that looks just like the data import format PBCS requires in all of its tab-delimited glory:
Gonna build me an application
Remembering not to be tempted beyond human limits by that bright shiny FCCS button on the right, creating a “Lite” application is easy so long as one remembers that this a single plan type Planning application.
Name it whatever you like, so long as it’s eight characters or less. Planning (and PBCS) != Unicode-enabled although the Essbase databases and even the data are Unicode. Curious but there it is..
Going through the motions
PBCS requires a wizard whose settings are super-duper important in on-premises but in PBCS not so much. Or is it? Inquiring minds want to know.
It alwasys takes soooo long. Working, working, working
Whirring and clicking complete.
If this is all done, why am I reviewing it? It’s almost like these settings matter. But they don’t and I only highlight them to show how meaningless they are.
Why the cavalier attitude? Those mandatory dimensions we know and love (or know and hate) are no longer mandatory. Don’t believe me? See below.
Years? Who needs ‘em?
Version? Why bother?
Entity? Only if you want to.
Even Period
I am going to use this and I will be sorry. But it isn’t necessary.
Even that Pretty Blue Eyed Account dimension isn’t needed
And so it goes.
And on-premises?
Is this true for on-premises? Not it is not.
And again.
It just isn’t fair. #OnPremisesMatters but such are the fortunes of war.
The thing to take away from all of this is that PBCS is wide open: What dimensions do you want to use? What “required” ones do you want to reuse? What functionality do you want to keep? It’s all yours to decide.
Load ‘em up
I’ve shown in previous Compleat Idiot posts how to load metadata and data. One of the joys (?) of PBCS is that its interface is in seemingly endless flux. It does get better but if you aren’t constantly in the tool things get confusing.
I gave thought to (and took a bunch of screenshots for) giving you a screen-by-screen overview of this but given that it’s been covered and given that this post is closing in on 40 pages in MS Word and per the comment about the UI changing, I’m going to ask you to consider that as read and move on to new bits of functionality.
As review though, here are my NGOE->csv files->PBCS dimensions:
One oddity I found was that on importing dimensions and creating them is that PBCS doesn’t assign a default hierarchy type to dimensions or even subhierarchies when manually creating them. This is odd.
And, if I’m replicating ASOSamp’s period to date calculations, fatal:
This, unfortunately, won’t be the last time I made this mistake and it is germane to this post. It’s really easy to do when creating those small dimensions. Stay tuned as I did it again, and again, and again.
Where’s my EAS?
It’s nowhere to be found, fortunately or not. Obv. ASOSamp is so small it doesn’t really matter but typical practice is to have at least some of an ASO database aggregated through views. Dan Pressman, I’m sorry. Perhaps it’s my poor designs that require this?
Given that there’s no EAS and for that matter no MaxL, how do aggregate views get applied? Through Calculation Manager, sort of naturally.
Database statistics
I can see that there are no aggregate views.
Calculating those aggregate views
There’s no command line way of doing this, but at least we can apply aggregate views. In fact just about all of MaxL’s execute aggregate selection is available.
It’s nice to see that alternate hierarchies can be aggregated as well.
All done although of course for ASOSamp this is more than a bit of overkill. Regardless it’s there for those real Essbase-via-PBCS applications.
This is all great, but…
I have now built ASOSamp, loaded data, and even applied aggregate views. For the most part PBCS’ ASOSamp returns the right results. Think about that last sentence for a second, reflect how many people say this in regards to EPM systems, and tremble.
Moving on past scary stories, this is a simple Essbase (see, I didn’t even think of writing PBCS) database and one that is being converted to boot, so this should be right first time round. But it isn’t.
See % of Total in Essbase:
And in PBCS:
Note the data everywhere else is wrong too. It’s not right anywhere.
Bugger.
Focusing on the specific aka one disaster at a time, the puzzling bit is that the formula is oh-so-simple, especially when I’m just copying it from Essbase to PBCS.
That’s what someone long ago and far away in Hyperion Solutions, Inc. once wrote. I like to make my MDX a bit more formal. Heaven knows I need the help. Regardless, the functionality (and result) is the same.
The same thing in PBCS doesn’t work. Why?
Time is a problem
The answer is right here. And yes, that is PBCS’ ASOSamp in 11.1.2.4 on-premises Essbase. I’ll get to that in just bit.
BegBalance is the villain
I unwisely chose to use PBCS’ native Period dimension. Because I did that, BegBalance is the first member in the hierarchy and although I’ve written about how to modify the Period dimension in the past, getting rid of BegBalance or even moving it is impossible. I’m keeping Time as a Multiple Hierarchies Enabled to be consistent with Essbase’s ASOSamp. This is a mistake because Time in % of Total now comes across #Missing. Life is complicated.
If MTD is what’s really needed (remember, there is no BegBalance in Essbase’s ASOSamp and that value is shown when Time is pulled because of Time’s Label Only property), simply change the formula:
Problem solved? No.
It’s at least better as I now see data in % of Total but there’s still that bigger problem in that data at Income Level and Under 20,000 is the same for all of the other Measures. Whoops.
What happened? I goofed when I created Income Level by not tagging the dimension hierarchy type as Stored when it should be stored. Goofus and Gallant know better.
I finally figured this out when I pulled PBCS’ outline back into EAS. It’s much easier to see here than in PBCS’ interface.
What happens is:
- In EAS, the default dimension type is Stored.
- In PBCS, creating a dimension does not require selecting a hierarchy type.
- When a dimension type isn’t selected, Multiple Hierarchies Enabled is selected.
- When MHE is used, Income Level is a Label Only member.
- When Income Level is a Label Only member, the first child, Under 20,000, is returned when Income Level is retrieved.
- Whoops.
It’s not like creating dimensions happens all that often but make sure give it more thought than I did.
Success!
Once I had that sussed out, everything worked.
After 32 pages of text and screenshots, we have come to a stopping point of sorts.
A few thoughts:
- Pushing an on-premises ASO application from on-premises Essbase to PBCS isn’t all that hard but as always the Devil’s in the details.
- It’s difficult to benchmark this in a development environment. Is PBCS faster than my VM on my laptop? Absolutely. Is it faster than an SSD, fast, 32 core server? Almost certainly not. Only you can decide if that matters or not.
- Control over the Essbase environment as is common in on-premises, e.g. Essbase.cfg, location of tablespaces, etc. isn’t possible. That’s per design but a bit jarring.
- PBCS (and Planning) management isn’t nearly as seamless as EAS. Such is life and EAS never was Planning’s (and PBCS’) goal.
- PBCS doesn’t support really large dimensions that one might typically see in an ASO database or even the number of them.
- Although I didn’t call this out, Planning’s Smart View ad hoc is painful compared to Essbase. Sorry Charlie, but in speed, flexibility, formatting, the lot, Essbase’s connection is better by miles. The good news is that Oracle knows this and is actively working on the issue. The bad news is the issue has been around for the proverbial Donkey’s Years. Only you can decide if working with a Planning connection in a pure ad hoc mode is worthwhile.
The important thing, at least as far as this blog post is concerned, is that we can have Essbase Cloud Services now. I used the Lite application for the purpose of this blog. In the real world, I’d use a Standard type and have just as many Plan Types I want including BSO as well as ASO ones.
We’re all waiting with various levels of anticipation (my level is DEFCON 1) for Essbase in the Cloud. Hurry Oracle, hurry.
Be seeing you.
An addendum – PBCS Migration aka Shared Services LCM but better
My younger, smarter, taller, from completely different parents, brother Celvin Kattookaran noted that PBCS’ version of LCM can now export Essbase artifacts and when it does that it in essence writes out all of the Essbase objects be they load rules (yes, really), .otl files, and the data files themselves. As always, Celvin says things worth listening to, especially when Yr. Obt. Svt. is trying to figure things out.
Here’s the contents of the Sample folder:
Once I have that, I can move the .otl file over to an Essbase instance. In fact if I were ambitious I could likely move the .dat files over as well. Interesting.
Note that I am not including the Planning, Calculation Manager, etc. artefacts to keep things simple. This is, again, something that on-premises cannot do.
Pretty cool stuff.