Wednesday, October 17, 2012

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?


Tuesday, October 16, 2012

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.

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.


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.


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.

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.

Tuesday, June 5, 2012

Dodeca on iPad

My friend Cameron Lackpour had asked to see this, so here it is..   Dodeca on iPad.  Dodeca provides extremely fast Essbase operations and the ability to return huge data sets.  Now you can take it with you.

Dodeca on an iPad showing a dynamically generated Essbase input form.

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.

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>

Tuesday, April 17, 2012

Dodeca and the Outline Extractor Now Supported on Essbase 11.1.2.2

Today, we have made the Dodeca-Essbase server for Essbase 11.1.2.2 available to licensed customers.  Support for this version of Essbase comes just 6 days after Oracle made the 11.1.2.2 release and shows our dedication to support for Oracle Essbase.  In addition, the ability to support this new release so quickly validates the superior architecture of Dodeca.  With this release, Dodeca supports every version of Essbase between 6.5.3 and 11.1.2.2 and features the unique ability to query data from any supported Essbase version into a single Dodeca report.  This ability, along with Essbase retrieval performance often exceeding 100,000 cells per second, shows Dodeca is the fastest and most agile Essbase product on the market.

In addition, we also posted an 11.1.2.2 version of the OlapUnderground Outline Extractor to the download site at  http://www.appliedolap.com/resources/downloads

Thursday, April 12, 2012

My Top 10 List of Reasons to Upgrade to the New Smart View 11.1.2.1.102


Recently, I did a presentation on the new Smart View 11.1.2.1.102 at the ODTUG Hyperion SIG event in Dallas.  In the presentation, I listed all of the technical innovations that brought this version of Smart View closer to the functionality of the classic Excel add-in for Essbase.  I also spent some time talking about the remaining differences between Smart View and the classic Excel add-in.  It was during this part of the presentation that I was asked a question I was completely unprepared for.  Someone asked me, “Why should we upgrade to Smart View over the classic add-in?”

At that moment, my mind went blank.  Maybe it was due to lack of sleep or maybe it was due to the fact that when I look at the new feature list, I feel the reasons to upgrade are pretty self-apparent.  Since that presentation however, I have stepped back and tried to look at it from more of a user's perspective.  In doing that, I came up with this list of reasons to upgrade.  Of course, some of the reasons are more compelling than others, so I decided to rank them based on what I would want as a user.

10. Smart View has integration with other Oracle products; the classic add-in does not.  Okay, if you exclusively use Essbase, this argument may not help convince you to move to the new Smart View.  If, however, you use any of the other products, this can help you get a better return on your investment in Oracle technology.

9. Smart View has integration with other Microsoft Office technologies; the classic add-in does not.  This can save you time if you have a recurring need to bring Essbase data into Microsoft Word or Microsoft Powerpoint.

8. Smart View allows you to setup favorite private connections that make it much easier, and faster, to connect to your favorite cubes.

7.

Smart View has new options for formula and format fill that leverages Excel formatting in ways not available in the classic Excel add-in. These options automatically apply Excel formatting from the Zoom-In operation source cells. Here is a simple example. In this grid, you can see that the cell containing the member name Cola has been formatted with a background color.

After a Zoom-In operation, the children of Colas also get the same formatting. This could be a very useful functionality for some users as they explore data in Essbase.

6. Smart View has a number of VBA functions which were not available in the classic Excel add-in. These VBA functions help customers who are would like to do lightweight automation tasks with Essbase. Of course, in the classic Excel add-in, you could revert to the Essbase API to do some of these tasks, but that added its own level of maintenance and deployment problems. Further, many customers have tried to use Excel as a development platform to build robust Essbase applications. Excel is a very good end user tool, but it is not a very good platform for building robust applications. Instead, if you need a robust Essbase development platform, save yourself a lot of time, effort, and pain and by checking out Dodeca. (Disclosure: I am the founder of, and lead the development team for, the company that publishes Dodeca.)

5. Multiple range operations are easy to use in Smart View; they are not easy to use in the classic Excel add-in.  This gives companies much more flexibility in designing their templates without the need for VBA coding.  The multiple range operation functionality embeds information about the different cubes within the workbook so that it can be refreshed without the need for the user to manually select a given range and connect to different cubes.  This makes these operations faster, more dependable, and much less susceptible to user error in Smart View than in the classic Excel add-in.

4. When you perform a cascade in Smart View, the Excel sheet tabs are appropriately named.  Cascade functionality is a very powerful feature of multidimensional databases, but it was severely limited by way it was implemented in the classic Excel add-in.  In the classic add-in, users had the choice of splitting the cascade into different worksheets or workbooks, but the worksheets, or workbooks, were named using a numbering system.  Users had to look at an optional tab of contents to determine which worksheet contained data for a specific member.  In the new version, the sheet names are based on the sheet member in the cascade.

3.

The ability to query more than 65,535 rows of data gives users the ability to see more data if necessary.  You may say to yourself, “Why would anybody need to see this much data?”  With today’s larger and larger datasets, it is easily possible for a ‘Next Level’ zoom to exceed the row limit enforced in the classic add-in.  Further, the classic add-in does not display the first 65,535 rows, but rather it puts up an error messages and prevents users from seeing those rows.  Here is a screenshot showing a large retrieve where I did a zoom-in to all levels of the largest 4 dimensions of Sample Basic.


Of course, if you try the same thing in the classic Excel add-in, you don't even get that far as you very quickly get an error message telling you that you have exceeded the maximum retrieval size.


2. Smart View users can have multiple instances of Excel open at once.  This may not seem like a big deal, but those of us who live and die in Excel feel very constrained if we have to be careful not to launch a second Excel instance.  What's the big deal, you ask?  The big deal is that the classic Excel add-in cannot do this.  Once you open a second instance of Excel, the add-in becomes very undependable as it can lose track of its connected worksheets.  I posted a 15 second video on my blog a couple of years ago that shows just how easy it is for multiple Excel instances to get mixed up.  Check out the video at http://timtows-hyperion-blog.blogspot.com/2009/09/fun-with-excel.html.

1. Smart View is the strategic direction for Oracle; the classic add-in will start to sunset in the next 4 to 5 years.   This isn’t something you can wait out hoping the issue will go away.  The classic add-in will be done.

When Oracle first started working on these improvements, they asked my opinion of their proposals.  I told them that, if they implemented the proposed changes, then Smart View would be good enough that people could use it to author Dodeca templates.  I am glad they have finally hit that milestone with the new Smart View 11.1.2.1.102.

Thursday, April 5, 2012

MTG Recorded Webcasts

My friend Ron Moore over at Marketing Technologies Group send me a link to the recorded webcasts they have on their site and asked that I get the word out about their presence.  Ron and I go back many years and, in fact, I used to teach Essbase Excel VBA classes for him at his training room in New York.  Ron is also a great sponsor of the ODTUG Hyperion user group activities and these videos are a great contribution to the community.

Here is what the recorded library looks like.

There are currently 38 webcast videos in the library on a variety of topics.  You can see the webcast library at https://mtg.webex.com/mw0306ld/mywebex/default.do?siteurl=mtg.  This link takes you to the webcast schedule.  From there, click on the View Event Recordings link in the upper right corner to access the archived videos.




Saturday, March 31, 2012

New Dodeca Whitepaper En Francais!

Our partners at Limpida in Paris have recently published a whitepaper on Dodeca in French!  We recently hosted two Limpida consultants in our office here in Huntsville.  During their week in Huntsville, we had the opportunity to give them some in-depth training on Dodeca so they can provide quality implementation services back home in France.

I also used their visit to work on my French, the but progress is very slow (ou trop lent pour ceux d'entre vous en France), so I haven't translated the whitepaper.  I trust is says good things about Dodeca!


Tuesday, March 20, 2012

Kscope 12 Early Bird Deadline is Almost Here

If you are planning to attend Kscope 12 in San Antonio and you have not yet registered, be sure to register this week as Early Bird discounts end next Monday and the price goes up $150 $300. Better yet, register this week and use the Applied OLAP discount code, AOLAP, and you will save another $100.

Kscope features the world's most intensive learning opportunities for Essbase, Hyperion Planning, Hyperion Financial Management, Oracle Application Express (APEX) and Oracle Fusion Middleware.  Register today and I will see you there!

Tuesday, March 6, 2012

Smart View 11.1.2.1.102 Performance Tip

A couple of weeks ago, I was preparing to speak on the new Smart View 11.1.2.1.102 at the ODTUG Hyperion SIG Dallas meeting and found a performance issue when trying to zoom in on large datasets.  Here is the sheet I was zooming:





I used Sample Basic and zoomed in to all levels on Year, Measures, and Product.  When I zoomed in on Market, Smart View never returned but rather just kept trying to zoom in for a while.  After working on it for a bit, I decided to try the same thing in the classic Excel add-in and, guess what?  The same sequence of operations is not even possible there as it easily shoots past the 65,535 rows supported by the classic add-in.   I tried it in Dodeca and it returned a bit over 172,000 rows in 5 seconds, so it certainly isn't an issue with the Essbase server and large retrieves.

I next talked to one of my friends inside Oracle and found the source of the performance issue.  If you have used the classic Excel add-in, you may remember seeing the Use Styles option which formats the cells returned by Essbase based on member cell or data cell attributes.  Based on my VBA experience, I can tell you that doing lots of format operations in an Excel worksheet can be quite slow.  In Smart View 11.1.2.1.102, there is a new option called Use Excel Formatting which also defaults to false.  This setting is conceptually the opposite of Use Styles in the classic add-in.  In other words, Smart View 11.1.2.1.102, along with all previous versions of Smart View, automatically formats the cells that are returned.  In fact, you can see the formatting in the screenshot above.

To prevent this automatic formatting and speed up your retrievals, set the Use Excel Formatting option to true.




Once I made this change, the zoom in operation on Market did complete and brought back more than 172,000 rows.

Another thing you may note from the spreadsheet is that member labels are repeated.  This is another difference between the classic add-in and Smart View.  The Repeat Member Labels option has been renamed Suppress Repeated Members and the default behavior has changed.  With Smart View, you must select the option to Suppress Repeated Members to see the same behavior you saw by default in the classic add-in.  Frankly, I don't remember if this is something new in Smart View 11.1.2.1.102, but just be aware of it.  In the screenshot below, I have set the Suppress Repeated Members setting to mimic the classic add-in.



Tuesday, February 28, 2012

APS 11.1.2.2 New Feature: Essbase Web Services

APS 11.1.2.2 appears to have a significant new feature, Essbase Web Services.  Here is an excerpt from the readme file posted on the Oracle website:

Essbase Web Services

Web services are self-contained, modular applications that one can describe, publish, locate, and invoke over a network. Web services allow access to regular applications using a Web browser. Web services use XML to code and decode data, and SOAP (Simple Object Access Protocol) to transport it.
Essbase Web Services will expose Essbase user and administrative functionality in a services-oriented (SOA) environment via Provider Services, and allow Essbase to be easily integrated with both Oracle and third-party applications.

The APS Admin Guide also posted at the Oracle site has information on the Essbase Web Services and what you can do with them.  It appears you can query data and metadata along with performing some admin tasks.  One thing that looks useful immediately is the ability to execute a Maxl statement via a web service.  This would eliminate the need to install the Essbase client just to run Maxl statements; I have one customer who could benefit greatly from this functionality today.

Hopefully I will get a chance to play with these soon and post some sample code.  On the other hand, ten years ago I wrote web services to retrieve Essbase grid data into Dodeca and they are *very* fast, so I don't see myself working very hard on those particular APIs.

Essbase 11.1.2.2 Patch Set; Full 11.1.2.2 Update Version Still To Come?

There is a patch set out for Essbase 11.1.2.2 which is apparently in connection with the release of the Exalytics system.  According to the docs, however, it appears there may be a full update on the way as well.  This is from the Essbase readme file:

About this Patch

This Readme file describes the defects fixed in this patch set (PS) and the requirements and instructions for applying this PS.


Who should apply this patch: This PS contains defect fixes and changes that are specific to the Oracle Exalytics In-Memory machine. You should install this PS only in the following circumstances:
  • You are installing Essbase on the Exalytics In-Memory Machine, or
  • There is an urgent need for a defect fix that is included in this PS
Customers considering this PS for a platform other than the Exalytics In-Memory Machine should carefully review the list of fixed defects. If there is not a truly urgent need for a defect fix included in this PS, Oracle recommends customers install the upcoming Enterprise Performance Management (EPM) 11.1.2.2.000 release, which will contain an update, instead of this patch set. To check the availability of the EPM 11.1.2.2.000 release prior to installing this PS, visit the Oracle Software Delivery Cloud (https://edelivery.oracle.com).

Another note in the readme leads me to believe the Essbase VB API lives!  There is a defect fixed in this patch that is described as "API. Update version of esb32.bas file."  It looks like we need to get to work on the 11.1.2.2 Outline Extractor and other OlapUnderground tools.



Tuesday, February 7, 2012

Wow! More Than 1 Million Rows of Data per Minute!

As we continue to work on Dodeca, we continue to stretch the limit.  Today was certainly evidence of that. Dodeca already has the most powerful relational/spreadsheet technology available in the world, but our development team is not yet satisfied.  Instead, they are working on making it even better.  Specifically, they have implemented a cursored retrieval that will allow the Dodeca client to request a specified number of rows on each trip to the server.

To test this functionality, our dev team created some very large retrieves.  The SQL itself was fairly simple in that it returned only 7 columns of data and joined only 2 tables.  The performance we saw, however, was outstanding.  In tests conducted on a debug build of Dodeca, they saw over 1,030,000 rows of data, or a total of over 7,200,000 cells of data, retrieved from SQL Server and filled into a worksheet in about 37 seconds.  That means Dodeca is processing nearly 195,000 cells per second!

Many people ask me, "Why would you want to retrieve 1,000,000 rows of data into a spreadsheet?"   Well, we have customers who retrieve massive amounts of data.  We believe that, as a software provider, it isn't our place to second-guess why a customer wants to do something like that.  Instead, we focus on allowing customers the freedom to do their business their way; Dodeca just makes it faster and easier.  At 195,000 cells per second, I would call it really fast!  How would you like to see performance like that when you do an Essbase drill-through?


Thursday, February 2, 2012

Smart View Dialog Positions

There was a question on the Network54 Essbase Board today about the member selection dialog in Smart View rendering off the screen.  It seems like I may have read something about this issue and a possible solution, so I decided to spend a few minutes investigating it..

The problem occurs when you have multiple monitors hooked up to your computer, open Smart View, connect to an Essbase database, open the Member Selection dialog and then move it onto your second monitor.  Smart View remembers where you moved the dialog and will display it there the next time you open the dialog.  The problem is, if you no longer have your second monitor attached, the dialog will display off-screen.

I took a look at the registry and found there is a DialogPositions setting in the registry under HKEY_CURRENT_USER\Software\Hyperion Solutions\HyperionSmartView\Options.





I found that you can close Excel/Smart View, delete the DialogPositions parameter from the registry, and restart Excel and the Member Select dialog will again be displayed centered on the main monitor.

You can make this easier for end users by creating a registration entries file.  On my machine, I created a text file and named it ResetSmartViewDialogs.reg.  In this file, I added the following lines:

REGEDIT4

[HKEY_CURRENT_USER\Software\Hyperion Solutions\HyperionSmartView\Options]

"DialogPositions"=-


To run the file, just double-click on it and you will be warned about the changes to be made to the registry. Simply confirm that you want the entries to be made and the offending DialogPositions setting will be removed.




Monday, January 30, 2012

The Book..

You may have noticed that I haven't been posting consistently over the past few months.  The main reason for that is that I have been involved in writing a book.   Actually, I wrote one chapter of a book, but it was intense work on the Essbase Java API.

The title of the book is Developing Essbase Applications: Advanced Techniques for Finance and IT Professionals which is the brainchild of, and potential cause of heart failure for, my friend Cameron Lackpour. The chapter list is quite impressive:
  • Essbase infrastructure
  • Data quality
  • Essbase Studio
  • BSO in depth
  • BSO to ASO conversions
  • Designing ASO for performance
  • Practical  MDX
  • Essbase Java API
  • Automating with Groovy
  • Advanced Smart View
  • Administering Essbase
Cameron recruited a team of Essbase professionals to come together on a book, defined what he wanted, managed us authors who whined about the amount of work it turned into, and lived to tell the story.  This book was written by a collection of authors with large amounts of real-life Essbase experience.  Almost all of these authors are speaking at Kscope12 as well. :
  • Dave Anderson;
  • Joe Aultman;
  • John Booth;
  • Gary Crisci;
  • Natalie Delemar;
  • Dave Farnsworth;
  • Michael Nader;
  • Dan Pressman;
  • Robb Salzmann; and
  • Angela Wilcox
The first draft of the book is now complete and has been sent to the publisher.  We are hoping the book will be published before, and available at, Kscope12 in late June. 

Tuesday, January 24, 2012

Kscope12 Venue

This past weekend, I participated in the ODTUG Board of Directors meeting which was held at the JW Marriott Hill Country in San Antonio, TX.  This will be the location of Kscope12 and I have to tell you, I was very impressed by the hotel and grounds.  First and foremost, the meeting areas for the conference are close together, so you will not need to walk very far between sessions, to get to the Exhibit Area or to get to lunch.

One of the big benefits of attending a conference is the one-to-one networking that you can do with other attendees and with the presenters.  This hotel looks like it will make that very easy.  The central atrium of the hotel has a large meeting area called the Crooked Branch Lobby that features a multiple fireplaces, sitting areas, games and a bar.  There are also multiple fireplaces and sitting areas just outside on the patio.  My guess is this area will be a prime gathering spot at Kscope12.  The picture I posted here does not do it justice.
The Crooked Branch Lobby
Just off the Crooked Branch Lobby is a sports bar called High Velocity.  This place has TV screens nearly everywhere and will be a popular hangout for sports fans.

High Velocity Sports Bar
Given that the content will, as always, be extremely good, the networking opportunities at this conference will be the best ever.  If you haven't registered yet, you can register at http://kscope12.com/ and use our discount code AOLAP to get $100 off.  I hope to see you there!

Wednesday, January 18, 2012

Kscope 2012 Sessions and Discount Code

Sorry I haven't posted anything in the past month or so, but recently I have been spending all of my writing efforts on some material for an upcoming book.  Thankfully, my writing on that project is now complete and I hope to spend more time writing here.

Since I posted last, I have learned I will be giving two presentations at Kscope12.  On Monday, 6/25/2012, I will be presenting on the topic of Oracle Fusion Accounting Hub: Where Oracle Fusion and Essbase Become One.  I think this will be a very interesting session that talks about the strategic importance of Essbase in the Oracle Fusion stack.  On Thursday, 6/28/2012, I will be presenting Advanced Essbase Java API Tips and Tricks.  I have done this session at the last several Kscope conferences, but try to add new content each time.

This year, Kscope12 features four distinct Hyperion related tracks including tracks for Hyperion Applications, EPM Business Content, Essbase and Beginner Essbase.  The session schedule is now posted on the Kscope website at http://caat.odtug.com/odtug_caat/caat_abstract_reports.location_schedule?conference_id=90&location_group=ALL.

If you have not yet registered for Kscope, register today and use the Applied OLAP discount code, AOLAP, to save $100 off your registration.  You can register for Kscope 2012 at http://kscope12.com/registration.  I am headed to the conference hotel tonight to help with conference planning and to participate in the ODTUG Board of Directors meeting.  In a previous board meeting, I voted for the selection of this property, the JW Marriott Hill Country, as the location for Kscope 2012 as I believe it will be a great place to learn and it will be a great place for families to play (while us tech people learn).   This will be my first visit to the property and I will let you know what I think.