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…

Data Input with Dodeca, part 2 – Comments

Yesterday, I kicked off my data input mini-series with Data Input with Dodeca, part 1. I’m going to take that example a small step further and put in comments that a user can edit as they add data input. Yesterday I also mentioned that in terms of data input to Essbase, you have several options, some of which include rolling your own in-house solution, such as with VBA (for the record, I recommend against rolling your own solution). It’s a lot of work.

But maybe you’re thinking: “You know what? Locking and sending isn’t so bad, we have a sheet we use…”. Fair enough. What about comments on the data? This question of comments and commentary comes up again and again – for good reason. It’s incredibly useful in the finance world to provide context to a data point, particularly when that data point appears out of the norm somehow.

Comments are a tentpole feature in Dodeca, and probably one of the biggest features in the product that goes to show its philosophy of being a best of breed tool for planning (with a lowercase P!), reporting, spreadsheets, and the best OLAP engine on the planet. Dodeca has extensive support for allowing commentary on any given cell. Today I’m going to talk about one of the simpler use cases for comments. I’ll do this by extending my example from yesterday so that in addition to allowing the user to input budget values for a given market, the user can now provide comments as well.

Setting up Comments in a View

The first thing we need to do is edit our Excel template to add cells for the comments themselves. You can see this in the following screenshot where I have enhanced the data input view from the previous article:

Dodeca input template comment range

Dodeca input template comment range

Note that I have given the comments range a name, in this case Comments.Range.1. This will come into play in a moment when we configure the comments in the view. The next thing that I need to do is define key/value pairs for each comment. Essentially, the key/value pairs are where we use a particular cell to define a unique string of text that identifies a particular comment. As with so many other things in Dodeca, we define this in the cells/workbook itself. The simplest way to achieve this is with a formula that references cells containing members from the point of view (POV).

Excel formula showing the key/value associations for a comment

Excel formula showing the key/value associations for a comment

Check out the formula for the comment for the Sales item:

="Measure=" & B9 & ";" & "Market=" & C$5 & ";" & "Time=" & C$7 & ";" & "Product=" & C$6

This is just a normal Excel formula. The format that I want to achieve in this case is that I have a semi-colon delimited list of items that in the format Dimension=Member. So for the first cell, the resulting intersection is this:

Measure=Sales;Market=[T.Market];Time=Jan;Product=Cola

Because it’s just a normal Excel formula, when I fill down, the item for Measure will update based on the current row (after Sales will be COGS). Also note that in this case we just see the token [T.Market]. Remember that with Dodeca templates we often need to think a bit temporally, which is to say that we need to keep in mind that when the view is built by Dodeca, the token will be filled in with the user’s current selection for the Market dimension, and thus the formula and in turn the POV for the comment will be updated dynamically. Also note the absolute cell references in my formula. I want to make sure that when I fill down the correct cell references are maintained.

Before moving on, note just one more thing regarding the POV for our comments: we don’t need to match up with the Essbase dimensions. We typically will match up to some extent, but you don’t have to slavishly represent each dimension. For if there is, for example, a dimension that has no bearing on the comments, we don’t need to bother to represent it.

Since the comment key/value range is only meant for Dodeca to be able to determine what intersection the comments belong to, we don’t really want or need to show it to the user, so we simply hide that column on our sheet, giving us the following template:

The comment key/value associations are hidden so that users aren't bothered with it

The comment key/value associations are hidden so that users aren’t bothered with it

Now let’s go over to the view properties and tell Dodeca about the comment range in our view, so that it knows how to update and populate them. Under the options for our view, there is a Comments category with several options. In this simple case, we don’t really need to change any of them, except to go in to the CommentRanges item and define a specific comment range (Dodeca allows multiple comment ranges but for now we are just concerned with our one range).

Comments options in Essbase Excel view

Comments options in Essbase Excel view

Let’s take a look at the configuration needed for the comment range that we have been setting up in the template:

Main comment range configuration for Dodeca input template

Main comment range configuration for Dodeca input template

Dodeca offers an incredible number of variations on the user comment experience and we can control most of that experience. For the moment, only consider the options in bold that I have specifically changed in order to make comments work on this sheet:

  • AllowDeleteString: True. I have specifically told Dodeca that I want to allow users to blank out a comment cell if they so choose, thus erasing the comment
  • InCellDisplayPolicy: MostRecent. Dodeca can track the comments for a given data point over time. In this simple case, I just want to show the most current comment
  • EditPolicy: EditInCell. Dodeca has a more featured comment explorer feature that I will get into in the future. For now we just want to edit the comments themselves in the cell
  • ThreadPolicy: OneCommentOnly. Again, there is quite a bit more enhanced functionality available here but I want to keep it simple
  • Address: Comment.Range.1. This address matches the defined name I have for the comments on the sheet
  • KeyItemsString: =OFFSET(@ACell(), 0, 1). This is probably the “trickiest” element to this entire configuration. In a nutshell, for a given comment range, we need to tell Dodeca about the cells that will contain the comments, and the cells that contain the POV for each comment individually. The formula in this cell represents a combination of an Excel formula along with a special Dodeca function @ACell(). The @ACell function returns the address of the current cell. Using the Excel OFFSET function, we can pass an address and a relative offset. In this case we are saying to offset by zero rows, and offset the column by 1. So this returns the value of the neighbor cell. If for whatever reason our comment POV cells were further to the right (such as one more column over), then I would need to increase this value to match.

Lastly, let’s run the view and see what happens:

The Market Input template as built by the user, with our new comment range

The Market Input template as built by the user, with our new comment range

Now let’s enter some text in to explain the value for Colas:

Entering a comment to a cell

Entering a comment to a cell

Given my input policy, the comment is sent up to the database right away. I can close and open this sheet and the comment will be loaded and shown. I can even develop other views and if I plugin the proper comment POV, I can show the comments on a totally different view. The comments are stored in the Dodeca relational repository (not as LROs or otherwise directly in the cube), which gives us fast access to them (and also explains why we don’t need to map every dimension from the cube if we don’t want to).

I hope this brief introduction to the comments functionality in Dodeca was useful and educational. Invariably when people (such as at the Kscope booth) ask about data input, the next question is whether they can get comments too. And the answer is yes; in fact, Dodeca makes it downright easy.