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.

Setting Up

We’re going to let the user choose their connection just like they would choose anything else – from a selector list. So the first thing we need to do is define a new selector. In my example I’m going to imagine that we have multiple monthly versions of the venerable Sample/Basic cube that the user will be allowed to choose from. Here’s my new selector:

Creating a new generic selector in the Selectors editor

It’s just a generic selector (I am going to hardcode the available values, and they don’t correspond to some actual member name – so it’s not a SQL or Essbase selector). And I’ll just call it SampleCube with a token name of [T.SampleCube]. This means that in any Dodeca configuration setting that can use tokens, I’ll be able to use this token name to have Dodeca drop in the value that the user selected.

Next I need a selector list to go along with the selector. This will just be a basic selector list that is defined manually (as opposed to sourcing dynamically from SQL or Essbase). I’m going to add a little twist and a nice improvement here though by making it a NameValuePairsDelimitedString. This allows me to configure a selector list for the user by defining its contents ahead of time. Additionally, compared to a normal selector list I’m going to define this such that the user will see one thing but the actual token value will be something else. For example, consider the help text for the DelimitedString setting in the below screenshot:

Creating a new Selector List for the data source selector

A pretty typical usage of this selector list would be to show the months of the year but the token value would actually be the month number. In the case I’m showing right now though, I’ve decided that I’d rather not show the possibly terse Dodeca connection ID to the user (such as Sample.Basic_JAN or something) but would rather hide that under the hood, so to speak, and show something a little more cleaned up such as Sample Basic - January. Of course, this method unshackles me completely from any arbitrary name, so I could just as easily put in Marketing Ops - January if that would be more intuitive for my users. It’s a small improvement but in the aggregate could result in saving a little bit of user training, confusion, and increasing user acceptance of the solution, so I think it’s a fantastic design choice here.

Now I’m going to define a very simple Dodeca view template with a single retrieve range (Ess.Retrieve.Range.1) that just does a top of the house retrieve from Sample/Basic.

Simple retrieve range from Sample/Basic on a Dodeca template

Now, when I designed the view in the first place I did it completely inside of Dodeca using the template designer. When I did this I actually just set the view’s connection to a specific connection for design purposes (and so I could ad hoc the design grid without having to manually login), so once I have the template how I want it, I now go back in to tokenize the connection ID, using the token name from my selector earlier:

Tokenizing the EssbaseConnectionID setting on our Dodeca view

Also as part of designing the view, I added the selector I defined earlier to the list of selectors for the view (this view has just one). There’s no screenshot for adding the selector, but I’ve blogged about it numerous times in the past, so check out a different Dodeca article on this blog if you need a refresher.

The last thing we need to do now is just test the view. Having tokenized the connection ID, and having added the selector, I now have this (pre-built) view:

The new selector that let’s us choose a value to be used for the connection

This looks perfect so far: the “nice” names are being shown. Now time to build the view (green arrow on toolbar):

The built Dodeca view using the connection that was chosen by the user

The view builds exactly as expected. Having chosen a value from the connection selector, it’s token value (in this case, a connection ID) is associated with the token [T.SampleCube], Dodeca uses the token value to replace the token in the connection ID setting for the view, then builds exactly as normal.

Dodeca Does It

I have to imagine that with other solutions/technologies, the way to implement this would have been less usable and more work, possibly resulting in duplicating lots of objects, adopting a non-ideal outline design, some weird one-off hack to allow connection selection, and more.

But for Dodeca this is a really great use case that shows off its flexible configuration model, ability to easily accommodate various data architectures, is pretty simple to implement, and perhaps most importantly: making it drop dead simple for the user.