The Humble ComboBox

Introduction

A lousy combobox?  Really?  You have got to be kidding me. 

I can’t think of a simpler dimension picker than a combobox.  Even its definition is pretty short.

So why would I write a post on this most humble of dimension selectors?

The combobox’s simplicity lets me focus on how that control gets filled, not the many cool dimension picker features that Dodeca provides.  I’ll get to that in the next post (a three post series on member selection, zoiks).

How do I populate a combobox?

Dodeca splits dimension pickers into two parts:  Selectors and Selector Lists.

Selector

To be able to select or modify a dimension’s member selection, it must first have a Selector.  A Selector defines the dimension, data source, caption, tool tip, and other settings.  We’ll come back to some of these settings, but for the time being, know that a Selector List without a Selector is a bit pointless as it has nowhere to go.

Selector Lists

Selector Lists are used to populate a Selector.   For me, that’s a pretty short sentence, but it is pregnant with potential.

Four ways to get to the same place

To get to a Selector List, go into Dodeca’s Admin menu, and pick “Selector Lists”.  (Yes, there is a metadata library, yes, it is stored relationally and served up through a really cool web services architecture, yes, there is a whole potential blog post on the architecture, no, I’m not going there right now – let’s see if I can populate a combobox first.)

Delimited member list

AppliedOLAP provides a slew of different Selector Lists, but I’m going to create my own to walk through this process.

Selector List ID

After clicking on New, I will fill in the Selector List ID – that’s the name of the Selector List.  In this case I’m going to call it “Blog_Scenario_Combo” because I am an inventive sort.

Selector

Remember how I wrote that Selector Lists have to be tied to a Selector?  I’m going to associate my new Selector List with a Selector called “5_Scenario”.  It’s the Scenario dimension from My Very Favorite Essbase Database In The Whole Wide World, Sample.Basic, aka MVFEDITWWWSB, also aka meph-edit-wysb, for those of you prone to say these things out loud.

NB – This Selector’s association with Essbase is important, as we’ll see in a little bit when we tie SQL to the selector.

Select List Object Type

There are four ways to populate an Essbase Selector:  a delimited text list, an Essbase API member query, an Essbase report script, and an Essbase SQL pass through data set.  It’s sort of a easiest to hardest progression, or maybe a nice to OMG-that’s-cool.  You choose.

The easiest (and least flexible) one is the delimited list – it’s just what it sounds like, a delimited list of member names.

Selector Control Type

Remember how I wrote this was going to be a combobox?  I must choose EssbaseSelectorComboBox to make that choice.

Description

This is optional – I’m just putting in a description for the sake of completeness/my sanity.

SelectorListObjectTypeID

Click on the magic OK button and the property sheet for the newly created Blog_Scenario_Combo Selector List appears. 

Click on the SelectorListObjectTypeID dropdown control, and select EssbaseDelimitedString.

Two spelling corrections

DelimitedString

Then click on the DelimtedString text box and type in “Actual;Budget”.  Note that a semicolon is the delimiter. .

If you had a big list, you could expand the text box by clicking on the ellipsis button:

There are other options (lots of them) you can set, too many of them to go into here.  This is what your property sheet should look like when you’re done:

I did not set a NullSelectionText to handle never making a selection (did I mention the awesome awesomeness of this product – good grief, they think of everything) – without something like “Please select a Scenario”, the initial combobox will look “narrow”.  You’ll see what I mean in a few screen shots.

Dodeca is smart

While your Selector List is being modified, Dodeca will tell you that it’s under modification by sticking a grid and a plus logo to the left of the name.

Commit it

Click on Commit to save the change, then confirm Yes.

Modify the View

The View (or report) must be modified to use this new selector list.  In the Dodeca Admin client, pick Admin->Views and then select the View called “Blog Income Statement”.

Click on the ellipsis for SelectorConfiguration.

Configure Selectors

The Selector List for Scenario needs to change. 

Click on the dropdown and pick the new Blog_Scenario_Combo list.

Click on OK, then Commit, then Yes.  You have now made your first Selector List change.  Off to the View to see what happened.

Viewing the View

Not much to look at initially.

Click on the arrow next to Scenario in the toolbar.

And there it is in all of its abbreviated glory.

Okay, but so what?

Well, you have to crawl before walking, walk before running, etc., yes?

Remember, your Scenario dimension could have 20 members in it – how would you limit that list in another tool?  Through METAREAD security?  A bit tricky that, as different reports would likely have different scope requirements.  Through hard coded lists – yes, you could do that, but you’d either be sticking it on a sheet and referencing the list or directly entering it into the control.  The former would be mildly painful, the latter a maintenance disaster.  And oh by the way, would either one be validated against Essbase?  I didn’t think so.  Told you that Dodeca was smart.

Two Essbase-centric approaches

Well, that’s great if you want to limit the list to two hard-coded members, but what if you want to dynamically retrieve all of the level zero members?  We all know what the Scenario dimension looks like:

How can we get Dodeca to dynamically query the outline to give us all of the level zero members?

EssbaseMemberQuery

One approach is to use the Essbase member query approach.  This is the simplest (code wise) and maybe a little unfamiliar to those who aren’t used to the Outline API.

Let’s go back to the View editor and change the SelectorListObjectTypeID to EssbaseMemberQuery:

Then navigate to the MemberQuery property and enter <DESCENDANTSOF “Scenario”

Commit it, run the View again and now:

There’s all of the descendants of Scenario, dynamically retrieved from Essbase as required.  Nice.

How would you do that in Excel for Smart View or the Classic add-in?   I believe the phrases “painful”, “ow, that hurts my brain”, “whattya mean I can’t use the API in SmartView, huh?”, “boss, can we please buy Dodeca” are all involved.

ReportSpec

Essbase API too esoteric for you?  How about a good old fashioned Essbase report script?

A report script to return the same four members looks like:

If this report is run in EAS it outputs the following:

Don’t worry about the data, Dodeca is smarter than the average front end, and will just pick up the member names for the selector lists.  .Remember, this is true just for selector lists – when it comes to a proper report, Dodeca is oh so capable of returning numbers to go along with metadata.

Change the SelectorListObjectTypeID to EssbaseReportScript and then click on the text box on the new ReportSpec property.

Click on the ellipsis button to expand the ReportSpec property and paste/type in the report script.

Click on OK to assign the script, then Commit, and Yes and rerun the view.

Again, a fully dynamic dimension list from sourced from the Essbase outline.  Dodeca gives you two ways to do this – awesome, isn’t it?

Let’s do this in SQL

Dodeca can drive member selection through SQL queries.  Why would you do that?  Well, how about a mix of limited member selections by View, central administration, and (this is really nice) validation against Essbase so you can’t select the Scenario “Rutabaga” even though it’s in the table.  I apologize to those who love this terrific tuber.

How about another, chuckle, scenario, like driving dimensionality from SQL (load rules, Studio, or EIS) and then having your limited lists (based on whatever SQL voodoo you can come up with) driven from the very same tables?  Neat-o, keen-o.  Take it as far as imagination and your SQL skills can go.

SQL Passthrough DataSets

Okay, the above is a pie-in-the-sky moment, let’s return to earth and define the SQL – don’t worry, I can barely spell S-Q-L, so there’s nothing to be afraid of.

Back to our Favorite Dodeca Menu In The Whole World, At Least For Right Now (FDMITWWALFRN – feh-dim-it-wall-forn – I like the sound of that one), Admin, and select SQL Passthrough DataSets.

Create a new SQL Passthrough DataSet and call it Blog_SQL_Scenario.

Click on the Query[] Array ellipsis button.

Three things need to get set in the Query Editor property box:

·       ConnectString – This looks scarier than it really is and defines the server, database type,

·       DataTableName – What table/view will this query read from.

·       SQL – The actual SQL statement.

ConnectString

This is pretty easy to read – I stole mine from Dodeca’s hibernate.properties file created during the installation of Dodeca and just changed the server and database name.  Yes, I will not win any awards for security but the server is a development virtual machine.

NB – For those of you currently experiencing cardiac arrest at the thought of clear texting this information, know that Dodeca triple DESes (I just created that verb) the ConnectString back to the metadata store.  I am naked on the Internet, but no else is, at least as far as ConnectString is concerned.

To give you a feel of how flexible Dodeca is (it’s really JDBC, but still), by scanning hibernate.properties, I see that Dodeca supports the following SQL databases:

·       HypersonicSQL

·       H2

·       MySQl

·       Oracle (of course)

·       PostgreSQL

·       DB2

·       TimesTen

·       DB2/400

·       Sybase

·       Mckoi

·       SAP

·       SQL Server

·       Interbase

·       Pointbase

·       Ingres

·       Mimer

·       InterSystems

Your company’s whacko SQL database backend isn’t going to limit your Dodeca implementation.  So what are you waiting for?

Cameron_Blog

This is perhaps the world’s simplest database – one table.

And one column in that single table. 

Big deal you say?  Dodeca doesn’t care how complex your query is – and if it’s a pig to retrieve, remember that the results gets cached, so only the first retrieve is expensive.

Back to the Blog_Scenario_Combo Selector

Just two properties to change after EssbaseSQLPassthroughDataSet is selected in the SelectorListObjectTypeID:  MemberColumnName and the SQLPassThroughDataSetID we just set up.

Commit the changes, click on Yes to confirm, and let’s look at the View.

No code required.  Awesome.  Think about the code you’d have to write in an Excel add-in to get you to SQL.  Urg, I’ve done it before.  No thank you.

What’s in a name?

Rutabaga is a versatile vegetable, but I think I like it because its name just sounds silly to me.  And with that deep thought, I’m going to add the Scenario Rutabaga to the Cameron_Blog.Scenario table.

What oh what will Dodeca do?

Ignore Rutabaga because it isn’t a member name in Sample.Basic.

FWIW, there is just a plain old SQLPassthroughDataSet that doesn’t validate member names against Essbase. 

And there it is.

Why would you ever want to do that?  Well, against Essbase you likely wouldn’t, but remember, Dodeca goes against SQL just as well as it goes against Essbase.  Oh yes, yet another upcoming blog post.

So where have we been?

Comboboxes aren’t fancy, but by examining the way they get populated, a pretty good idea of Dodeca’s dimension power can be grasped.  And remember, comboboxes aren’t even the cool dimensional control.

But the four different paths to populating a combobox – delimited strings, Essbase API member queries, Essbase report scripts, and SQL selectors that validate against Essbase – is there for all to see and the techniques apply to dimension listboxes and treeviews.

The next post will be a quick run through of how to populate those listboxes and treeviews and then a more detailed view of some of the more interesting things you can do with a dimension selector like driving selector possibilities based on other selector choices, all without writing a line of code.

Hacking Essbase, indeed.