The dependent selectors feature in Dodeca is one of my favorite “simple” features. I say “simple” because the implementation is very straightforward. I like it so much because the crux of this feature is about getting users to their data and reports faster, with the most streamlined UI possible. Not to get on my architectural high horse too much, but I believe that solutions – whether it’s a cube, a view, software, an ODI solutions, or whatnot – should be elegant, maintainable, and intuitive. Perhaps in another life I was a UI or UX designer. Advanced software such as Dodeca and its more niche cousin Drillbridge strive to make users and administrators alike as productive as possible.

To that end, dependent selectors are a way to provide an enhanced report/view build experience. In a typical Dodeca report, you typically select values from one or more selectors: choose a time period, choose a year, choose a location, build the report. You might choose multiple selections such as multiple time periods – it all just depends on how the selector list has been configured. A dependent selector is one whose value changes depending on one or more of the other selectors.

As a straightforward example, consider a view where the user must select a state and a city to build a report. The available states to select are, say, the 50 states in the US. And the available cities may span all of these states. But let’s say in this example that when we choose a state, we want to choose from a list of cities in that state using another selector. By doing this we get people to their data faster and improve the user experiencce.

This can be accomplished easily with a dependent selector. Let me show you how, starting with some simple tables modeling the states and the cities.

Here we have the table STATES for states and STATE_CITIES for cities. Also note that even though this is a bit of a “quick and dirty” example, I have nevertheless given the STATES table a primary key of STATE_ID (the two letter postal state code), and a STATE_NAME column with the “nice name” of the state. This will come into play more a little bit later when I talk about selectors and the difference between an ID and a value. Next, over in the STATE_CITIES table, we have a compound primary key of the combination of the STATE_ID (a foreign key into our STATES table), and the city name. These data constraints ensure that every state/city combination is unique, and that every city is associated with a particular state that exists in our states table.

The tables we have designed so far will be used as the basis of a Dodeca selector. Dodeca is very flexible with respect to how selectors are populated, with two of the primary methods of populating them being from Essbase dimensions or generated with SQL. In this case we’ll use the SQL Passthrough DataSet editor to create a query that pulls back the list of states from this table:

We can quickly and easily test the selector by using the Test Data Set button in the interface, showing that the query does indeed work (note that I only populated the table with a few states):

Now things get a little more interesting where we go to define the query for our city selector. We write a normal query, but in place of a value for the WHERE STATE_ID clause, we put in our states token (written as [T.States] in this case. Come runtime, the value of [T.States] will be replaced with value from our state selector, based on what the user has highlighted.

One of the newer features in Dodeca is the ability to save and edit test tokens so we can test the query out immediately instead of having to build a view first. Pressing the Edit TestTokens button brings up the following editor:

Let’s plugin a value of WA for [T.States] so we can test out which cities come back when the state is Washington. Note that my tokens value corresponds to the ID of the state in this case. I’ve configured the selector to use an ID value in this case, but I could have used the state name if I wanted to. Wherever possible I prefer to use the “core” or identifying value of a piece of data. Let’s test out the query to verify that the query works and the correct data comes back:

For good measure let’s swing by the Selectors editor and ensure that we have created selectors for [T.States] and [T.State_Cities]:

Now let’s look at the Selector List that corresponds to our State from the Selectors configuration. In particular note that the type of the selector list is a SQLPassthroughDataSet where we have chosen the associated data set ID:

Now let’s go over to the city selector list, where things get a little more interesting. Here we can choose the DependentOnSelectorIDs value and tell this selector which other selectors will influence it. We simply choose States:

With the selectors all configured, we can now include them in our view by including them as we normally would. For those of you not familiar with Dodeca, a view is like a report template, and when we administrators design the view for users, we can control an incredibly large amount of its behavior very easily. Each view can have its selectors configured, which will influence the options that a user gets to select from in terms of configuring and building the view. So by going to the Selectors configuration for a particular view/report, we get to choose which selectors should be associated with the view. In this case, it’s the State and State_Cities selectors. We could easily have more (and usually would, such as time periods, years, and other things).

With that all set, let’s commit our changes and go take a look at the view. Note how I have selected Washington from the State selector in the top right of the window. In real-time when I select Washington, the City selector refreshes instantaneously, showing just those cities from Washington:

Since I have Auto Build turned on for this report, once I have selected a city the report refreshes using my selection:

The report shown is just an incredibly minimal report for testing purposes that simply uses the city as part of a simple retrieve, but an arbitrarily complex report could also have been designed and used. 

Selector ID vs. Display Value

I mentioned earlier that while we were showing the full name of the states, we were actually using the state code under the hood, so to speak. This is somewhat analogous to member names and aliases in Essbase, where we have a core name (one that is often a little less “friendly” to the user) and a display name that is more friendly. As a configurable option, Dodeca let’s you use a value for display and an internal value for the ID – if you want. In this case it came in handy for the SQL because the query I wanted to write to fetch the cities was simplest if it got to use the state ID rather than the name. Without this feature I might have had to just show terse IDs to the user or rewrite the SQL query to join against another table so I could dereference the full state name. I think the design win here is that we got to show the “nice” name to the user but not have to make any sacrifices in the design (in terms of enduring additional complexity).