Category Archives: Workbook Script

Workbook Script functionality in Dodeca

Dodeca Dynamic Build Example & Walkthrough

Today I’m going to walk through a multi-faceted Dodeca example that shows off several different concepts and techniques. We tend to conceptualize Dodeca applications and solutions in terms of making Essbase even better and today’s example is a perfect example of how we do that.

Think about it this way: your organization spends an enormous amount of time designing the perfect cube – the proper dimensionality, formulae, calc scripts, data, and more. Often, the cube serves multiple units or departments and they each have their preferred way of looking at things: hierarchies, views, and more. On the developer/Essbase side of things the functionality is solid and feature-rich. But there may be compromises on the user/interface side of things. This is where Dodeca and the example I’m walking through today really shine. It’s a practical example of how Dodeca can make Essbase better by creating a highly focused and tailored experience for the user. Continue Reading…

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

Dodeca Workbook Script documentation available online

I kind of posted this on the down-low earlier this week but got outed by my auto-tweet feature, where it got picked up by Oracle EPM Blogs and a few others, so I thought I should just write about this for real.

Earlier this week, the new documentation for the Dodeca Workbook Script functionality went online. In case you’re not familiar, Workbook Scripts are part of Dodeca Spreadsheet Management System’s event-driven extensibility model. You can kind of think of it as an elegant blend of the best aspects of Microsoft Access macros and Visual Basic, but designed from day 1 to make it easy to facilitate really sophisticated functionality in views that contain Essbase, SQL, or MDX data (or all three on the same sheet!)

The documentation contains a full index, all methods/overloads, events, and functions. It represents one of the first major steps towards my goal of making incredibly high quality documentation and online resources available for people that are developing with Dodeca. In the future I am hoping to get even more documentation online with samples and other resources that make developing even better.

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…