Friday, September 5, 2008

Adventures with Essbase Smart Lists (a/k/a Typed Measures)

I have been looking at the changes in the Essbase Java API and more, specifically, started investigating the interesting new things called 'Smart Lists' in the Java API. The IEssGridView interface contains some new fields and methods around smart lists such as the CELL_CONTENT_TYPE_SMARTLIST constant, the getSmartListName() method that returns the name of a smart list for a given cell and getFormattedValue() which in the documentation states "If cell content type is CELL_CONTENT_TYPE_SMARTLIST returns the smartlist datatext"'. I thought I would take a closer look.

First I did a search for the term "smartlist" in the DBAG for 11.1.1 and strangely it turned up nothing. I tried a couple of other terms and was also stumped so I turned to EAS to see if one of the sample databases had smart lists implemented. I found that none of them do but did find a clue:

The outline properties tab shows a property called 'Typed Measures enabled'. Above is a screenshot from an outline I created to test 'Typed Measures'' which, obviously, must have the 'Typed Measures enabled' property set to true. I also noticed the next tab:

The 'Text List Manager' is where you create and manage the values returned for a Typed Measure. Here is the list I created for my simple test outline:

The next step was to assign this text list to a member. I added it to the 'Product' member of my outline:

I then went to Excel and input some data on the 'Product' member. I input 6, 8 and 10 which mapped to 'Moderate', 'High' and 'Exceptional' satisfaction as it was my understanding that Essbase stored the value and displayed the text upon retrieve.

By the way, did you note the subtle difference between the missing text string '#Missing' on the 'Support' data cells and the 'Missing' string from the Text Measure o the 'Product' data cells? In any case, I got an error when I did a lock and send:

Instead, I had to enter the appropriate values from the 'Text List' in order to send:

Now that I have a simple cube that supports Typed Measures to work with, I can go back to the Java API and see more detail on how it works under the covers. I may post a followup showing that later.


Unknown said...

My QE background is showing... did you notice that "Smartlist" is spelled wrong in the Essbase Warning (Smarlist)

Tim Tow said...

I did see that when I was originally doing the post (and then quickly forgot it before I got a chance to report it).


Daniel Poon said...

Exactly what I was looking for. This feature is cool because it's like attributes except more powerful. Attributes only work to say Cola 12oz and it's fixed. This feature allows values to be assigned and provide a textual context and doesn't have to be the same all the time. Well as the name suggest, it's a measure. I love it. Thanks so much Tim once again!!!

Keep blogging my friend!!!