Thursday, December 19, 2013

Meet the Oracle ACE Directors Panel - January 9 - Seattle

I will be in Seattle on Thursday, January 9th for the Meet the Oracle ACE Directors Panel.  It is at the Sheraton Seattle from 4 - 6 pm and will feature several other ACE Directors including Martin D'Souza, Kellyn Pot'Vin, Tim Gorman, and my longtime friend and collaborator, Cameron Lackpour.  

Come see and the panel and stay for the Happy Hour; the beer will be on me!

Friday, December 13, 2013

Smart View Internals: Exploring the Plumbing of Smart View

Ever wonder how Smart View stores the information it needs inside an Excel file?  I thought I would take a look to see what I could learn.  First, I created this simple multi-grid retrieve in Smart View.

Note that the file was saved in the Excel 2007 and higher format (with an .xlsx file extension).  Did you know that the xlsx format is really just a specialized zip file?  Seriously.  It is a zip file containing various files that are primarily in xml format.  I saved the workbook, added the .zip extension to the filename, and opened it in WinRar.  Here is what I found.

I opened the xl folder to find a series of files and folders.

Next, I opened the worksheets folder to see what was in there.  Of course, it is a directory of xml files containing the contents of the worksheets.

My Essbase retrieves were on the sheet named Sheet1, so let’s take a look at what is in the sheet1.xml file.   The xml is quite large, so I can’t show all of it here, but needless to say, there is a bunch of information in the file.  The cell contents are only one of the things in the file.  Here is an excerpt that shows the contents of row 5 of the spreadsheet.

This is interesting as it shows the numbers but not the member name.  What is the deal with that?  I noticed there is an attribute, ‘t’, on that node.  I am guessing that the attribute t=”s” means the cell type is a string.  I had noticed that in one of the zip file screenshots, there was a file named sharedStrings.xml.  Hmm...  I took a look at that file and guess what I found?

That’s right!  The 5th item, assuming you start counting at zero like all good programmers do, is Profit.   That number corresponds perfectly with the value specified in the xml for cell B5, which was five (circled in blue in the xml file above).   OK, so when are we going to get to Smart View stuff?  The answer is pretty quick.  I continued looking at sheet1.xml and found these nodes near the bottom.

Hmm, custom properties that contain the name Hyperion?  Bingo!  There were a number of custom property files in the xml file.  Let’s focus on those.

Custom property #1 is identified by the name CellIDs.  The corresponding file, customProperty1.bin, contained only the empty xml node <root />.  Apparently there aren’t any CellIDs in this workbook.

Custom property #2 is identified by the name ConnName.  The file customProperty2.bin contains the string ‘Sample Basic’ which is the name of my connection.

Custom property #3 is named ConnPOV but it appears to contain the connection details in xml format.  Here is an excerpt of the xml.

Custom property #4 is named HyperionPOVXML and the corresponding file contains xml which lines up with the page fields I have in my worksheet.

What is interesting about the POV xml is that I have two different retrieves that both have working POV selectors which are both implemented as list-type data validations in Excel.  I don’t know what happens internally if I save different values for the POV.

Custom property #5 is labeled HyperionXML.  It appears to contain the information about the Essbase retrieval, but it doesn't appear to be the actual retrieval xml because it doesn't contain the numeric data.  My guess is that this xml is used to track what is on the worksheet from a Hyperion standpoint.

There is a lot of information in this simple xml stream, but the most interesting information is contained in the slice element.  Below is a close-up of contents in the slice.

The slice covers 6 rows and 7 columns for a total of 42 cells.  It is interesting that the Smart View team chose to serialize their XML in this manner for a couple of reasons.  First, the pipe delimited format means that every cell must be represented regardless of whether it has a value or not.  This really isn’t too much of a problem unless your spreadsheet range is pretty sparse.  The second thing about this format is that the xml itself is easy and fast to parse, but the resulting strings need to be parsed again to be usable.  For example, the vals node will get split into an array containing 42 elements.  The code must then loop the 42 elements and process them individually.  The other nodes, such as the status, contain other pieces of information about the grid.  The status codes appear to be cell attributes returned by Essbase; these attributes are used to apply formatting to cells in the same way the Excel add-in UseStyles would apply formatting.  There are a couple of things to take away:

  1. In addition to the data on the worksheet itself, there is potentially *a lot* of information stored under the covers in a Smart View file.
  2. String parsing is a computation-intensive operation and can hurt performance.  Multiply that workload by 8 because, depending on the operation and perhaps the provider, all 8 xml nodes above may need to be parsed.

In addition, the number of rows and columns shown in the slice may be important when you are looking at performance.  Smart View must look at the worksheet to determine the size of the range to read in order to send it to Essbase.  In the case of a non-multi-grid retrieve, the range may not be known and, as a result, the grid may be sized based on the UsedRange of the worksheet.  In our work with Dodeca, we have found that workbooks converted from the older xls format to the newer xlsx format, which support a larger number of cells, may have the UsedRange flagged internally to be 65,536 rows by 256 columns.  One culprit appears to be formatting applied to the sheet in a haphazard fashion.  In Dodeca, this resulted in a minor issue which resulted in a larger memory allocation on the server.   Based on the format of the Smart View xml, as compared to the more efficient design of the Dodeca xml format, if this were to happen in Smart View it may cause a larger issue due to the number of cells that would need to be parsed and processed.  Disclaimer: I did not attempt to replicate this issue in Smart View but rather is an educated guess based on my experience with spreadsheet behavior.

Note: The Dodeca xml format does not need to contain information for cells that are blank.  This format reduces the size and the processing cycles necessary to complete the task.  In addition, when we originally designed Dodeca, we tested a format similar to the one used today by Smart View and found it to be slower and less efficient.

Considering all of this information, I believe the xml format would be difficult for the Smart View team to change at this point as it would cause compatibility issues with previously created workbooks.  Further, this discussion should give some visibility to the fact that the Smart View team faces an on-going challenge to maintain compatibility between different versions of Smart View considering that different versions distributed on desktops and different versions of the internal formats that customers may have stored in their existing Excel files.  I don’t envy their job there.

After looking at all of this, I was curious to see what the xml string would look like on a large retrieve, so I opened up Smart View, connected to Sample Basic and drilled to the bottom of the 4 largest dimensions.  The resulting sheet contained nearly 159,000 rows of data.  Interestingly enough, when I looked at the contents of customProperty5.bin inside that xlsx file, the contents were compressed.  It occurred to be a bit strange to me as the xlsx file format is already compressed, but after thinking about it for a minute it makes sense as the old xls file format probably did not automatically compress content, so compression was there primarily to compress the content when saved in the xls file format.

Custom property #6 is labeled NameConnectionMap.  The corresponding property file contains xml that appears to map the range names in the workbook to the actual grid and the connection.

Custom property #7 is labeled POVPosition. The file customProperty7.bin contains the number 4 followed by a NUL character.  Frankly, I have no idea what position 4 means.

Moving on to custom property #8 which is labeled SheetHasParityContent.  This file contains the number 1 followed by a NUL character.  This is obviously a boolean flag that tells the Smart View code that new features, such as support for multiple grids, are present in this file.

Custom property #9 is labeled SheetOptions.  The corresponding file, customProperty9.bin, contains an xml stream that (obviously) contains the Hyperion options for the sheet.

Custom property #10 is labeled ShowPOV and appears to contain a simple Boolean flag much like that in custom property #8.

Finally, custom property #11 is labeled USER_FORMATTING and may not be related to Smart View.

I did look through some of the other files in the .zip and found a few other references to Smart View, but I did not see anything significant.

So, now that we have completed an overview of what is contained in one, very simple, multi-grid file, what have we learned?

  1. There is a bunch of stuff stored under the covers when you save a Smart View retrieve as an Excel file.
  2. With the reported performance issues in certain situations with Smart View, you should now have an idea of where to look to resolve Smart View issues in your environment.

There are a number of files I did not cover in this overview that could also cause performance issues.  For example, Oracle support handled one case where they found over 60,000 Excel styles in the file.  Smart View uses Excel Styles when it applies automatic formatting to member and data cells.  When there are that many styles in the workbook, however, it is logical that Excel would have a lot of overhead searching through its internal list of Style objects to find the right one.  Accordingly, there is a styles.xml file that contains custom styles.  If you have a bunch of Style objects, you could delete the internal styles.xml file.

Note: Be sure to make a copy of your original workbook before you mess with the internal structures.  There is a possibility that you may mess it up and lose everything you have in the workbook. Further, Oracle does not support people going under-the-covers and messing with the workbook, so don’t even bring it up to support if you mess something up.

Wow, that should give you some idea of what may be going on behind the scenes with Smart View.  Even with the experience I have designing and writing the Dodeca web services that talk to Essbase, I wouldn't say that I have a deep understanding of how the information in a Smart View workbook really works.  However, one thing is for certain;  Dodeca does not put stuff like this in your Excel files.  It may be interesting to hear what you find when you explore the internals of your workbooks.

Monday, November 25, 2013

Dodeca Now Available for Download!

This past Friday, November 22nd, we completed our work on the newest version of the Dodeca Spreadsheet Management System and made Dodeca available for download from our website.  This blog entry is a sneak peek at some of the new features in version 6.6, as well as 6.5, which was released to select customers with specific functionality requests.  There are a few features that are particularly useful for end users, so let’s start there.

More Excel Support

Dodeca has always been strong on Excel version support and this version delivers even more Excel functionality.  Internally, we use the SpreadsheetGear control, which does a very good job with Excel compatibility.  This version of Dodeca integrates a new version of SpreadsheetGear that now has support for 398 Excel functions including the new SUMIFS, COUNTIFS, and CELL functions.

Excel Page Setup Dialog

The new version of Dodeca includes our implementation of the Excel Page Setup Dialog which makes it easy for users to customize the printing of Dodeca views that are based on Excel templates.  Note that for report developers, the Excel Page Setup has also been included in the Dodeca Template Designer.

New PDF View Type

Customers who use PDF files in their environments will like the new PDF View Type.  In previous releases of Dodeca, PDF documents displayed in Dodeca opened in an embedded web browser control.  Beginning in this version, Dodeca includes a dedicated PDF View type that uses a specialized PDF control.

View Selector Tooltips

Finally, users will like the new View Selector tooltips which optionally display the name and the description of a report as a tooltip.


Performance is one of those things that users always appreciate, so we have added a new setting that can significantly improve performance in some circumstances.  Dodeca has a well-defined set of configuration objects that are stored on the server and we were even awarded a patent recently for the unique aspects of our metadata design.  That being said, depending on how you implement reports and templates, there is the possibility of having many queries issued to the server to check for configuration updates.  In a few instances, we saw that optimizing the query traffic could be beneficial, so we have implemented the new CheckForMetadataUpdatesFrequencyPolicy property.  This property, which is controlled by the Dodeca administrator, tells Dodeca whether we should check the server for updates before any object is used, as was previously the case, only when a view opens, or only when the Dodeca session begins.  We believe the latter case will be very useful when Dodeca is deployed in production as objects configured in production often do not change during the workday and, thus, network traffic can be optimized using this setting.  The screenshot below shows where the administrator can control the update frequency.

Though users will like these features, we have put a lot of new things in for the people who create Dodeca views and those who administer the system.  Let’s start with something that we think all Dodeca admins will use frequently.

Metadata Property Search Utility

As our customers continue to expand their use of Dodeca, the number of objects they create in the Dodeca environment continues to grow.  In fact, we now have customers who have thousands of different objects that they manage in their Dodeca environments.  The Metadata Property Search Utility will help these users tremendously.

This utility allows the administrator to enter a search string and locate every object in our system that contains that string.  Once a property is located, there is a hyperlink that will navigate to the given object and automatically select the relevant property.  This dialog is modeless, which means you can navigate to any of the located items without closing the dialog.

Note: this version does not search the contents of Excel files in the system.

Essbase Authentication Services

In the past, when administrators wished to use an Essbase Authentication service to validate a login against Essbase and automatically obtain Dodeca roles based on the Essbase user’s group memberships, they had to use an Essbase connection where all users had access to the Essbase application and database.  The new ValidateCredentialsOnly property on both of the built-in Essbase Authentication services now flags the service to check login credentials at the server-level only, eliminating the need for users to have access to a specific Essbase database.

New Template Designer Tools

Prior to Dodeca 6.x, all template editing was performed directly in Excel.  Since that time, however, most template design functionality has been replicated in the Dodeca Template Designer, and we think it is preferable due to the speed and ease of use with which users can update templates stored in the Dodeca repository.  We have added a couple of new features to the Template Designer in this version.  The first tool is the Group/Ungroup tool that allows designers to easily apply Excel grouping to rows and/or columns within the template.   The second new tool is the Freeze/Unfreeze tool that is used to freeze rows and/or columns in place for scrolling.

Parameterized SQL Select Statements

Since we introduced the SQLPassthroughDataSet object in the Dodeca 5.x series, we have always supported the idea of tokenized select statements.  In other words, the SQL could be written so that point-of-view selections made by users could be used directly in the select statement.  In a related fashion, we introduced the concept of parameterized insert, update, and delete statements in the same version.  While parameterized statements are similar in concept to tokenized statements, there is one important distinction under the covers.  In Dodeca, parameterized statements are parsed and converted into prepared statements that can be used multiple times and results in more efficient use of server resources.  The parameterized select statement was introduced in this version of Dodeca in order for customers using certain databases that cache the prepared statement to realize improved server efficiency on their select statements.

Workbook Script Formula Editor Improvements

We have also been working hard to improve extensibility for developers using Workbook Scripts within Dodeca.  In this release, our work focused on the Workbook Script Formula Editor.  The first thing we added here is color coding that automatically detects and distinguishes Excel functions, Workbook Script functions, and Dodeca tokens.  In the new version, Excel functions are displayed in green, Dodeca functions and parentheses are displayed in blue, and tokens are displayed in ochre.   Here is an example.

In addition, we have implemented auto-complete for both Excel and Dodeca functions.

New SQLException Event

Version 6.6 of Dodeca introduces a new SQLException event that provides the ability for application developers to customize the behavior when a SQL Exception is encountered.

XCopy Release Directory

Beginning in version 6.6, the Dodeca Framework installation includes a pre-configured directory intended for customers who prefer to distribute their client via XCopy deployment instead using Microsoft ClickOnce distribution.  The XCopy deployment directory is also for use by those customers who use Citrix for deployment.

Mac OS X Release Directory

The Dodeca Framework installation now includes a pre-compiled deployment for customers who wish to run the Dodeca Smart Client on Mac OS X operating systems.  What that means is that Dodeca now runs on a Mac without the need for any special Windows emulators.  Dodeca does not require Excel to run on the Mac (nor does it require Excel to run on Windows for that matter), so you can certainly save your company significant licensing fees by choosing Dodeca for your solution. 

In short, you can see we continue to work hard to deliver functionality for Dodeca customers.  As always, the Dodeca Release Notes provide detailed explanations of all new and updated Dodeca features.  As of today, we have decided to make the Release Notes and other technical documents available for download to non-Dodeca customers.  If you are curious about all of the things Dodeca can do, and if you aren't afraid to dig into the details, you can now download our 389 page cumulative Release Notes document from the Dodeca Technical Documents section of our website.  

Thursday, November 21, 2013

Save $250 on Kscope14 Registration Now!

If you work in the Essbase, Oracle EPM, or Oracle BI world, *the* place to be every June is the annual Kscope conference.  Registration is open for the next conference, Kscope14, coming next June in Seattle, WA.  If you are not currently a full ODTUG member, let me tell you how you can save $250 on the $1650 registration fee.

There are two steps you have to take to "save big".  First, become a full member of ODTUG for $99 and enjoy all of the benefits, including access to a members-only presentations library, throughout the year.  Next, register for Kscope14 and you are eligible for the members-only price of $1500 for a savings of $150.  While you are registering, simply use the code AOLAP to get an additional $100 discount!

My company, Applied OLAP, is one of top-tier Platinum Sponsors of Kscope14 and I will be there.  I hope to see you at the conference and, if you were able to save some money by using our exclusive AOLAP code, be sure to stop our booth, say hello, and learn how the Dodeca Spreadsheet Management System can help your company reduce spreadsheet risk, increase spreadsheet accuracy, and reduce costs.

Friday, November 1, 2013

Hyperion Update - Kscope14 Street Team Meetup in Seattle - November 8th

If you are in or near Seattle on Friday, November 8th, don't miss the opportunity to see the new features of the Hyperion release.  Oracle representatives are teaming together with the Kscope14 Street Team to bring you this 2-hour mini-session at the Fred Hutchison Cancer Research Center.  The session starts at 3 pm sharp and concludes with networking and a happy hour.  For details and to RSVP, visit

I won't be able to make it to this event, but I wish I *could* be there.  I can't think of a better way to kick off a weekend!

Saturday, September 28, 2013

The New / Free Tools Access

My team has been hard at work getting our new website out the door, and we feel that it will make for a better experience all-around for our users and the users of the free OLAPUnderground utilities.  To help make the experience better, we have implemented a new download process.  Previously, users who wished to download files selected the files they wished to download and then provided their email address.  Our server then sent an email with temporary links for the download.  The email process sometimes caused issues with email filters or incorrect emails, so we decided to work to provide a better experience by using an off-the-shelf software release component.  As a result of this change, we are now requiring users to have a user account to download software.  Fortunately, the user account is free and you can create your own account in about a minute.

On the new site, all of the free downloads are available from the 'Downloads' section under 'Resources.'

If you are not logged in and you attempt to access the 'Downloads' section of the site, you will be prompted to login or register:

If you already have a account, you can go ahead and login using your existing credentials, but if you don't, simply click the 'Register' button and fill out the registration form that pops up.

Once you have filled out the required information and pressed 'Register', a confirmation e-mail will be sent to the address provided on the registration form.

Click 'OK' and check your inbox for the confirmation e-mail.  Once you click the activation link, all you need to do is login to the site and you will have full access to the 'Downloads' section.  If you are a Dodeca user, we will provide you access to the 'Dodeca' downloads page, but as a free registered user, you will have access to the OLAPUnderground tools, as well as the Next Generation Outline Extractor.

Note:  Your Applied OLAP account username will always be the
e-mail address you used when you registered your account.

Go back to the 'Downloads' section after you log in to access the OLAPUnderground Utilities, as well as the Next Generation Outline Extractor.

We hope you find the new Applied OLAP website informative and easy-to-use.  While you are there, be sure to check out all of the new information about the Dodeca Spreadsheet Management System and sign up for a free, personalized web demo so you can learn how Dodeca can help you.

Wednesday, September 25, 2013

Oracle Open World 2013 General Session: Empowering Modern Enterprise Performance Management

Here at Oracle Open World 2013, I attended the general session entitled "Empowering Modern Enterprise Performance Management" which was led by Balaji Yelamanchili, Oracle Senior Vice President for Analytics and Performance Management Products.  His keynote focused on the development priorities within the Oracle EPM group.  Those priorities are:

  • Continued Investment in Existing Modules
  • EPM Applications in the Cloud
  • Continued Innovation in New Modules

There are a number of things planned in the area of continued investment in existing modules.  The investment areas include:

  • HFM platform work to make HFM cross platform and to improve performance.  In addition, these changes allow HFM to run on Exalytics which puts massive computing power under HFM.  For example, testing of HFM on Exalytics has shown that virtual close becomes a possibility even in complex consolidations.  One test that had 31,000 accounts, 2,000 legal entities and 66 currencies showed a reduction of consolidation time from 7+ hours down to less than 30 minutes.  This type of performance increase, if borne out in the real world, could have a real effect on how and when companies do their consolidations.
  • Optimization of Hyperion Planning and Hyperion Profitability and Cost Management for Exalytics.  This scalability allows for implementations that spread further in the company (i.e. more users), but also allow companies to consolidate their applications onto fewer physical machines.
  • Packaged OBI Analytics for Hyperion Planning.  The packaged analytics make it easier and faster to deploy Hyperion Planning with an added benefit of reduced consulting costs.

Oracle is continuing its move into the cloud arena with 2 services.

  • The Oracle Planning and Budgeting Cloud Service ("OPBCS") has been in customer and partner preview for a few months now and is nearly general availability.  Oracle is stressing that this isn't just moving the software to a managed service running on the cloud, but rather is a fundamental new paradigm for planning that focuses on self-serve models that have not existing since the inception of the classic, on-premise Hyperion Planning.  Because OPBCS must run without the need for IT interaction, users must be able to quickly and easily configure what they need from the service.  That being said, Oracle is taking a very conservative strategic approach with their Cloud Planning application as they are not trying to displace traditional on-premise Planning applications and move them to the cloud.  Rather, they are emphasizing the cloud for use in quick-to-deploy departmental applications or as a development platform that can be used to build an application for on-premise deployment.  Oracle expects to make the OPBCS available later this calendar year.
  • A new service that was announced at Open World 2013 is the new Financial Reporting Cloud Service.  Based on the slides and the comments made during the presentation, this isn't simply moving the classic Hyperion Financial Reporting application to the cloud, but rather they have imagined and created an application that appears to be targeted for statutory reporting.  How it appears to work is that the developer/administrator creates a framework for the reporting package which may be, for example, a corporate annual report and can assign responsibilities for different parts of the report to team members.  The editing of different components can be completed either via refreshing of data from the corporate data stores for items like a balance sheet or income statement, or edited in Word for things like the management discussion.  The product tracks workflow and approvals and, when complete, produces a PDF of the completed document.  It looks quite different than what we see today in HFR.
For innovation in new modules, Oracle is focused pretty heavily on mobile.  As of now, the focus on mobile has been on information consumption but Oracle plans to work towards full user functionality in the future.  They are using the 90/90 rule as a guide for their mobile effort by focusing on the functionality used by 90% of users 90% of the time.  In addition, they have created some purpose-specific applications such as the new EPM Workflow Mobile App that was created specifically for approval workflows for Planning and Financial Close.

Oracle has been working hard on the EPM Suite, but notice I didn't really say anything about my beloved Essbase.  Balaji did not say much about Essbase in his session, but rather left that for the Essbase Roadmap session that I also attended.  I will work on a blog post on that session soon.

Sunday, September 22, 2013

Essbase Meetup in San Francisco This Tuesday!

As you may or may not know, this week is the annual mega-conference Oracle Open World and, in conjunction with the conference, I am hosting, with fellow Oracle ACE Director Cameron Lackpour, an Essbase Meetup this Tuesday evening in San Francisco.  Unlike most events during the week of Open World, our Essbase meetup is open to both Open World attendees and non-attendees, so if you are in the San Francisco area, make your plans to attend.  Last year, we had several Oracle ACEs, ACE Directors, and Oracle product managers who attended; several of the same people have confirmed their attendance this year as well.

The meetup is at Specchio, a great Italian restaurant in the Mission District.  Specchio is co-owned by one of my friends, chef Gino Assaf, and has had some notable guests in the past couple of years including numerous NBA teams and one of my favorite rock bands, The Who.  Specchio is located at 2331 Mission Street in San Francisco.

If possible, please RSVP for the meetup at so we can get a count for the restaurant.  I hope to see you there!

Thursday, September 12, 2013

Kscope13 Top Speaker Award

I thought I would post a quick picture of the award I earned at Kscope13 as one of the Top 5 Speakers at Kscope13 for my presentation entitled "Introducing the Outline Extractor NG (Next Generation)".   The Next Generation Outline Extractor is a complete rewrite of the popular Outline Extractor.  The original extractor was written in VB which hasn't been supported by Microsoft for many years; the new extractor is written in Java.  You can download my slides, and the next generation outline extractor, from our website at

I was honored to earn the award which is based on some secret formula combining feedback scores from attendees and number of attendees.  I did have a couple of hundred or so attendees at my presentation which, in my opinion, speaks to the popularity of the outline extractor and not to the popularity of me having anything to say... <BigGrin>

Here is a picture of the nice award sent by ODTUG.

Wednesday, August 28, 2013

Find Your Brilliance

I’d like to interrupt our regularly scheduled programming to tell you about one my personal highlight's of Kscope 13 which was held back in June in New Orleans. Every year ODTUG announces who the keynote speaker will be well in advance of the conference. Most of the time, the speaker is a person of note; interesting, relevant, sometimes even inspiring. And then there are those times when, frankly, I’m not particularly interested in them or what they have to say. But this year was different. At the end of January, ODTUG announced that the Kscope 13 keynote speaker would be Doc Hendley.

Who is Doc Hendley, you ask? Well, from my perspective, Doc Hendley is one of the most inspiring and truly extraordinary individuals I’ve ever come across. And after meeting him and having the privilege of spending time with him in New Orleans, I’m proud and truly humbled to be able to call this man a friend. He is truly extraordinary, which is ironic when you consider that Doc thinks of himself as “just an ordinary, regular, everyday guy.”

Let me tell you, Doc is anything but. This is the story of how a boy who grew up in Greensboro, North Carolina saved thousands of lives all the way across the globe and in the process, proved to himself and everyone else that one person – even an ordinary regular everyday person - can do something extraordinary.

Doc was “just a bartender” and musician who worked and played in nightclubs in Raleigh, NC. In fact, bartending was the only job he’d ever had. But in his own words, he was “dying to make a difference in this world.” In 2003, standing behind the bar, he heard that polluted water kills more children globally than HIV/AIDS, Malaria, and Tuberculosis combined, yet at that time, no one aware of this crisis.

So what did Doc do? In his words, "He got angry, he got pissed off, he took action." And he did it the only way he knew how. He tapped into the "marginalized people in his community, the bar crowd, the regulars"  – the people that everyone else said were too ordinary - to create Wine to Water, an organization that would take him to the site of the greatest humanitarian disaster in the world – Darfur, Sudan, and eventually to 9 other countries. Doc lived in Darfur for a year, and taught the locals how to clean their water and utilize their own resources to keep it clean.

Ordinary guy? I don’t think so.

I watched his TEDx talk on YouTube before going to Kscope13. I was so moved by what he’d done, so overwhelmed, and so energized, that I made everyone in my company watch it before the conference. I wanted every person who worked for me to hear what Doc had to say, and to understand how we all can change the world if we try.  I love Doc's commitment to his cause and I hope we remain friends for a long time to come.

I know my commitments don't allow me to travel the world helping others like Doc does on a regular basis, but that doesn't mean I can't help.  We decided that Applied OLAP could help support the efforts of Wine to Water and so I presented Doc with a $5,000 check as our small contribution. During his keynote speech, Doc demonstrated, again, how one person, one donation, can change the world. I’m pledging to find a way to make a difference in the world too.

After all, I’m a regular ordinary every day kind of guy too.

Saturday, July 13, 2013

Next Generation Essbase Outline Extractor Released!

After a bunch of work, we have finally released the Next Generation Essbase Outline Extractor. So, what is the Next Generation Essbase Outline Extractor?  Well, the original outline extractor was written in Visual Basic and Visual Basic has has not been supported by Microsoft for a number of years.  In addition, the original Essbase Outline Extractor requires the 32-bit runtime client which is getting harder and harder to support.  In addition, users have asked for new functionality over the years and it just wasn't feasible to add the new functionality to a VB based product.

Some people have asked me "What about the new Maxl Outline Extractor?"  That is a good question, but keep in mind it only works with 11.1.2.x and higher plus it gives you an XML output. You still have to do something with the XML format for it to be useful.

The Next Generation Essbase Outline Extractor was completely redesigned from scratch.  The main design goals were:
  • Design for long term
    • Extensible
    • Use Oracle’s strategic technology
  • Design for supportability
    • Wider adoption
    • Wider platform choices
    • Less support calls
It was also architected for extensibility to enable new functionality to be added and to allow programmers to write their own extensions to the code.  I chose to write the extractor in Java for a number of reasons.  First, it is Oracle's strategic API for the future which means new features of Essbase will be available in the Java API first.  In addition, Java is multi-platform which means it runs on Windows, Linux, AIX, Solaris, and MacOS regardless of whether it is 32-bit or 64-bit.  In addition, with the Java API, we can ship a single zip download that has everything it needs (except Java itself).  There is no need for the runtime client with the new extractor!

The new Outline Extractor also supports a number of features.  Many of these features are ne:
  • Export one or more dimensions in an outline.
  • Filter members to to be exported by dimension.
  • Write to multiple outputs on a single pass of the outline.
  • Output multiple alias tables into a single output file.
  • Write to Hyperion Planning Outline Load Utility format.
  • Output to real relational databases via JDBC.
The extractor was also designed to be run by a batch process using Java properties files.  The properties files can be written from the GUI interface and directly executed from the GUI.  There is even built-in functionality that optionally encrypts the username and password on first usage.

Here are a few screenshots of the new extractor.  The first screenshot shows the GUI screen where you can select one or more writers for output.

The second screenshot shows the option screen showing the options for the Load File Text Writer.

The final screenshot shows the execution screen. 

The Next Generation Outline Extractor is available on our website at  If you have any questions on the new extractor, don't hesitate to contact us at

Monday, April 29, 2013

EPM Available for Download

Oracle Enterprise Performance Management became available over the weekend, so let the rush of upgrading begin!

Here at Applied OLAP, we have already started our work with Essbase  We have added the new version to our build list and have started testing Dodeca with Essbase  We will make the version of our server available to our customers within a few days.

One interesting fix that we have seen so far is that EPM uses an upgraded version of Java, Java 1.6 update 35.   The previous EPM version,, shipped with Java 1.6 update 29 which contained a bug related to the SQL Server JDBC Driver.  The issue, known as the BEAST vulnerability, caused SQL Server JDBC connections to hang unless you made a fix to the Windows registry.  I, for one, am glad that issue is resolved.

Thursday, April 11, 2013

Oracle Excellence Awards 2013 - Nominations Open

One of my friends at Oracle asked me to help get the word out about the Oracle Excellence Awards for Oracle Fusion Middleware Innovation. These awards recognize customers for their cutting-edge solutions using Oracle Fusion Middleware and are featured in Oracle publications, videos and case studies. If you think you have a cutting-edge solution and would like to compete for a free pass to Oracle OpenWorld 2013, this is your chance to shine! Here is a more information on how to enter.

The deadline for your nomination is Tuesday, June 18th, 2013. Good luck and, if you win, let me know so I can see your application at Open World!

Thursday, March 28, 2013

Dodeca Dashboard Screenshot

One of our customers recently sent us a screenshot of a dashboard they developed in Dodeca. This application, which is deployed to over 1,000 Essbase users worldwide, allows users to easily analyze over 250,000 SKU-level products by customer and by business unit. The application, which was implemented to replace a failed attempt at forecasting within SAP, uses Essbase and went live in less than 20% of the time that was put into the SAP system.

We think it looks great.  How about you?

Monday, March 25, 2013

Happy Birthday Applied OLAP

Last Monday, we reached a milestone at Applied OLAP as it was the 15th anniversary of our incorporation.  I founded Applied OLAP on March 18, 1998, because I was not satisfied with the status quo of Essbase reporting at the time which was, of course, the classic Excel add-in.  And, despite the fact that I was one of the original five Microsoft Excel MVP's in the world, I was frustrated by inherent limitations in the add-in which meant limitations to what users could do with the spreadsheet.   So, being the totally unreasonable* person that I am, I quit my job in order to start a company focused on making it easier for users to leverage Essbase.  Flash forward 15 years and now we have Dodeca!

*One of my favorite quotes is from the playwright, George Bernard Shaw:

"The reasonable man adapts himself to the world; the unreasonable one persists in trying to adapt the world to himself. Therefore, all progress depends on the unreasonable man."

Am I unreasonable?  Why yes.  Yes I am!

Wednesday, March 6, 2013

We Don't Make Essbase, We Make Essbase Better!

We have always claimed that Essbase was better with Dodeca, but until now we had only anecdotal evidence about how our customers get more return on their Essbase investment using Dodeca.  Now we have something a bit more concrete.  Recently, the annual BI Survey, which bills itself as the “world's largest vendor-independent survey of Business Intelligence and Performance Management users”, published the results of their annual survey, The BI Survey 12.  As in past years, we asked our customers to participate in the survey and, as a result, we earned a mention in the survey.  More importantly, we are thrilled by how we were mentioned.

One particular question in the survey asks customers how well they achieved their goals with their selected product.  In the past, Essbase has not scored well on this metric but, this year, for the first time ever, Essbase attained the highest score in goal achievement among all the products in the BI Survey.
  • Better than Microsoft Analysis Services?  Yes!  
  • Better than SAP BW?  Yes!  
  • Better than Cognos?  Yes!   
  • Better than Microstrategy?  Yes!  
  • Better than TM1 / Tableau / QlikTech???   Yes! Yes! Yes!   
  • Even better than OBIEE?   Yep!  
The analysis provided in the survey provides clues as to why the Goal Achievement Index for Essbase was the highest of all 25 products reviewed:

"This year for the first time we have a BI Giant with the highest goal achievement level. Oracle Essbase which has had quite low goal achievement scores in previous years, performs very well this year.  The sample includes more answers this year from companies that use Essbase in conjunction with front-end tools like Dodeca. These seem to increase the ratings for Essbase compared to ratings from users that only use the Excel Add-in."

That’s right.  Dodeca appears to be the reason that more customers achieve their goals with Essbase than with any other product.  If you are still using one of the Excel add-ins as your interface to Essbase, what are you missing?  Contact us today to learn more about how you can make Essbase better at your company using Dodeca and don't forget to ask about our no-cost evaluation program!

We have just released some exciting innovations in our most recent release.  Click here to read about the newest innovations inside the Dodeca Spreadsheet Management System.

Sunday, February 17, 2013

ODTUG/EPM Coming to Australia!

I can’t believe it has been nearly a year and a half since I visited Sydney and spoke at the ODTUG EPM Seriously Practical Conference.    It seems just like yesterday and I can’t wait to get back to the Land of Oz, but I am not making the trip this time.  However, my good friend Cameron Lackpour is going to make the trip and is working on some great presentations for the ODTUG EPM Seriously Practical Conference coming up March 21 and 22 in Melbourne, Victoria. New Queensland (Note to self: Remember to google things like this before you post your blog).  Cameron brings some great experience to share at this conference and, in fact, when I have an Essbase or Planning question, who do I call? I call Cameron.

Cameron will be stopping by New Zealand on his way to the Australia and speaking March 18 and 19 at the NZOUG Conference in Wellington.  If you are in either Australia or New Zealand, make sure to catch Cameron on this trip.

As a side note, I really wish I could be in Wellington for a couple of reasons.  First, the NZOUG Conference was really fun when I was there last year.  The other reason is more personal in that my wife’s grandfather spent significant time near Wellington, in Paekakariki, during World War 2.  Many Marines were there on the ground staging and training for the major beach invasions of the islands of the South Pacific.  He stormed the beach in four of the bloodiest battles of the war including Tawara where his unit lost nearly 90% of their men on the first day.  He remembers every detail to this day.  Many local New Zealand communities have organizations called the RSA (Returned Services Association) that operate to, among other things, welcome military personnel back to New Zealand.  I hope to visit the RSA in Paekakariki sometime in the future.

Wednesday, February 6, 2013

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

See you there!

Wednesday, January 16, 2013

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