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.

Let’s jump in and configure the view. First of, let’s create a tab that will have a retrieve from Sample/Basic with a single range on it:

Defining retrieve range for data from Sample/Basic

Next, we’ll create another tab that will be based on Demo/Basic and will have a retrieve range based on that database:

Defining retrieve range for data from Demo/Basic

Notice that the Sample/Basic retrieve range contains a selector token [T.Product] and that Demo/Basic contains a selector token [T.Scenario]. The selector lists for these tokens will be generated from their proper respective connections.

Next, we need to define a couple of special defined names on the sheet so that Dodeca knows which range should be associated with which Essbase connection:

Defining retrieve connection settings

The special names are Ess.Retrieve.Connection.1 and Ess.Retrieve.Connection.2. These are just defined names with a specific value. The name we give them corresponds exactly to an Essbase connection ID defined in Dodeca. The defined retrieve ranges will attempt to use the connection defined in the corresponding retrieve connection definition (e.g., Ess.Retrieve.Range.1 will use Ess.Retrieve.Connection.1).

For this view, rather than defining a specific Essbase connection to use, I’m going to leave that blank (note that EssbaseConnectionID is empty in the following screenshot):

Empty EssbaseConnectionID value

In terms of the selector configuration for the view, I simply have two selectors:

This view has two selectors

But let’s take a look at the details for the selector configuration:

Selector configuration

Often the Connection Policy is just UseViewConnection (meaning use the connection defined on the view) but in this case, because we want the selector contents to be dynamic based on a specific connection, we will set this to UseSpecifiedConnection, and then click on Edit Settings… in the Connection Settings configuration to configure the connections:

Connection setting for selector

And for completeness, here’s the configuration for Demo/Basic:

Connection setting for selector

Before we take the plunge and build the view, let’s do a really quick recap: we have an Essbase view with two tabs and two selectors. One tab will be generated based on Sample/Basic, the other from Demo/Basic. Additionally, each tab makes use of a selector whose contents will be dynamically generated based on a particular connection. Usually the connection to use is implicit based on the view configuration, but in this case we need to configure them explicitly.

With all of that configuration out of the way, let’s go ahead and build this thing:

The built view, on the tab with data from Sample/Basic

This looks good: You can see that I have my Product selector with products that are quite obviously populated out of Sample/Basic, my selection (Colas) was plugged in to the view template, and a retrieve was performed. Now let’s go check out the other tab:

The built view, on the tab with data from Demo/Basic

This also looks good – although in this screenshot I still have the selector from Sample/Basic showing. But you can see that my selected Scenario (Actual) was plugged in and used to retrieve against Demo/Basic. Note the products in column A that are decidedly not of the beverage variety.

That’s a pretty simple and quick overview of using multiple Essbase connections (and multiple selectors with different sources!) in a Dodeca view, but you can start to envision some of the potential applications of this. I have seen some extreme views in Dodeca that have literally hundreds of retrieve ranges and more than a dozen different connections, and the performance is very good. In some cases, being able to build this view in Dodeca (as opposed to manually retrieving and building a report) has resulting in a time savings of days each period, because the Dodeca view builds quickly and automatically.