28 March 2017

Head in the Essbase Cloud No. 2 -- Up the down staircase

I know a little bit about a lot of things

One of the more interesting aspects of Essbase Cloud is that dimension management within Excel is possible. Yes, there’s a web UI that allows this as well but for most things I find it difficult at best to work with. It’s interesting, but it simply doesn’t do a good job in managing a dimension more complex than Sample.Basic’s Period dimension because it doesn’t support any kind of treeview. Don’t believe me? Wait for another blog post where I cover it in detail (this is assuming, of course, that I continue to have access to a pod. Fingers crossed on my part.

Instead, I am – hypocritically of course – going to use Sample.Basic, but in Hybrid mode, to create a member formula and load data]. Some of this (much?) is documented. I recommend you Read The Whole Thing as well as commend the documentation team for their valiant effort.

I of course, am not going to read the documentation because…because I eat pain like candy. Instead, I shall follow the path I usually take: blind stumbling about while I try to do something, anything, of use. It’s just how I roll. Later, after frustration overtakes me, I’ll do the manly thing and pretend that I didn’t read said documentation. J/k, I do read documentation.

Let’s begin

Let’s start off in the web UI. While I reviewed this in my last post, it bears repeating that the hierarchical nature of EAS is not reflected here and one must pay attention to the context of the page. In the page below, I’m at the server level.

I read the latest news, No buttons on my shoes

Oracle provide a series of predetermined templates for you to download to jumpstart your Excel/Cube Designer work.

Here’s the list. While I see good old Sample.Basic (Jason, honestly, did you think that SdotB would ever die? Really? When Essbase is dead and buried, sure. But Oracle and I have kept the faith.), my heart beats a little faster but I’m really interested in the Hybrid version of this. “Pure” BSO is so 1992.

Inconsequential rants aside, I’m going to download the Cube Designer manifestation of MVFEDITWWW to my local drive (oooh, I’ve finally sort of joined the Win 10 world albeit on my own terms and on a VM).

There’s the template in my Downloads folder:

Without going through every sheet, I see all of the metadata bits that I expect:

Application, database, and dimension sheets aside, it’s worth noting the last three: Data.Basic, Calc.calcall, and Query.Sample. It looks as though more than just dimensionality is possible within a Cube Designer workbook.

If we look at Data.Basic it’s the same Essbase data we (well, at least I do) know and love.

This is a bit odd. This is a Hybrid-enabled (actually, everything’s Hybrid-enabled as it all goes through the Hybrid query processor – this is true in on-premises as well) database with all aggregations tagged as dynamic so there isn’t really much point in running a CALC ALL. In any case, it’s illustrative of how calc scripts are written. As I haven’t (and won’t for this blog post) tested writing one, I think that each calc script gets its own Calc.calcname sheet.

There’s even a sample query sheet to help you (someone else?) see what the data ought to look like on load. I’m not sure I understand why this is better than a simple Smart View ad hoc retrieve from scratch but it’s a nice touch regardless.

You know I went to school, And I'm nobody's fool

Editing dimension metadata is as simple as typing into a sheet or using the functionality of offline Cube Designer.

Simply click on Cube Designer in the Cube Designer Excel ribbon and then the From Sheet button in the lower right.

Ta da, you now have a fully disconnected (for now) Essbase database editor.

The Dimensions tab controls metadata columns. In the case of the Measures dimension, I see that Solve Order isn’t visible. By selecting Solve Order and then To Sheet, this:

Becomes this:

And yes, I could have simply inserted a column, assigned a header row value and have achieved the same goal. But, as has been explained to me, I am lazy, hence the use of Cube Designer.

Adding a dimension member and assigning properties is an odd mix of CD functions and you-better-know-your-Essbase-Load-Rule properties.

A Load Rule by any other name

To add a member to a dimension, I must:

Define the child (not hard) and its parent (could be a bit tricky in a large dimension)
Put it into the right order
Know Essbase Load Rule properties for storage, consolidation, Two Pass, variance reporting (financial intelligence), time balance, formula syntax, solve order, etc. as well as which properties are valid with what, e.g. setting Skip Missing when Time Balance isn’t defined will end up with a FOOM! To be fair, this is all true in an on-premises Load Rule but at least there’s the option of a fully-functional UI in the form of EAS. In Cube Designer, no one can hear you scream.

In any case, since I in theory know all this already I’ll get this right the first time round. Actually, I did. I must grasp the small moments as I may.

All I want to do is create a Measure called “Cameron’s test”, make it dynamic calc, do not aggregate, and have a formula of Sales less COGS. Easy peasy.

Here it is without the formula. Again, I could simply it directly into the cell.

Or, because I am the fancy sort of chap who revels in his sartorial splendor (if you can call Fjällräven fancy) that you have seen at Kscope over the years, you’ll know I go 1st class and pick the formula editor. Btw, that’s me in the center with the cocktail glass and the smug look at last year’s EPM Monday Night Event. Yes, correct dress is required in ODTUG, cf. the ladies’ frocks. While board dinners are strictly white tie, this was an informal affair. Did I mention that, as per tradition, it ended with the Lambeth Walk? Oi!

Although it is quite possible that most of the above paragraph is not true, I did in fact use the formula editor.

Jack of all trades, master of none

And what does Port Out, Starboard Home buy me?

Oh dear, that’s a bit more like steerage. There is: no syntax checking, no choice of a monospaced typeface (that’s not as petty as it sounds), no autoexpand, no tabbing, etc. It’s just a textbox. This bit of functionality has a way to go.

Whining aside, I enter my formula, click on Save, and the formula is written back to the sheet.

In the same vein of this is a 1.0 release, when I click on View Hierarchy I get the beginnings of what will one day (let us pray that it be soon because this has real potential) a highly functional dimension editor in Excel. Seriously, this will be great but it’s early days still.

In the meantime, hierarchy move (Go To), rename, edit some parent properties (everything to stored or dynamic – a nice trick to “Hybridize” a dimension), and find is pretty much it. As noted, a good start.

Now that I’ve modified the Measures dimension, it’s time to manifest this as a database in the cloud by clicking on Build Cube and then logging in. Note that after the initial download of the template, all work has been dynamic. Why, you could pull down a database and work on it in your unbelievably-cramped aeroplane seat in what has to be a class below Steerage ‘cos that’s how your Hmbl., Fthl., & Obt. Svt. wrote this post. No connexion to the network required until this loading step.

You’ll be asked if you’re sure and if you want to blow away any application with the same name.

Are you DoublePlus sure?

Yes? Then let’s begin.

And how is everything going?

It’s not possible to tell without going into the Details:

Just when I think you're mine, You try a different line

Closing this dialog box, refreshing the main job list, and then viewing progress via the Details button will, eventually, tell you if your process succeeded or not. However, there’s an easier way and it’s spelt b-r-o-w-s-e-r.

It’s the autorefresh option in the web job console. I’m not sure why the below screen shows a job in progress and also says that there are 0 active jobs.

Ta da, it’s done:

I can see this Cube Designer’s job console as well:

Is there data there? Let’s start up an ad hoc query:

Yup, it sure is.

So that was Sample.Basic (dynamic) from server template downlaoded to a Windows client manifested as a cloud Essbase database.

You get me in a spin, Oh what a stew I'm in

What about pulling an existing Essbase cloud database down to client without a predetermiend template. Does everything come across? It better. Or does it?

Export the database:

Here it all seems to be. When I ticked the “Show Advanced” box I can then see the dimensions and even pick the dimension build type.

I now have a new file named Sample_Dynamic_Basic.xlsx”. How many ways can Oracle combine those three words? Watch and find out.

It mostly looks the same:

But there is no data in the data sheet. That’s odd as I selected the “Add data sheet” option. Perhaps that means create the sheet but do not populate with data? Why? I guess I sort of see and that is after all exactly what the option states. It’s still odd.

Also, there is a calc up there, but no corresponding calculation sheet:

But what is there is this zip file:

What oh what oh what is inside it?

And that looks like this:

IOW, Sample.Basic (dynamic)’s level 0 data.

But baby, I'm confused about you

What happens if I do this via Cube Designer?

A promising start:

It works a few moments (okay, like 60 or so seconds) later:

What ho! More of the same but this time no data sheet or calc sheet. This isn’t a big deal, just a bit perplexing.

The download location is not selectable.

DBX? Whatever that is it’ll have to wait for another post. In any case, we now have three possible names:

Sample_Basic_Dynamic.xlsx
Sample_Dynamic_Basic.xlsx
DBX_Sample_Basic_Dynamic.xlsx

The naming convention isn’t really important but it’s interesting/odd how Oracle came up with three (really just the latter two but still) naming conventions.

What if I want to load data? It’s easy enough to create a new (or, because I am lazy, copy) data sheet and then copy and paste text file data into the sheet:

NB – Make sure you format the cells as text and the paste puts everything into the right columns most licketysplit.

Okeydokey, there is now data in a data sheet that looks just like the one used to populate the workbook. I have changed it by multiplying 100-10, New York, Sales and COGS, Actual, and Jan by a factor of 100 to prove that I can load the data.

Surely it will just go in without a load rule as there was none the first time round.

It failed. Unpossible. Yet there it is.

What happened is that the original upload of the application created a load rule for data, just as it did for all of the dimensions. And when that happened, the ability to send data from Cube Designer seems to have gone walkies. But there is a load rule called “Data”.

Trying that gives me:

Yes, indeedy, I want to see the results:

And that does not show me my new numbers:

I'm so sure that you'd be good for me, If you'd only play my game

What’s going on? When I created the database the load sheet worked. Why not now?

Remember those file objects? Could it be that there’s also a data file? Could be.

If I go back to the web, select Sample.Basic (dynamic) and navigate to the Files button, I spy with my little eye a data file called Data_Basic.txt. Note that this a different name than the Basic.txt file I downloaded via the database export a while back. Why?

Note also that it does not export as a zip file but instead as plain text.

It turns out that the file itself is a bit different in that it only has line feeds as an EOL, not line feeds and carriage returns. Why?

Notepad sucks, but Notepad++ most definitely does not and shows me the file in columnar format:

Note the new data values.

If I then save the file back up to Essbase:

I see it there in the Uploaded Files dialog box. All I need do is Upload it directly to the database folder:

Refresh the list:

And there it is:

Unlike in EAS, there is no right click on a database or navigate through a menu to load data. Instead, navigate to the Jobs button and then select New Job:

This is a data load. Why the load rules are called “Scripts” is a mystery to me.

Execute it and it seems to have worked. Let’s check:

Everything seems tickety-boo:

Ah, but the proof of the pudding is in the eating. And there it is:

Yummy.

I'm a little gem in geology

But that’s a drag. What happens if I want to go through Excel? We know that Load Data doesn’t work and that on initial create, data loads do happen. Can it be done again via Build Cube?

Yes it can. Why the dialog box says “Retain All Data” when in fact I’m trying to replace data is Yet Another Essbase Cloud Mystery. ‘Cos it works:

I guess I’d better get out the encyclopedia, And fresh up on from "shmer" to "shmoo"

This was quite the journey, wasn’t it. I thought about breaking this up into multiple posts but if I did that I’d likely chicken out from showing you each and every step. I’m weird (and lazy) that way.

What really have we uncovered?

I’m nuts for writing a 44 page post (in MS Word). I really should have split this up but oh well.
There are multiple ways to do things in EssCS such as extracting a metadata set.
Cube Designer’s Cube Designer is a really cool concept but it has quite a long way to go before it’s ready to replace EAS’ dimension editor.
Metadata extracts don’t work exactly the same way.
Loading data once on database creation works from Excel. After that, nope, because that process uploaded a data file to the database.
Once in Essbase, downloading the text file that was created from the create database process, modifying it, uploading it, and then creating a job to load the data works.
But running the Build Cube process again and selecting “Retain all Data” while changing the data works.
There are many, many, many Essbase Cloud Mysteries. Some if it’s ignorance on my part, some of it has got to be weirdness on the part of a 1.0 release.
The process of creating a database from an Excel metadata workbook uploads both load rules and dimension and data files.
Essbase Cloud is a work in progress but it shows great promise.
I don’t know enough about Essbase.

Seriously, there’s much to learn here and there are many quirks that have to get fixed and soon. Regardless, this is Essbase’s future. We just have to be a bit patient getting there.

Be seeing you.

P.S. In order, the Great American Songbook’s, “I Don’t Know Enough About You”: