Dodeca 7.3 Workbook Scripts Enhancements Overview

Today I’d like to continue on the theme of new functionality in the latest release of Dodeca, version 7.3 that came out some time ago. I talked about the new Outline Viewer functionality and MDX enhancements already, today I wanted to give a brief overview of some of the new functionality in Workbook Scripts (WBS).

If you’re not familiar, Workbook Scripts are optional scripts that can be added to views that provide advanced functionality inside of views. You can think of it as a modern, more maintainable analog to VBA, optimized to work in a world of Essbase-based data on spreadsheets. Dodeca 7.3 included some new methods and functions. There’s also updated Dodeca 7.3 WBS documentation available on the web.

New & Updated Procedures

CopyFromBinaryArtifact

The new CopyFromBinaryArtifact method is potentially the most interesting new method to show up. There are overloads for copying a whole sheet or copying a range from a particular binary artifact (i.e. a completely separate Excel sheet than the current view). So this potentially allows organizations to centralize some information, formatting, data, or other stuff into a central sheet, then refer to it. I could see people using this to store small amounts of data that they want to reuse in a view (thereby obviating the need to put the data in a bespoke relational table/cube), or copying some common formatting elements into a new view. So, this new method potentially opens up some significant maintenance savings and other possibilities in a lot of cases

OpenApplication

OpenApplication can be used to launch a different application instance from the current one. This can be useful for user convenience, where instead of telling the user that they need to go to a separate app, you can launch it for them.

SaveWorkbookAsCsv

The SaveWorkbookAsCsv method is pretty much what it sounds like. In addition to being able to save the workbook as a CSV file using the UI, you can perform this via WBS. I see Dodeca used in many cases providing some quasi-ETL functionality, or being used for large data dumps, so this could be handy. For example, maybe you want to allow users to drill into relational data and then automatically download it to their desktop.

AddDataCache: EssbaseMDXScript

There’s a new EssbaseMDXScript overload available as part of AddDataCache. The Data Cache functionality is really handy in many instances, especially where you want to execute an “expensive” (in terms of query time) query but then be able to do multiple things with the data without having to re-run the query. Now you can cache data based on an MDX query in addition to the normal options with report scripts, SQL, and more.

SendEmail (new Zip options)

SendEmail gained a couple of new properties to zip attachments. This method is already used quite heavily to have Dodeca generate and email large, complex books of reports (Excel and PDF) and mail them around. This is quite frequently used as part of batch reporting. This functionality is used so extensively by so many organizations that it is now useful to compress the output into a single file and attach that to the email. Now with a single option you can do exactly that.

Miscellaneous

There are a few other tidbits here and there like a new attributes property on the EssbaseReport method, some new convenience functions like getting the first column letter, last column letter, checking if a binary artifact exists, if a view is shared, and more.

Read the Release Notes!

The release notes that ship with the Dodeca distribution contain pretty detailed notes on all of the new functionality and features, so be sure to peruse those for other new functionality you might be interested in. If you are planning to use some of these new procedures/methods, please let me know if you do and how useful you find them, I’d love to hear about it.

 

Dynamic Essbase Connections in Dodeca: Faster Cubes and an Enhanced User Experience

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.

Continue Reading…

Dodeca Zebra Striping with WBS Example

I wanted to punch up a Dodeca view the other day by putting a little zebra striping on some relational data. Although having built-in support for this is on my wishlist, for now a simple workbook script (WBS) gets the job done. This is also alternatively called “greenbar”… depending on what decade you were born in.

The technique itself is pretty simple. You can accomplish this in a few ways in Dodeca (as with everything), so here’s one way to go. First, when the view is opened, we have a workbook script to set a color index (that’s the first step in the following screenshot). In this case I am setting a very light grey to be color Index 2.

In the next step (the one that actually does the striping), I have defined a simple method that applies to a range named “Address” and just paint every other row depending on if it’s even or not. I’m just using the formula =MOD(@CRow(), 2) = 0, which is a normal Excel function (modulus), and a workbook script function (@CRow()) that returns the current row number being processed. If it’s even, then the cell should be painted. If not, nothing happens. So if you wanted two different colors you’d just add a new color set step and a new SetFill method that applied to odd rows.

Here’s a screenshot of the full WBS:

Screenshot of Dodeca Workbook Script editor showing how to implement a zebra striping technique

Dodeca WBS Zebra Striping Example

Jazz Up Those Static Dodeca Views With Advanced Essbase Features

Oftentimes when I am demonstrating or teaching aspects of Dodeca to people, they are amazed at the sheer number of options and configurations that are available on a view. Fortunately, I am able to tell them that yes, there are many, many options – and they are there if you need or want them, but they won’t get in your way. The defaults are very sensible and getting a basic Essbase-based Dodeca view running is incredibly easy.

Another thing to keep in mind is that for the most part, the extreme amount of options and flexibility we have on a single view is often available to us in lieu of code. So, tasks that typically required some non-trivial amount of VBA code are now completely code free. When we need some advanced functionality that isn’t available out of the box, we can use Workbook Scripts, which is an event-driven scripting technology that is particularly well suited to working with spreadsheets and the data contained in them.

That all said, today I want to walk through a bit of a cross-functional example that starts with a very typical Dodeca view based on an Essbase retrieve range, then enhance it to give our users the ability to zoom in on the different time periods in the view without having to rebuild the view. So we’re going to blur the line a bit between static and dynamic reports, and our users are going to enjoy some additional flexibility and convenience with regard to their reporting (and keeping users happy is always a good thing, right?).

Continue Reading…

Dodeca Techniques – Auto Load Windows User Name During Relational Input

Today I want to look at a practical example in Dodeca that came up while I was at a Dodeca training workshop for a client the other week. We know that Dodeca can update data in a relational database using its robust SQL Passthrough DataSet functionality. And we know that it can automatically use values from the selectors to update columns as needed (for example, if the user has selected a Scenario of Actual, we could use a token such as [T.Scenario] in the SQL query).

Now let’s say that we have a table with data, such as headcount forecast data, and for whatever reason (auditing), we want to record the username of the user that is inputting the data. To achieve this, we can use a single Workbook Script method and a function to dynamically insert the Windows username of the current user into the view, and then use that token just as we would use any other token. In this case, we’ll use that token in the INSERT/UPDATE statements on the SQL Passthrough DataSet. Note that none of this configuration has anything to do with security, per se, it’s purely to have a column in the table to make it easy to see who updated/inserted a given piece of data.

Continue Reading…

Data Input with Dodeca, part 6 – SQL and Essbase Hybrid Input in one View

The last article on relational data input with Dodeca was a bit epic – I was planning on something a little shorter and sweeter for this next article, but it’s going to be another long (but awesome!) one that combines everything we’ve seen so far in this data input series, and more. To recap, the series so far has consisted of the following articles:

Let’s get crazy today with a soup to notes implementation where we’ll input relational data and then load it to Essbase automatically so that the data ties out. You might call this “home-brew hybrid”. As with before, it’ll be based on our favorite database in the whole wide world, Sample/Basic.

Consider the Sample/Basic dimensionality: Year (time periods), Scenario, Market, Measures, and Product. The use case that I’m going to look at today will cover the scenario where we want to prepare a budget, by product, by time period, by region, but have it be by employee. But this dimension doesn’t exist in the cube – no problem! Let’s further stipulate that for either architectural, performance, or other reasons, we absolutely do not or cannot put in an Employee dimension. So what we’re going to do is have Dodeca facilitate inputting data by employee and feed that into a relational database, then we’re going to use some simple Dodeca automation (workbook scripts) to take the sum of the data we input (for the given time period and market and so forth), send it up to Essbase, do a focused calculation on the cube, and then retrieve the updated data to show on the exact same sheet that we’re already on. Continue Reading…

Data Input with Dodeca, part 4 – Focused Calcs

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

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

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

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.