The other week I showed an innovative approach to providing user-selectable Essbase connections from a Dodeca view. I’m going to continue on the subject of dynamic Essbase connections this week, but with a bit of a twist. I’m really excited to show this technique off because it’s a perfect combination of showing the flexibility that Dodeca provides, but perhaps even more importantly it speaks so strongly to our raison d’être: making Essbase better.

Current Cube vs. History Cube

Many organizations spin off a copy of their cube each year or periodically when they need to boost performance a bit. Typically the major win for performance here is that you can drop a year or more of data, often by literally deleting a member or two from the Years dimension. In an ASO cube, this can significantly cut down on the amount of data in play (thereby increasing some combination of load and query performance), and in BSO databases, the effect can be even more dramatic, particularly depending on whether years is sparse or dense. Years (FY17, FY18, etc.) is typically a sparse dimension, but is sometimes dense, which could yield even more reasons to try and keep it as small as possible.

Transparent partitions are also often used for carving up a cube into multiple sub-cubes (and then providing a unified/all-years cube to end-users) in order to achieve better single database performance, but partitions can also introduce a fair bit of complexity and create administration/management issues. For a lot of organizations, it’s just nice and simple to squirrel away a copy of the cube as it stands at a point in time. But this also tends to necessitate some end-user training.

For example, if a given cube is only going to have the most recent year or few years in it, then users need to be aware of where to go to get data that’s older than that.  Generally this isn’t a big deal since most of the time the user is going to want to work with the “current” cube and not have much of a need for older data.

Implications of the Current/Historical Cube Dichotomy in Dodeca

As it pertains to Dodeca views, the current/historical cube dichotomy could have some implications for your views and applications. For example, since a view is typically configured for a single Essbase connection, you might have to make a copy of a view and perhaps its template, then change the connection. Now you have two copies of each view and possibly the templates. One view is pointed to the historical cube and one points to the current cube.

Here’s an idea though: why not just dynamically choose the Essbase connection depending on the year? It turns out that Dodeca can easily facilitate this with a simple workbook script. I’ll show you how.

Background

For this example, I’m going to work with a current and historical cube where the Years dimensions are completely identical to each other (as opposed to deleting the irrelevant years). This will simplify things for the moment, although it’s also possible to implement this technique where each Years dimension is “pruned” accordingly. Here are my Years dimensions:

Two cubes in a “current” and “historical” configuration

Over in Dodeca (by the way, yes, that’s Dodeca Spreadsheet Management System running on macOS!) I have my two connections defined (SOS_Curr.SOS_Curr and SOS_Hist.SOS_Hist, following Dodeca’s Application dot Cube convention for naming connections):

Dodeca Essbase connection configuration for “current data” cube

Now, since I didn’t already have a Selector for the Years dimension, I went ahead and created one, although typically this would already exist:

An Essbase selector for the Years dimension

Now I’ll define a custom Selector List for the Selector:

Create a selector list based on a delimited String

I’m going to use the Delimited String style of selector, where I can easily just define the different user-selectable options myself. This approach will work nicely enough for this case since there’s not really a huge need to pull the members dynamically from the cube, and in the case where we would customize the Years dimension in each cube (to shrink it down to only relevant years for that particular cube), I literally can’t source the items from the outline, so defining them manually works well.

Let’s now define a really simple template such that the only tokens used are for the year itself:

A simple retrieve template with a token in the POV to select the year

Of course, as I would in any Dodeca view template, I also set the Essbase retrieve range as normal.

Now over in the view configuration, I’m going to set the Essbase connection:

The “default” connection for the view will be the “current” cube

When I created this view, I actually used the quick start wizard and selected the Essbase connection ahead of time, so that I’d be able to use that particular connection to help me develop the template inside of Dodeca. Also, I’m making the design decision here that I want the “current” cube to be considered the “default” connection. In other words, I’m only going to change the connection for the view when I need to (i.e., the chosen year necessitates connecting to the historical version of the cube).

And of course I will use a workbook script (WBS) to help determine the proper connection:

Set the WorkbookScriptID to a new workbook script

And just for completeness, check out that I have one selector (which is the Years selector/list from earlier):

Defining the selector for the view

Some Workbook Script Magic

At this point, everything would actually be set as it needs to in order to have a “normal” Dodeca view working just fine. But the purpose of this exercise is to show how we can dynamically select the connection during the view/build lifecycle. For that, we’re going to use a workbook script. Here’s the entire script, which I’ll walk through:

A workbook script for dynamically choosing the Essbase connection for a view

For starters, I’ve defined a property (think of this like a variable in the script) called OldestCurrentYear that will denote where the break in data is from the current cube to the historical cube. I could have hard-coded this into the script but I thought it’d be nice to make it a property so that the script is more documented and its intent is more obvious. Also, if I wanted to, I could have just as easily used an application-wide token in this script. This might be useful in the event that I have several cubes that I want to use this dynamic connection technique on, and want to reuse/share this workbook script amongst them. Then I wouldn’t have hard-coded values all over the place and one-off script tweaks that I’d have to make whenever the “break year” changed.

Now over to the meat of the script. There is one event (AfterWorkbookOpen) that calls my procedure for flipping the connection if needed. Remember, the connection for the view is going to default to the current cube – so I just need to check if they actually want data older than a certain year, and change the connection if needed.

The method condition (the condition that is checked to see if the method itself is even executed) is this:

= @TVal([T.Years]) < @PVal(OldestCurrentYear)

Let’s break this down since I’m using a couple of different workbook script functions to help. First of all, I am using @TVal (token value), in order to give me the selector value (in this case, either ‘2017’, ‘2016’, or whatever) for a given token associated with the Years selector. Next, I’m checking if that’s less than the OldestCurrentYear property that I set earlier. In order to reference this property, I use the @PVal function (property value).

In the case that this condition (Excel formula) evaluates to true, then the method should be executed. In this case, the method itself is a SetViewProperty method that updates a property on the view being built. In this case, we want to update the Essbase connection (view property: EssbaseConnectionID) and set it to the ID of the defined connection pointing to the history cube). In this way, we will update the connection property based on the chosen selector value, and everything else about the view will execute as normal.

To make this really easy to validate, when I setup the cubes to show this technique off, I went ahead and cleared all of the data from the current cube, meaning that when 2017 is selected, no data should come back:

The built view when 2017 is selected

Now if I check it with an older year, such as 2016, I should see data because the historical cube has data for all years (including 2017):

The built view when a different year is selected

A Brief Note on Excel Boolean Logic

Among other principles in play, the way that I implemented this technique relies on simple boolean logic in my method condition. In this case it was using the less than operator, which given two inputs (typically numbers but can also be text), should always evaluate to true or false. If you’re not familiar with this “simple” way of thinking about logic even in Excel, you might otherwise be tempted to unnecessarily use an IF statement (which isn’t wrong or bad or anything). Just to prove that the logic works how I want it to, here’s a simple example in Excel:

Simple Excel boolean logic

You can see that this example actually compares string values (FYxx) and achieves the correct results. As I mentioned, while the less than or greater than operators are typically used on numeric data, they will also work on text data, and work just fine by simple virtue of the lexicographical ordering inherent in this data).

Some Caveats

There are of course some caveats to this technique and method. The main one is that one of the big drivers of spinning off archive cubes is not just to gain some performance, but because the other dimensions (measures, accounts, locations, cost centers, whatever) are also shifting around year to year and there might be some cleanup happening in those dimensions too – meaning that you might not be able to do the exact same retrieve with some specific member on a given cube that has changed from one year to the next. So long as your retrieves and report definition are sufficiently high-level, this won’t necessarily be a problem, but you’ll have to judge for yourself if your best approach is going to be with a separate view, application, tenant, partitions, or something else.

We Make Essbase Better

Like I said, I really liked this use-case. It’s actually inspired by a support email that came in the other day. There are so many great things about this technique because it’s this perfect combination of being able to improve performance and value, and perhaps most importantly, enhancing the user experience.