Category Archives: Techniques

Tips and techniques for working with 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: Turn on Auto Build button by default or hide it entirely

A customer request came in the other day asking if it’s possible to turn the Auto build button on by default. You can see this button next to the green build button in this screenshot:

A Dodeca view with Auto on turned on by default

Validating in our view that the Auto on button was indeed on by default

Given a default set of toolbars that ship with Dodeca, this button typically starts in the “off” state. In case you’re not familiar, the effect when it’s on is that any time you change an item in a selector, the current view will be rebuilt. This can make it really convenient to browse through different views of data without having to press the build button (you can see this in the video at the end of this article). It’s just one of those usability things that makes interacting with data just that much more fluid.

Continue Reading…

Dodeca: Create and use a new Essbase MDX Selector List

The most recent version of Dodeca brought several exciting enhancements for MDX-related functionality. One of these is a new selector list based on a reusable MDX script object. Although MDX queries are probably most often associated with queries that return numerical data from a cube, they also have incredibly useful metadata capabilities that can be employed for various purposes. In Dodeca, it’s common to use a report script or member query specification to return members from an outline. For example, you might want to provide your users with a selector such that they can choose a particular product (or products) from your Product dimension in order to customize a report that they will build dynamically.

I see MDX scripts as being a natural, clean, and flexible way to populate these selectors, and moving forward I will recommend them whenever possible over the more arcane report scripts that have been around for years.

That all said, what I want to show today is the following: I’m going to edit an existing Dodeca view so as to replace one of its existing selector lists with a new list based on an MDX query.

Continue Reading…

More on Dodeca Dynamic Connections: Allowing Users to Select Multiple Connections

Last week I wrote about how you can configure user selectable Essbase connections on a Dodeca view. One of the comments on the blog article was from a Dodeca customer that said, “Hey, that’s great and all, but what about a Dodeca view with multiple Essbase connections?” In other words: Can we setup a Dodeca view that pulls data from multiple user-selectable connections and have data from different connections on the same sheet? The answer is yes – although the configuration is just a tiny bit different than what I thought it would be (it was actually simpler). This blog post will walk through how to set this up.

Continue Reading…

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…

User Selectable Cube Connections in Dodeca

A request came in the other day asking if it was possible to make a Dodeca view’s connection dynamic/selectable by the user. For example, say you are rotating through cubes every month that are essentially the same outline but just have different data. You might have the January cube, the February cube, and so on. This is a somewhat unorthodox, but certainly not unique design approach that I have seen over the years. Among other things, this approach can help keep a cube very manageable/fast when an organization’s data needs and processes might otherwise require an entirely new dimension or other dimensional shenanigans in order to facilitate the necessary reporting, planning, and forecasting activities.

To start, since connection objects in Dodeca are centrally managed it is certainly possible to just update the connection details as needed and point to the proper cube. But in this case we need a little more power. Can we let the user choose the connection for their own Dodeca view? Absolutely. I’ll show you how in this article. 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 Technique: Multiple Essbase Data Sources in View

When I’m talking about Dodeca features, one that very often comes up is that Dodeca views have great support for multiple data sources. I’ve seen customers and clients use this to give them a cutting edge in terms of developing reports that tie together information from disparate data sources in a flexible way that was previously very cumbersome or impossible with the tools at hand. Among other instances, this feature comes into play when it would be beneficial for a user to view data that happens to reside in multiple databases, but for the sake of the user experience, we don’t want them to have to run multiple reports.

So today I want to look at a very simple Dodeca view that taps into multiple sources. There are a couple of nuances to consider for this development scenario. Consider that a typical view with a single data source will just have its connection specified explicitly as a property on the view, and the selectors on the view (if any) will assume that they are to be populated based on that connection as well. For example, let’s say we have a view based on the Sample/Basic database, and we have two selectors that are dynamically generated: Time and Product. When Dodeca goes to generate the list of Products to display to the user to make their selection(s), it knows to use the Sample/Basic database. However, if we want to have multiple selectors and have their contents be based on a particular cube’s outline, then we need to simply associate the proper connection with the selector.

For today’s example, I’m going to build a simple view that has one tab based on Sample/Basic and another tab based on Demo/Basic (as a brief aside, Demo/Basic is Sample/Basic’s less popular, less-talked about sibling that is eagerly awaiting its day in the spotlight). Note that while this example will have multiple Essbase connections and multiple selectors (one on each database), this isn’t necessarily how a view will always need to be configured. If you have a selector whose contents aren’t dependent on a particular database, then you wouldn’t need to worry about the connection specification for that selector.

Continue Reading…

Dynamic Calendar with Comments in Dodeca

I keep telling myself that I’m going to do more blog posts that are short and sweet, instead of these epic 6,000 word monsters, but I’m just having too much fun. Today’s article is going to be a little bit of thinking outside the box. Outside the box – but inside the grid. This is actually inspired by a use-case I saw a Dodeca customer present on at Kscope this year.

The basic original idea was “Why not make a calendar view in Dodeca?” Those of us that are heavy in the Essbase/Excel world are used to modeling financial data, but spreadsheets are used for countless different activities. Create a workout plan. Create a list of your favorite movies – and even make a calendar.

In the context of Dodeca, a calendar, whether it be static or dynamic, is a really cool use of the tool, if a bit unorthodox. A lot of financial departments and companies have very complex but methodical financial processes, particularly around the “close period”, and keeping everyone on track and coordinated is important. And companies that have Dodeca already have a very quick and very easy way to make dynamic spreadsheets centrally available to their users without having to email around a bunch of Excel files.

For today’s post I am going to start off with a basic calendar, then absolutely turbocharge it. The user is going to be able to select a month and year from Dodeca selectors and the calendar will dynamically update. We’re going to make it so we can add comments to each cell of the calendar. The comments will be associated with arbitrary intersections of our choosing (a great feature of Dodeca comments that I’ll go into extensively in this post). We’re going to accomplish this using the built-in Dodeca comments functionality. Along the way, I’m going to show off some of the power and versatility of Dodeca comments and use practically every option available.

Continue Reading…