Today’s article continues my series on data input with Dodeca. This post will be an elaboration on the basic data input to Essbase shown off in part 1. As a quick refresher, part 1 just looked at setting up a view that allows a user to input data to a given Essbase intersection. We made it a little more interesting by allowing the user to choose their Market (from our favorite database in the whole wide world, Sample/Basic). Now we want to take it a step further and run a calc script after the user inputs their data. This is pretty typical requirement because data in the cube often needs to be aggregated after lower level inputs.
Achieving this functionality is pretty straightforward. We also have some interesting possibilities because we aren’t limited to just running a static calc script on the server – we are afforded all of the normal Dodeca token replacement functionality so that we can focus the calc however we want. This can be incredibly advantageous for performance reasons. For example, rather than running a calc that refreshes all of the data across the cube, we can focus it on a particular cost center/region/functional unit based on the current POV. Why recalculate data that doesn’t need to be recalculated? Speed up the calc – speed up the user experience.
Cleaning up Anti-Patterns
This technique also let’s us cleanup an Essbase anti-pattern I have seen time and time again out in the real world. Imagine a company that has several managers that control different markets. For example, there are separate managers for New York, Washington, and California. Up until this point, the company has managed to get away with a process that involves doing a classic Essbase lock and send to the proper market, then choosing a calculation to run. The list of calculations might contain the following:
- BdNewYrk
- BdWash
- BdCalifor
All of these calc scripts contain effectively the same script, differing only by that they FIX on. For example:
FIX ("New York", "Budget")
CALC DIM ("Measures");
ENDFIX
The “run calc after data send” pattern in Dodeca lets us clean this up and consolidate down to a single calc that will simply plug in the POV from the user’s current Market selector. Let’s take a look at how to set this all up.
Introduction to Workbook Scripts
I’m going to leverage the exact same view as part 1 of the series, and simply add a Workbook Script to it. I’m going to get much, much deeper into workbook scripts in the future, but think of workbook scripts as the procedural side of Dodeca views. They are like a unique but approachable blend of Access macros and VBA functionality. Any view can have a workbook script attached to it. Inside of the workbook script, we can define sequences of procedures and attach them to particular events that can happen to our view.
In our case, what we want to have happen is that after the user submits data to Essbase (the AfterSheetSend event), we want to run a procedure that runs a calc script.
Tokenize the Calc Script
The very first thing we need to do is create the calc script that we want to run. This will be a normal server-side calc script, with a twist: replacing the market with a token. Here’s our script:
FIX ("[T.Market]", "Budget")
CALC DIM ("Measures");
ENDFIX
Note that the market is replaced with a token, just like the tokens that are used on a normal Excel view. Also note that the token is enclosed in double-quotes. Dodeca will perform a full and literal token replacement. So we want to make sure that if the market is New York that it is put inside of the double quotes so we don’t end up with a syntax error. I’ll save the calc as BdMarket.
Create the Workbook Script
Now we head back over to Dodeca and create the workbook script. We can create a workbook script as with any other major object in Dodeca by simply navigating to Admin → Workbook Scripts, then selecting New. Nicely enough, the Workbook Script editor provides a rich environment where we can define most options and items by simply selecting them from a dropdown menu. Consider the following screenshot, showing everything we need:
Dodeca Workbook Script Editor
In particular, see in the Event Links pane that there is a definition that associates the AfterSheetSend event with the CalcMarket procedure. Next, look at the Procedures pane containing all of the procedures in this workbook script. There is just one, the CalcMarket procedure. In the workbook scripting world, there are many, many functions available to us to choose from. In Dodeca parlance, these are known as methods. For many methods within Dodeca, there are multiple versions of it available, these are known as Overloads. These terms are borrowed from the world of object-oriented programming. Think of the overloads as slightly different versions of a methods but with the same name.
In this current case, the method I’m using is the EssbaseRunCalc method. This particular method has several overloads available. These are General, TextBased, ServerBased, and DefaultCalc. Most use cases will probably be satisfied with TextBased or ServerBased. In the case of TextBased, we can define the entire calc script locally (inside of this Dodeca procedure) and run it on the server. With ServerBased, it’s a calc script that resides on the server, but we still get to perform token replacement on it.
I think what makes the most sense in this case is that we use a ServerBased calc script and include token replacement within it. Don’t be overwhelmed by the numerous options available to us. We can live with the defaults for just about everything. The only thing important that we need to specify is to tell it the name of the calc script (the ScriptName value), and to make sure that DoTokenReplacement is set to TRUE. These should hopefully be self-explanatory by now, but it’s worth pointing out that if we just wanted to run any given server calc script without worrying about tokens, we could just leave the token replacement value set to false.
With the workbook script created and saved, we now simply need to associate it to the view. This is set in the Workbook Script category:
Assigning a Workbook Script to a View in Dodeca
Lastly, after we change some data in the view and click on the Send button, we can go back out to our Essbase server and see what happened on the cube:
Viewing calc script execution results in EAS
You can see in the log that the current POV was used (New York) to replace into the script text, and the resulting script was executed. We can replace any number of tokens if need be, focusing the calc even more. This can frequently be a win for organizations with a wide/deep outline, and many forecasters that need to see aggregated data – but can’t wait for a more general calc to run. This technique can also frequently significantly streamline the technical side of things (fewer calc scripts) and the user experience (as compared to manual input with Excel spreadsheets). It can also potentially help you clean up your filter/calc security situation, in that you can let the user piggyback off their existing read-level access without having to dole out access to a particular calc script.
