I’m back from Kscope16 (recap coming soon!) and getting back into the swing of things. Needless to say, Kscope16 was another absolutely amazing conference and my three presentations all went pretty smoothly. While at the conference, I got to speak with a lot people about our products, what they do, and how they work. Along those lines, something that came up over and over again in one way or another was that many organizations are performing data input to Essbase using the classic Lock and Send technique or just using the Submit Data button on their Essbase toolbar.

From a purely technical perspective, this can work just fine. From a business perspective, there are numerous pain points. I’ve seen this play out at companies in a handful of ways:

1. Users have varying levels of Excel/Essbase skill. They are given pre-formatted Excel files and instructed to follow a very specific sequence to enter data into a sheet, connect to the Essbase server, select a range, press submit (or lock, then send), and perhaps run a calculation
2. Users are asked to fill in a template and email these to a power user or admin so they can properly load it into Essbase using their own template or process
3. Many hours are spent writing a mini-program in VBA and handing this file out to the users, wherein they are just asked for their username and password, but the automation otherwise hides all of the gory details of connecting and sending data to Essbase.

All three of these situations are rife with complications and things that can go wrong. Off the top of my head, note the following issues in play here:

1. Varying/extensive amounts of training are required to get users up to speed on how to use Excel, properly format data, and more
2. The power user/admin is burdened quarterbacking and marshaling significant amounts of data into the cube. This is often a highly paid individual whose time is better spent on other activities, such as development or system improvement
3. Resist the urge to spend significant amounts of time developing a custom VBA solution. It will always take longer than you think it will and it can quickly become a maintenance nightmare

That all said, one of Dodeca’s core features is the ability to handle data input from users. It excels at this in much the same way that data retrievals work, meaning that we can create an arbitrary spreadsheet to collect input, define what range(s) should be sent up to the server, using any Essbase connection we want, and then optionally performing some action, such as running a calc. For the rest of this post, I’ll be showing a very simple example of this to give you a feel for how this works.

I’m going to modify the simple template that I used for my blog series a couple of weeks ago. This template is based on the Sample/Basic database. In this example, I’m going to fix (hardcode) the product but allow the user to change the Market. This is a bit of a contrived example (normally we’d want the user to budget for multiple time periods) but it will demonstrate the basic functionality. I’ll expand on more complex examples in the near future.

To start things off, let’s take a look at our simple input template:

A simple Dodeca data input view template

In particular, note that I have used a normal selector [T.Market] to indicate that the market selected by the user should be plugged into the template. So you can think of data input in Dodeca as an elaboration on the normal Dodeca report/retrieve paradigm: we get to use the same spreadsheet/token/selector functionality as before, and simply extend it to send data back up.

In this particular example just to simplify things a bit, I have chosen to hardcode the product (Cola) and the time period (Jan). In a future example I will make those into tokens. As with before, I have decided that for visual/aesthetic reasons, I would like to take the current product and time period and use those values to put a “nice” title elsewhere in the sheet. This is accomplished with a simple Excel formula to concatenate the values using cell references, as shown here:

Dynamic template title using Excel formula

Now, one interesting difference in our input sheet versus our normal report sheet is that we are going to use an Excel formula to calculate data as it’s entered and provide the user some instant feedback on the values they are entering. Consider the following example, noting the formulas for Margin, Total Expenses, and Profit:

Regular Excel formulas dynamically calculate user-input as it is entered

If we were just creating a normal report of this data (meaning it was a retrieval, not a send), we probably wouldn’t have these formulas here, because the data would be coming directly out of Essbase. In the case of input, however, there is no harm in putting a formula here, and indeed, that’s exactly what we want. Remember, we get to leverage all of the power and expressiveness of Excel in our templates, so we can include just a normal Excel formula in these rollup rows (since the user won’t be entering a value for them anyway), and as the user enters data we can show them the dynamic total. This is a somewhat subtle nicety that I think is worth noting. Among other things, it obviates the need for the user to, say, enter a value, submit it, calculate the cube, retrieve, and see what their running total is. Again, remember, we are looking for that polished and intuitive user experience.

Given the way this template is constructed, we need to let Dodeca know what the range of cells is that contains data to be sent to the cube. This works exactly the same as it does for retrieval ranges, just with a slightly different name. Shown in the following screenshot is the special named range Ess.Send.Range.1 that let’s Dodeca know (in conjunction with our SendPolicy) where the Essbase data range is that should be sent to the cube.

With the template and view saved, I can now run it:

Our built input view showing current values for the Budget for this POV

Let’s see what happens when I type in a value for Marketing and hit enter:

Dodeca input view, shown with rest of Dodeca client for context

My Expenses value was dynamically updated via its Excel formula, and in turn, my profit value was updated.

Do you see the problem with this data? If you said, “Wait a second, you just increased your expenses but the profit went up, what gives?” – you’d be correct (and astute).

We aren’t limited to just addition or simple sums – again, we can use just about any Excel function that we want. In this case, a better formula to use would be one that subtracts the expenses from the margin and shows the value. I simply go back into my Excel view template, update the formula in cell C16 to actually be =C11-C15, save the template, and re-run it.

Now check out my dynamic totals:

Structure with Flexibility

I hope you enjoyed this simple example of user input directly to Essbase that is facilitated by Dodeca. In the coming weeks I’m going to show off some really interesting examples that are more involved, but I definitely wanted to start off with the basics. I think this is incredibly relevant due to the apparently huge number of people I have talked to (especially last week at Kscope) that have a very cumbersome input process fraught with Excel sheets flying back and forth via email, extensive user training, and sometimes performance issues. Even if I was the manager of a relatively small finance team (especially if including non-finance users), I would be looking for a tool that provided me enough structure to make the process streamlined and straightforward, while maintaining flexibility: adapting a process to my business rather than adapting my business to a process (or technology). In this regard, Dodeca delivers.