Essbase Date/Text Measures In Detail

I have been working on the Dodeca data audit log to make sure changes to date and text measures are properly logged and, as a result, got some interesting insights into how date and text measures work at the API level.  I thought I would share my thoughts with you.  In my work, I created a very simple example for tracking satisfaction of marketing programs including the start date and end date of the promotion.  This may or may not be a realistic usage of date and text measures, but I am interested to look at how it works under the covers, so it is good enough for my purposes.

For my test environment, I made a copy of the Sample Basic database and called it SampleEx Basic.  In this Essbase outline, I made a couple of changes.  First, I added two date measures, Start Date and End Date, and a text measure, Satisfaction.   I also added a dimension called Marketing Campaign.

In the Member Properties dialog for the members Start Date and End Date, I changed the Type property to Date.   When I pressed the OK button, the Consolidation property was set to Never automatically.

The date format used by Essbase is determined by the Date Format String property of the outline.  Here is where I set the date format that will be recognized by Essbase.  This is important not only for display of dates, but also for the input of dates as well.  I will show this in an example a bit later in this post.

Next, I added a Text List named Satisfaction Level defined with High, Moderate and Low values represented by the numbers 1, 2 and 3.  These numbers are the actual values stored in Essbase.

I selected the member Satisfaction and opened the Member Properties dialog, then changed the Type property to Text and selected the text list Satisfaction Level.   As with the date measures, Consolidation property was automatically set to Never by EAS.

I then created a view in Dodeca based on an Excel spreadsheet.  This view is really a template targeted for input these date and text measures.

As I mentioned earlier, Essbase expects dates to be input in the format specified for the outline.  It isn’t good enough, however, to use an Excel date format on the cell.  So, what happens if you us Excel dates?  Let’s take a look.  Here is the spreadsheet with an Excel date input.

After the data is sent to Essbase, then re-retrieved, the date appears to have changed.

So, what happened?  To find out, I stepped the Essbase Java API code that is running inside the Dodeca server.  Once I stepped into the server, I can see the value passed to Essbase is 41142 which is the numeric date format used by Excel.  The Excel date format represents the number of days since January 1, 1900.  I couldn’t find official boundaries for date measures in the Essbase DBAG, however, I did see references to date attributes are valid for dates between 1/1/1970 and 1/1/2038.  Apparently, the way that Excel format dates differs from the way Essbase server formats dates.  I experimented a bit more to determine how it works.

Next, I re-retrieved the data from Essbase so I could check the API level detail.  I found that the IEssDataCell.getDoubleValue() call for this cell returns 41142.0.  In other words, Essbase properly returned the same number that was sent to that cell.  The Essbase API also has an option to return a formatted string; the formatted string for this cell, returned via IEssDataCell.getValue(), returns “01-01-70”.  That is the value that I am seeing in the spreadsheet.  Hmmm...

What the...?  What is going on?  Well, here is the answer: Essbase expects the date to be submitted to the server in string format.  After all, it is a “date and text” measure, right?  So, let’s give that a try by putting the date in the string format expected by Essbase and, as with Excel, I will prepend the date with a single quote to force the value to be a string.

Once again, I sent the data to Essbase, then re-retrieved the data into the spreadsheet.  This time, I got different results.  The formatted string returned by IEssDataCell.getValue() returns “08-21-12” and the numeric value returned by IEssDataCell.getDoubleValue() returns 1,345,507,200.  What the heck is that number?  Believe it or not, it is the number of seconds since 1/1/1970.  Let’s calculate it in Excel:

Now we also know why the first attempt to send data to our date measure returned the date 1/1/1970.   The number that was actually sent to the database, 41,142, is less than the number of seconds in one day.  As the date measure is the number of seconds since midnight on 1/1/1970 and the number sent to Essbase doesn’t represent even one entire day’s worth of seconds, it was correct that it returned 1/1/1970.

I also tested input of date measures in Dodeca using the numeric values representing a date.  For this test, I entered 1,345,999,999 and sent it to Essbase.  When I subsequently retrieved the data value, it properly returned 1,345,999,999 as the numeric value and 08-26-12 as the formatted date.  In other words, sending the number of seconds to Essbase appears to work properly.

One final thought on date measures.  As we saw earlier in this post, when you setup an Essbase outline for date measures, you must choose a date format string.  We also saw that Essbase stores date measures with a data granularity to the second.  My question is this: Why are the allowed date format strings only granular to the day?

With that, we have learned a bunch about how Essbase handles date measures, so let’s turn our attention to a (much shorter) discussion of text measures.  In my example, we have the member ‘Satisfaction’ as a text measure and valid values are 1 – High, 2 – Moderate, and 3 – Low.  To enter the data into these cells, simply type the text value and sent it:

I did find, however, that I was unable to update the cell by entering the numeric values (even in Smart View or the classic add-in).  I thought this was a bit strange and may take a look at it in more detail in the future.

On the topic of text measures, I have heard discussion by a lot of people who believe that text measures may be a way for end users to enter free-form text into Essbase intersections.  That is not the purpose of text measures and neither can you enter free-form text.  Instead, the values that can be entered must be defined by an Essbase administrator in the Text List Manager for the outline.  They are much more akin to Smart Lists in Hyperion Planning.  In fact, under the covers in the Essbase API, they are known as Smart Lists.  Free form text is available in Essbase in the form of Linked Reporting Objects, or LROs.  LROs are problematic, however, as they are stored in the Essbase database index and must be subjected to special backup/restoration routines when you rebuild your cubes.  LROs are also not available in ASO databases.  If you need this capability, I recommend Dodeca, which features the ability to store textual information related to an Essbase data cell in the Dodeca relational server.

So, now you know more than you thought you may have ever wanted to know about date and text measures.  Perhaps your sole remaining question relates to my example.  You probably want to ask me, “Tim, why does the start date of your marketing campaign for the July 4th holiday start on August 21?”  OK, you caught me.  I used that date because I am writing this in August and August 21st happens to be my Dad’s birthday.  So, now you know..