Tuesday, September 4, 2012
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..
Saturday, September 1, 2012
Dodeca Customer Feedback - The Rosewood Company
I was talking yesterday with a new customer who had an interesting comment about Dodeca. In her responsibilities at her company, she is not only the Dodeca administrator, but also handles all of the Essbase administration along with about 5 other major tasks. They are now deploying Dodeca to replace an aging VBA/Essbase application that had been created by a consulting firm several years ago but nobody knew how to maintain it. Here is the quote (used with permission):
“In Dodeca, I built my dynamic schedules very quickly on my own and they worked! I was a little intimidated at the beginning of training, but now I have full confidence to build new reports on my own which is something I could not do in our old VBA-based system.”
- Lynn Mahurin, The Rosewood Company
One of the challenges that Lynn faced is that she has a report that needed to be generated for different groupings of business units and it included a number of reports where the rows included in different sections changed with any change to the Essbase outline. Further, on some of these reports, she needed to see the detail for some rows, then have smaller values grouped together dynamically to form an 'Other' category. These challenges were easily met with the unique Dodeca workbook scripting functionality which provides the ability for non-programmers to customize reports and their behavior.
We are seeing a number of companies who are planning ahead for their move away from the classic Excel add-in. These companies are evaluating Dodeca and find it to be not only as fast as the classic Excel add-in, but they are finding it faster, easier and less expensive to create, deploy, and maintain Essbase applications.
“In Dodeca, I built my dynamic schedules very quickly on my own and they worked! I was a little intimidated at the beginning of training, but now I have full confidence to build new reports on my own which is something I could not do in our old VBA-based system.”
- Lynn Mahurin, The Rosewood Company
One of the challenges that Lynn faced is that she has a report that needed to be generated for different groupings of business units and it included a number of reports where the rows included in different sections changed with any change to the Essbase outline. Further, on some of these reports, she needed to see the detail for some rows, then have smaller values grouped together dynamically to form an 'Other' category. These challenges were easily met with the unique Dodeca workbook scripting functionality which provides the ability for non-programmers to customize reports and their behavior.
We are seeing a number of companies who are planning ahead for their move away from the classic Excel add-in. These companies are evaluating Dodeca and find it to be not only as fast as the classic Excel add-in, but they are finding it faster, easier and less expensive to create, deploy, and maintain Essbase applications.
Thursday, August 23, 2012
Kscope12 Wrap-up - Better Late Than Never!
I can’t believe it has been a month-and-a-half since Kscope12 in San Antonio wrapped up, and I haven’t had a chance to blog about it. I finally have a chance to put together a quick review of my favorite, and not-so-favorite, things about the conference.
I arrived on Thursday as we had an ODTUG Board of Directors meeting all day Friday. The hotel was a bit quiet while awaiting the arrival of Kscope attendees. The pools and water park were, however, in full operation, which was not the case when I visited in January.
Conference activities kicked off on Saturday morning with the annual ODTUG Community Service project. There were a bunch of ODTUG volunteers that took the bus across San Antonio to provide our labor at the Boys and Girls Club of San Antonio. At the club, we tackled a number of projects including painting, assembling a basketball court, landscaping, and painting.. OK, I said painting twice. I know. There was *a lot* of painting! My wife, two sons, and one son’s girlfriend participated in the projects. Everyone was touched by the individually prepared tiles presented to us by the kids at the club. Here is my family that participated in the community service day with our tiles. My tile is now displayed on the ‘trophy case’ in my office.
One of my favorite events of the week is the Sunday EPM Symposium, which features presentations by Oracle product development about their future direction. It also features heavy interaction between Oracle and users. Unfortunately, I had a number of duties as an ODTUG Board member that took me away from the symposium. I did, however, get to see the keynote by Balaji Yelamanchili, who heads up EPM development. If you are planning to attend Kscope13, plan ahead to arrive on Saturday so you can attend the Sunday Symposium.
On Monday, I did a session with Rob Zwiebach from Oracle on how Essbase is being integrated into the Fusion Financials and Fusion Accounting Hub products. This is a very interesting product in that Essbase is integrated so deeply that posting to the general ledger fails if the corresponding Essbase update fails. I know a lot of companies who would love to have their general ledger automatically synced into Essbase and this product does that. Here is a screenshot that shows Essbase aggregations along with general ledger detail:
The Kscope12 keynote address was Monday evening and was given by fellow Oracle ACE Director, Cary Millsap, on the topic “Learning About Life Through Business and Software”. I really didn’t know what to expect from it and, although I had met Cary a couple of times, I really didn’t know him that well. I have to say, it was great. Not only was it great, but it was probably the best keynote I have ever heard and it was one of the highlights of the week for me. The keynote was recorded and I have included it here. I have requested that Cary’s slides be integrated with the current YouTube video. (Note: I thought the keynote was so good, I had my entire family watch it.)
Monday night was also the night of the Hyperion Midnight Madness event. This year, the Midnight Madness event featured a Hyperion-themed Hollywood Squares game; I was one of the squares (pretty fitting, right?). Unfortunately, I wish there had been more time for networking.
My favorite part of Tuesday was, of course, the customer session on Dodeca. We were lucky to have 4 customers talk about their Dodeca applications. Wells Fargo led it off and talked about how their nearly 8,000 Dodeca users use the management reporting, asset acquisition, and plan transfer applications. Kroger, which is heading to around 17,500 Dodeca/Essbase users by the end of this year, talked about the multitude of applications including their tactical operational reporting and analysis, store reporting, merchandise planning, and labor planning apps. Nestle has about 750 users (so far) live on a product planning application and Forestar, who developed the land around the conference hotel, is now live with about 35 users for a forecasting application. Before the conference, I asked each of these customers which features and benefits of Dodeca they were leveraging. Much to my surprise, each customer has very different sets of features that they leverage and different reasons for using Dodeca; I am glad they were able to share those reasons with the audience.
Wednesday is the day of the big ODTUG special event and this year’s event was quite special, a rodeo at the Knibbe Ranch. The Board of Directors rode into the rodeo on horses; it was the first time I had been on a horse in about 40 years. I did, however, buy a new Stetson so I could at least look the part.
Here is fellow Board Member and recent Oracle ACE Director, Cameron Lackpour, also on horseback.
Monday, June 11, 2012
That's a lot of bananas!
A couple of weeks ago, I was talking with a Dodeca customer that is in the grocery business and they were talking about their new Dodeca-based labor planning application. During the testing of their new system, one of their planners found an error in the amount of time that each grocery store allocates daily for placing bananas on the shelves. That doesn't seem like a big deal, right? Well, it turns out that it is a big deal.
The excess labor being allocated to this task totals about 30 minutes per day per store. This particular customer has around 2,500 stores and has estimated the cost savings from this particular labor planning correction to be around $5,000,000 per year. That's a lot of bananas!
This customer will be just one of four customers participating in a session with us at Kscope where they will be sharing more information about how they are using Dodeca and the cost savings they are realizing from the software. If you are coming to Kscope, be sure to join us in our session entitled "Solving the Performance Management Puzzle: Dodeca® Customers Reveal Their Answers" on Tuesday, June 26 at 9:45 am. If you are not signed up for Kscope12 yet, you can still join us by registering at www.kscope12.com.
The excess labor being allocated to this task totals about 30 minutes per day per store. This particular customer has around 2,500 stores and has estimated the cost savings from this particular labor planning correction to be around $5,000,000 per year. That's a lot of bananas!
This customer will be just one of four customers participating in a session with us at Kscope where they will be sharing more information about how they are using Dodeca and the cost savings they are realizing from the software. If you are coming to Kscope, be sure to join us in our session entitled "Solving the Performance Management Puzzle: Dodeca® Customers Reveal Their Answers" on Tuesday, June 26 at 9:45 am. If you are not signed up for Kscope12 yet, you can still join us by registering at www.kscope12.com.
Tuesday, June 5, 2012
Dodeca on iPad
Monday, May 21, 2012
Dodeca on Linux
OK.. Our labs have been busy.. Here is a screenshot of Dodeca running on a Linux desktop. Who is running a Linux desktop out there?
Friday, May 4, 2012
Dodeca on a Mac
Over the past couple of years, we have been asked about the possibility of having Dodeca running on a Mac. Well, here it is! This is a screenshot of Dodeca running on a Mac in our labs.
So, who is running a Mac but can't find how to get to Essbase data on it? <BigGrin>
![]() |
| Dodeca on a Mac (click to enlarge) |
So, who is running a Mac but can't find how to get to Essbase data on it? <BigGrin>
Subscribe to:
Posts (Atom)
Oracle AI World 26
I've been attending Oracle conferences for many years, and one of the things I enjoy most is the opportunity to reconnect with longtime ...
-
Note: I have posted a PDF version of this blog post on our website at http://www.appliedolap.com/downloads. Back in the days when I starte...
-
I had managed to go for the past 5 months, since I moved to Windows 7 64-bit on my laptop with Essbase 11.1.2, without the need to connect t...
-
In a recent blog post, I expressed my dedication to installing Essbase 11.1.1 and, I figured that while I was installing it, I should docume...

















