Announcing Kscope13 Platinum Sponsor – Applied OLAP and Dodeca!

We are happy to announce that Applied OLAP and our flagship product, The Dodeca Spreadsheet Management System, have signed up as a Platinum Sponsor for the Kscope13 conference in New Orleans.  We believe that the conference, which runs from June 25 to June 28, is not only the best forum for Hyperion related content, but it is also the best place for Hyperion related vendors to show their products.

There are several events at Kscope13 that you should not miss.  The first event is the annual ODTUG Community Service Day on Saturday, June 24.  This year, ODTUG volunteers will be working to improve a school with a new coat of paint, landscaping and playground improvements.  The community service project is also a good time to meet others who share your interests.  I have made a number of new friends at these projects over the years.

The next can’t miss event is the Sunday Symposium.  The Symposiums are one of the only times during the year that you can get direct contact with the Oracle development team.   One of the cool things about the symposium is that Oracle not only talks about the new things they are working on over the next year, but they also spend a lot of time listening to the wishes of customers.

Last week, ODTUG announced the identity of the keynote speaker and, frankly, there have been many conferences where the keynote just didn’t interest me.  This conference will be different.  The story of the Kscope13 keynote speaker, Doc Hendley, is amazing and inspirational.  Doc is the founder of Wine To Water, an organization who is working to bring sustainable, clean fresh water to parts of the world that desperately need water.  I watched his TEDx Asheville presentation on YouTube and all I can say is this..  WOW!.  I can’t wait to meet Doc in New Orleans.

Finally, last time Kscope was in New Orleans, there was a Wednesday night Hyperion social event that wound its way down Bourbon Street.  There were about 22 of us that made that journey; this year I expect that number to be in the hundreds (and that is *after* the traditional Wednesday night Kscope “special event”, the just announced party at Blaine Kern’s Mardi Gras World.

As a vendor, I hope you get to spend time in the vendor hall and visit all of the vendors to learn about their products and services.  As a self-funded user group, ODTUG relies on these vendors help provide the funding to put on a great conference plus you may find some tools to help you in your job.  If your company uses spreadsheets, I would be especially happy to tell you about the benefits our customers realize using The Dodeca Spreadsheet Management System.
 
As a Platinum Sponsor, we can also help you save money on your Kscope13 registration.  Use the special code AOLAP when you register for Kscope13 and save $100 on your registration.  You can learn more about Kscope13 and register for the conference at www.kscope13.com.

See you there!

The Dodeca Spreadsheet Management System, Version 6.3, Has Been Released!

We have been very busy at work on a new version of Dodeca and were working so hard, in fact, that we missed our marketing opportunity of the century.  Of course, I am talking about December 12th or “12/12/12” which is also known as, of course, “Dodeca Day”.  Oh well, we have 99 years, 11 months to plan for the next one!

Not all is lost, however, because after months of hard work, we have released the Dodeca Spreadsheet Management System, version 6.3.0.3696.  The new version of Dodeca is very exciting as we have worked very hard to make it easier and faster to create and publish dynamic spreadsheet content.  This new capability is provided by the new Dodeca View Wizard and the new View Designer.  First, let’s take a look at the View Wizard.

The View Wizard is used to create or modify a Dodeca View which is the Dodeca version of a report or input form.  In all previous versions of Dodeca, the system administrators used a three step process to create and deploy a view:

1. Create a report template in Excel and import it into Dodeca using the Binary Artifacts Metadata Editor form.
2. Create a View object to link together the Excel template with Essbase and/or SQL Connection objects, and set Essbase properties and other settings.  These settings were set in the View Metadata Editor form.
3. Deploy the new View on a View Hierarchy object in the View Hierarchy Metadata Editor form.

When we originally had the vision for the View Wizard, our intent was that it would help developers who wanted to learn Dodeca as they wouldn’t have to learn these three separate forms just to publish a simple report.  We also thought that, once someone learned the ins and outs of the product, they would return to using the metadata editor forms.  What we have found, however, is that the View Wizard makes it so much easier that, even for me, *I* haven’t created a new view using the old ‘3-step process’ since I first had the View Wizard available to me in alpha form.

The View Wizard takes a step-by-step approach to creating the new view.  The first step is to choose the type of view that you would like to create.  Dodeca supports creating static views from Excel files or dynamic, data-driven views from Essbase and/or SQL data sources.

Choosing a specific wizard determines which properties are available on the view object.   Once you choose the type of view, the wizard then walks you through the steps.  The first step lets you assign a name to the new View.

Next, you specify the Excel file you would like to use or specify if you would like to create a view using a new Excel file.

In my example, I have selected a file from my desktop to bring into, and publish with, Dodeca.

The next step is to choose the member selectors you would like to filter the view.

The member selectors are highly configurable in Dodeca, but for this example I will let Dodeca use the default selectors, which use the Essbase hierarchy tree from which users can make selections.
Next, I will choose user interface settings including the toolbars and whether or not Dodeca displays the Excel formula bar.

In my case, I let Dodeca choose the default toolbars for this view type, and then I selected the checkbox that tells Dodeca to display the formula bar.
Next, I will skip the AutoBuild on Open step and jump to the Essbase Connection Settings.  When I initialized my Dodeca environment, I ran the Dodeca Essbase Connection wizard and chose the Essbase databases I wanted to include in my application.  In this step, I will choose which Essbase connection to use for this specific view.

In the next step, I will specify that Dodeca retrieve data into a specified range and will specify the format for #Missing and #NoAccess labels.   Dodeca has the ability to use a specified range name where the data will be retrieved.  This capability uses normal Excel range names which can be created dynamically within the spreadsheet.

My next step is to specify that this view be associated with a workbook script.  Workbook script is the typical way that customers customize and tailor view creation or behavior based on their unique requirements.   It works using a paradigm of events and related actions, or methods, which dynamically change either the spreadsheet or the Dodeca environment.  In my case, I don’t plan to do anything with the workbook script, yet, but I decided to add one just in case I want it later.

Finally, I am just going to press the Finish button in the navigation panel on the left and all of the necessary objects will be created behind the scenes and stored in the Dodeca server.   At this point, I have several options.

I need to make a couple of modifications to my Excel template.  Remember that I had picked three member selectors in an earlier step in the View Wizard, so I need to tell Dodeca where on the spreadsheet to place the member values selected by the user.   I also had specified that the data will be retrieved based on a named range, so I need to create that named range.   In all previous releases of Dodeca, this work would be done in Excel but that is no longer the case.  The new View Designer allows you to edit the Excel template directly within Dodeca.  First I will place the tokens that indicate where the selector values will be placed.

Next, I will specify the range where I want to limit the Essbase retrieve.

Finally, I saved the template back to the Dodeca server and tested the output by opening the view in preview mode directly from the wizard.

Since I liked how the view looked in preview mode, I decided to go ahead and deploy the view by placing the view in a view hierarchy and, using drag and drop, I dropped the new view on the hierarchy.

Once I placed the view in the hierarchy and committed it to the server, the view was now deployed and available to users on my server.

So, now you have seen an introduction to some very useful things in the new version of Dodeca and I hope you have the chance to use them soon. Soon, I will do a deeper dive post on the View Designer and explore the savings in both time and effort our customers are seeing when using this new component.

If you are not currently a Dodeca customer and would like to try it out, go to our Contact Us page on the Applied OLAP website and ask about an evaluation copy of Dodeca.

Oracle Open World Essbase Roadmap Session

For me, one of the highlights of Oracle Open World is the ability to attend a number of roadmap sessions where Oracle tells you what they plan to work on over the next year.  Of course, these sessions are always prefaced with the standard Safe Harbor statements that the contents are not officially part of the product until they ship, that they may not ever ship as part of the product, don’t make any decisions based on the content as the features discussed are subject to change, etc..  The items on the list, however, do show the continued investment, and thus continued commitment, that Oracle has to Essbase.

This year, the items mentioned as areas of concentration over the next year show continued work on Essbase scalability, dependability, performance and integration with the Oracle Fusion strategy.  From my quickly and messily scribbled notes, some of the items on the list include:

• 6 new calc functions
• Parallel Fix
• Java API performance improvements
• APS/Essbase custom aggregates and lists
• Dimension properties
• MDX improvements
• Fusion Middleware security integration
• Dimension build performance increases
• Calc runtime parameters
• Resolve naming restrictions

My favorite items on the list are the Java API performance improvements and the Essbase custom aggregates.  We use exclusively the Java API inside Dodeca and any performance improvements can only be a good thing.  Personally, I think that grid retrievals are quite fast in the Java API already.  In fact, we see retrievals in Dodeca that are faster than the classic Excel add-in.  That being said, many of the APIs that query member information are pitifully slow plus they consume an unnecessarily large number of ports.  

On the other hand, I can only imagine the functionality that Essbase custom aggregates could bring to the product.  From the sounds of it, it will provide a way to define custom members that simply provide a total of values from other selected members in a dimension.   For example, I can envision a use-case where I could define “Tim’s Region” as the total of Michigan, California and Oregon.  This personal member would then be available for retrieve/zoom/pivot and other Essbase operations.   Until we actually see it, we will not know for sure what it can do.  I do hope, however, it is exposed via the Java API so we can develop some cool solutions around them.

Which of these features would you like to see in the product?  What features would you like to see that are *not* on this list?

Two Weeks Left to Vote in the ODTUG Board of Directors Elections

If you are a paid ODTUG member, you only have two weeks left to vote for the slate of candidates for the ODTUG Board of Directors.  The Board of Directors guides the business of the organization, including its largest event, the annual Kscope conference.

This year, there are 11 professionals, including me, running for 4 spots on the board.  The candidates include 1 Oracle Application Express specialist, 4 database tools specialists, and 6 Hyperion/EPM specialists.  I think I can speak for all of the candidates when I say we are all willing to volunteer many, many hours to help the various communities that comprise ODTUG.  Don't forget to make your contribution to the community and vote.  If I am re-elected to the Board of Directors, I will be happy to serve you and will feel lucky to serve with any and all of this year's candidates.

ODTUG provides many opportunities to volunteer and contribute to the Oracle EPM user community.  If you are interested in getting more involved, let ODTUG know at http://www.odtug.com/volunteer.

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..

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.

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.

The conference ended midday Thursday and, after a quick Board of Directors lunch, I finally got to head out to the pool with my family and float around the lazy river.  San Antonio was a great place to visit and to have a conference.  Next year, Kscope13 returns to New Orleans, and you can bet it will be both a great learning experience and a lot of fun; I hope to see you all there.