Monday, May 19, 2008

Essbase 11.1.1 New Features - Date and Text Measures

Comments on my last post left by fellow Oracle ACE Glenn Schwartzberg has prompted me to expand on my comments about the Date and Text Measures features coming up in Essbase 11.1.1. This information if from a Collaborate 08 session by Aneel Shenker regarding future features of Essbase. This presentation, as any presentation you see from Oracle on unshipped product, has the following disclaimer:

"The following is intended to outline our general product direction. It is intended for information purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions. The development, release, and timing of any features or functionality described for Oracle’s products remains at the sole discretion of Oracle."

As I have said before, the fact that you hear it from me certainly doesn't change the above disclaimer as I can't/don't speak for Oracle (sorry I had to start this post with the same disclaimer again, but it is just something I think I have to do to prevent angering the Oracle legal gods).

This new feature coming in Essbase will enable you to do new things that you really couldn't do before in Essbase. Under the covers, the data will still be stored numerically but numbers, or ranges of numbers, appear to be mapped to text or date labels on the client side. Here is a screenshot from the presentation:

The 'Satisfaction' and 'Transaction date' columns in this screenshot both come from Essbase but have associated labels. This functionality will also work on summaries and averages:

I haven't seen how they plan to provide this functionality 'under-the-covers' at the API level; to warn you, it gets a little geeky past this point. In the current version of the Essbase Java API, the data is provided in a multiple 2-dimensional arrays. The member names and data is in one array of strings, and returned as an int, the cell types are returned in an array of bytes and the attributes, which hold the information such as dimension numbers, read/write status and LRO information that make Excel add-in 'Styles' work (and is used in our datapoint mapping work in our Dodeca product) are returned in an array of int types.

I doubt they add another layer for for date/text measures to work as it seems like a whole bunch of overhead; the date/text labels could possibly be repeated many, many times in the array. I guess it would be easier to work with from the programming side if it is in a array. The other option they may implement would be to provide a mapping of the values to the strings at retrieve time. If this were the case, the network traffic would be reduced at the cost of programmers ease of use. I guess I will find out soon enough; when I get a build of 11.1.1 to play with, I will need to look at how they implemented this new feature to make sure we support it properly in our Dodeca product.


otaf said...

Thanks, i did not see that averages/sums could be calculated.
(not so clear in the pdf from the presentation).
That is one of the interesting part s of it, though i cannot say how to make a real "average" (the group A - rather satisfied label).

There is a kind of similar feature in planning, though afaik it maps a value with a label (Smart list)

excerpt form planning doc :

A Smart List is displayed in a Planning data form cell as an alphanumeric text description, but stored in the database as a number. For example, an integer Smart List for a reporting cycle may have values 1-5, for Yearly (1), Quarterly (2), Monthly (3), Daily (4), and Hourly (5). The values that are displayed on the data form are Yearly, Quarterly, Monthly, Daily, and Hourly.

Thx again for the info, Tim !

Anonymous said...

Hi Tim,

Do you know where and how these comments are stored? i would like to get the comments out with FDqM instead of the figures (or match them through SQL e.g.).


Tim Tow said...

If you are talking about the text for the text meausure, they are stored in the outline.