Accounting Jobs and Software

I got an email recently from another blogger, Hunter Richards, who manages the site at http://www.softwareadvice.com/accounting/.  He recently published a study on accounting-related technical skills sought by employers.  I got my start as an accountant, so I found this study very interesting.  I started out my technical career doing international consolidations in spreadsheets.  At the time, I was using Supercalc 5 on a Unix workstation with a 640K DOS partition and 2 diskette drives.  The consolidation macros I wrote prompted me to change diskettes twice for the Balance Sheet and twice for the Income Statement.  Later, I was involved in both an Oracle Financials project and an SAP project at the same company.  I also got really good at Excel and ended up writing Excel books.

Technology certainly made it possible to get me from my accounting background to where I am today.  That being said, the time I spent in accounting was also extremely important as it gave me domain experience in a given field and I use that experience nearly every day. Lesson from it all?  If you an accountant and you learn a technology, you end up an Oracle ACE Director and blog on your experiences?  Well, you never know!

Here is a link to the blog entry.  Enjoy!

http://www.softwareadvice.com/articles/accounting/which-tech-skills-help-accountants-land-jobs/

Goodbye to Microsoft Analysis Services? Part 2

I got a follow up email from a friend who asked if I had seen a follow-on post to the original blog entry.  I had not, but here is is:

http://cwebbbi.wordpress.com/2010/11/14/pass-summit-day-2-the-aftermath/

It sounds a little like back pedaling from Microsoft but, if you read the comments, it doesn't look like the Microsoft crowd feels very comfortable.   My favorite comments posted there is this one.

"Well, it looks like Microsoft talked to you and and wants to “assure” everyone that all is well. Yeah, just like ProClarity, right? I think that a new technology direction is a good thing. But not when you push the timeline out many years to get a good stable solution. Long term, this is probably a good thing, but short term, msft will see a loss in market share….. When the client tools AND server techonology are in flux, how many customers will want to invest in that dream? Check back with msft in about 5 years and they will be winners."

And it isn't that Essbase customers have been immune as there was much uncertainty after the Oracle acquisition.  As I am someone who, more than anyone I know, is all in for Essbase, it is very comforting that storm has passed and Essbase is becoming a featured component deep in the heart of the Oracle Fusion strategy.

Goodbye to Microsoft Analysis Services?

In a series of recent Twitter tweets, fellow Oracle ACE Director Mark Rittman posted the following (pasted/edited) statement referring to a post by a Microsoft BI person at http://cwebbbi.wordpress.com/2010/11/11/pass-summit-day-2/:

"History repeating itself. MS doing to MSAS what Oracle did to Express Server .  So with MS abandoning AS and MDX, will Oracle (through Essbase) be the main sponsor and supporter of MDX going forward? Oh the irony."

 

Ironic?  Certainly..  I was actually there at the initial XML for Analysis Advisory Council meeting when Microsoft announced to the world that MDX would be the foundation of the XML/A standard.  It had already been discussed with Hyperion but, as it was 9/11, yes, that 9/11, I was the only "Hyperion" representative in the room as the entire Hyperion team was grounded by the terrorist attacks.  In the past 9 years, it  has now come full circle that Oracle, apparently, will be the standard-bearer for MDX.

Maybe it is my good fortune, then, that I haven't worked extensively with MDX, not that I didn't have the opportunity.  I remember exactly when I got my first copy of the precursor to Analysis Services.  I was at my first Arbor Dimensions conference in Santa Clara in the fall of 1996.  I remember being invited to a private Arbor executives party at the hotel and hearing the rumor about Microsoft buying an Israeli company that was an Arbor competitor.  Little did anyone at the party know that I had an alpha version of the software in my computer bag.  But, you know what?  I never installed it.  I am a loyal Essbase fan to the core and, despite the fact that the consulting firm I worked for at the time actually wrote most of the interface that Microsoft shipped, it just wasn't my cup of tea.

Over the years, a number of our competitors went off and re-wrote their interfaces to use XML/A so they could expose their products to other databases.  When they did that, they were forced to stop supporting some of the features that make Essbase magic.  You don't hear about most of them anymore.  Proclarity anyone?  Purchased by Microsoft and now on the trash heap.  Temtec?  Acquired by IBM where, apparently, software goes to die (remember Lotus 1-2-3?  Alphablox?); Clarity recently joined them at IBM which means, strategically, their days in the Essbase world are numbered.

If you read through the comments at the end of the blog post referenced above, it looks like Microsoft is positioning Excel PowerPivot to be the ultimate BI user interface..  That is great for spreadsheet jockeys, but will lose many, many users who don't want, or need, to look through the raw data for their summarizations.

After reading all of this, I am thankful for a few things.  I am thankful we:

• Are exclusively focused on Essbase and that Essbase is very well tied in with the Oracle Fusion strategy and will continue to grow.
• Invested heavily in Dodeca.
• Focused Dodeca on the end user.

ODTUG Election - Thank You!

Thank you to everyone who trusted me with their vote for the ODTUG Board of Directors.  I will continue to work hard for the whole Oracle development community and, especially, for my fellow Hyperion professionals.

When you have ideas you believe can help the Hyperion user community as a whole, please don't hesitate to contact me or fellow ODTUG Board-electee, and old friend, Cameron Lackpour.  Serving on the Board of Directors provides us direct contact with Oracle management on behalf of the user community and we are here to serve you.

Thank you again for the honor.

Off Hours: The Southeast Climbers Coalition Entry in the Pepsi Refresh Challenge

As many of you know, one of my other passions (besides Essbase) is rock climbing.  I have been a member of Southeast Climbers Coalition since it was formed.  Further, a number of other Applied OLAP employees are also climbers and members of the SCC.

This month, the SCC is competing in the Pepsi Refresh Challenge.  Winning this challenge would give the SCC the resources necessary to continue our mission of conservation and access and I am asking for your help.  First, watch the video we entered for the contest: http://www.refresheverything.com/seclimbers.  Then vote for our idea at this site.

On the video, be sure to watch for 2 shots of me and one of Applied OLAP installation guru Derek Hill.   Here is Derek at the 0:05 mark in the video at the first crux of Rave at Sandrock, AL.  Rave is the *only* 5.12 rated climb that I have climbed cleanly.  That rating is the very beginning of the expert level and it took me 6 months of work to get this climb:

I am at the far left of this picture, taken at the Little River Canyon near Ft. Payne, AL, at the 0:41 mark in the video:

I am also in the film of the Yellow Bluff ribbon cutting at 2:19.  Yellow Bluff is 16 miles from my house and I climb there often.  In fact, I was there yesterday.

If you can spare a few minutes, please visit the Pepsi Refresh Challenge site and vote.   You can vote once per day for the next 30 days; you can bet I will be!

Killer App? Show It Off at KScope 2011!

So you think you have a killer Essbase, HFM, Planning or OBIEE application that everyone would envy?  Please share with us and show it off at KScope 2011 in Long Beach.  Time is running out and the last day to submit an abstract is October 26.  You can find more information at http://www.kscope11.com/content/technical-content.

Oracle Open World Trip - Day 4

Sorry for the delay in posting but you know how time flies when you are having fun!

Sunday, September 19 consisted of two big events in my mind, the User Group forums and the Oracle ACE Dinner.    The best thing about the forums is hearing from Oracle Development on upcoming plans.   I jotted down a bunch of notes on the Essbase Roadmap.   Here are the notes with my comments in italics..

Essbase Roadmap - OOW 2010

General 

• Calc extensions within ASO - The effort seems to be focused on making allocations much easier in ASO in order to better integrate Essbase with Oracle E-Business Suite and other Oracle general ledger products.   This seems to me to be a great usage of Essbase in the Oracle Fusion strategy.  Further, it may lead to ASO being a viable option

• In-Memory Analytics - Not much was discussed on this point but my gut feel is this is a competitive response to both TM/1 and Qlikview.

Integration

• Migration of OFA/OSA - Great news for Oracle Financial Analyzer/Oracle Sales Analyzer users as they will now have the power of Essbase backing them.
  
• Studio/OBIEE integration - I don't recall exactly how this will work..  Sorry!
   
• Essbase web services layer - Yeah!  Finally, a real way to get to Essbase via web services (besides XML/A which originated as the way to get to Microsoft Analysis Services data..)
  
• Essbase ADF control - I have heard about this one now for 2 1/2 years..  I don't know what, exactly, to expect when we do finally see it.   It is a tool for Essbase/Java developers only, though.

Enterprise Scalability and Reliability

• Agent re-write - The Essbase Agent will be rewritten into Java to make it work natively within an application server and to provide better scalability.

• High availability/Continued focus on performance and scalability - Better clustering/rollover capability including (if I remember correctly) deployment within Oracle Real Application Clusters (RAC).

Improved Administration Experience

• Thin client Studio - Pure HTML administration application.

• Oracle Enterprise Manager Integration - Oracle Enterprise Manager is the main application for administering Oracle databases and will now add Essbase functionality.

My evening featured one of the highlights, for me, of Open World.   The annual Oracle ACE Dinner was held on a cruise boat out in San Francisco Bay.  I got to spend a bunch of time with some of the other Oracle ACE's including Edward Roske, Glenn Schwartzberg, Mike Riley and Jerry Ireland.   Unlike the ACE Dinner at Kaleidoscope, where I arrived just as dessert was finishing up, I actually made the boat launch this time and it was a great time.

Oracle Open World Trip - Day 3

Saturday, September 18 was basically an off-day (or a travel day) for most Open World attendees, but it was one of the best days of my life.

My wife and I stayed out in the East Bay with my best friend from college, Mark, who is the chief pilot for North Face.  Mark and I are both die-hard Michigan football fans, so we got to watch the Michigan game together.  We did not, however, expect the game to be such a thriller, so my adrenaline was running high during and after the game, but the best adrenaline rush was yet to come.

After the game we drove down to Livermore airport to meet with one of my customers, Tony Banta.  Tony is quite an interesting guy in that he was an air force test pilot, was in the astronaut corps, and later went on to be the director of worldwide manufacturing for Cisco Systems.  His current company, Calix, recently implemented Dodeca for production forecasting and Tony raves about the cost savings it has generated.   In fact, he gave us this quote:

"Dodeca can answer simply the questions that can't be answered simply"


He was literally talking about their implementation and said that line; I stopped him so I could write it down word-for-word.

Tony and I also get along because he is also a pilot.  I have a pretty cool plane, a Cessna 210, but Tony has some really cool planes.  Saturday afternoon I got to fly in one of them.  Here is a video my wife, Darlene, took when we flew over them after takeoff.  The best part of the video, frankly, is her scream and laugh..

In case you don't recognize the plane, it is a P-51 Mustang which is the airplane that saved Europe in WWII.  A picture of one of these airplanes has hung over my desk for many years now as a reminder to work hard so I can earn one, but this is the first time I have actually been in one.  My laptop machine name is Mustang.  This flight confirms it..  I want one!  I told my wife I will now have to double the hours I work (from 12/day) in order to earn one..

I will post more video from the ride after Open World.

Oracle Open World Trip - Days 1 and 2

I have blogger credentials for Oracle Open World 2010 and thus hope to do at least daily posts.

My trip started earlier in the week with the Oracle ACE Directors meeting at Oracle corporate headquarters. The ACE Directors meeting gives Oracle an opportunity to gather all of the ACE Directors together for a couple of reasons:

• To communicate upcoming announcements at Open World.
• Give this group of experts in the Oracle community an opportunity to talk directly with the appropriate Oracle executives to better understand Oracle's direction and to give feedback from the community directly to Oracle management.

Of course, this session was under NDA but there are a couple of things that are very apparent..  First, Oracle is committed to the future of Java (and to it's future as open source) and will make announcements at Open World to show their commitment.  

Second, the Hyperion applications look to me like they will be integrated into WebCenter; Hyperion Financial Close Manager already requires it if I am not mistaken.  WebCenter is essentially the Oracle equivalent of Microsoft Sharepoint and will add good value to the stack.

Relational Functionality of Dodeca 5.0 - How To, Part 3

In my previous post, I showed how to create the Excel template for a Dodeca relational view.  In this post, I will complete the series by creating the SQLExcel view that will join together the SQLPassthroughDataset from part 1 and the Excel template from part 2 to display and summarize Essbase performance data.

To create the new, select the New button from the View Editor to see the New View dialog.  I gave my view the ID and Name ‘Essbase Performance’ and chose the SQLExcel ViewType.

Once the view is created, the property set for the new view is displayed in the editor.  There are four mandatory properties for this view type.   The ExcelTemplateBinaryArtifact property is an object that wraps the Excel file that is used as the template for the view.  The ViewToolbarsConfiguration and the WindowsViewUIObjectTypeID specify the set of toolbars to use for the view and the type of display window.  The WorkbookView WindowsViewUI specifies the view to be rendered with a single workbook resized to the entire size of the view window.

The most important property, however, is the DataSetRanges property. The DataSetRanges property specifies the data to be retrieved along with the sorting, grouping and other display options.

The DataSet Range editor is used to specify one or more data set ranges that will be retrieved into the worksheet or workbook.  In turn, each DataSet may contain one more DataTables.  Each DataTable contains the data retrieved from a single SQL statement.
 

The DataTableRanges property is defined where each DataTable is placed on the worksheet.

This dialog dictates how and where the data is place into the worksheet.   The key property is the DataSheetRangeProperty which defines where the rows returned in the DataTable are placed into the worksheet.  Optionally, the data rows can exclude column headers and can insert cells into the range. This latter setting is very useful when you want to use Excel formulas in your group subtotal blocks as it is the key to having the spreadsheet engine automatically adjust the formulas.   The GroupStartCell property specifies where Dodeca will start building the group blocks, if any.  The groups are specified using the RowSortAndGroupByInfoList property.  The editor for the RowSortAndGroupByInfoList specifies, obviously, how the rows are sorted and grouped.

The RowSortAndGroupByInfo Editor not only specifies the sorting and grouping of the data, but also specifies the Excel Outlining configuration and the range name used to identify the block.  With these point and click configurations complete, here is what the completed view looks like.

The completed view shows metric information about the Essbase operations conducted through Dodeca.  Essbase administrators have, over the years, tried to get this type of information by parsing the Essbase logs.  Dodeca, however, provides a much simpler solution.

Relational Functionality of Dodeca 5.0 - How To, Part 2

In my previous post, I covered the first part of three parts on how to create and deploy a relational view in Dodeca.  In this post, I will cover creation of the Excel template.  For my example, I am going to write a simple report from the Dodeca Essbase Performance logging table.

Dodeca exposes a tremendous amount of Excel functionality to the relational view type.  One of the most common things that most customers leverage is the ability to use Excel formulas in the subtotal blocks.  One of the challenges most developers have when adding formulas, such as the SUM() function 'on-the-fly', is determining the range address for the formula.  We made it very easy to use formulas as we insert rows to contain the actual data.  This approach leverages the built-in spreadsheet functionality which adjusts the range addresses automatically.

Another useful Excel functionality that is available in Dodeca is grouping.  Grouping provides users a way to tie a range of cells together so they can be easily collapsed or expanded.  It supports the ability to automatically sort and group data based on column values and expressions.  Additionally, up to 8 levels of grouping is supported and each level can be configured to initially display expanded or collapsed.

The requirements for the Excel template are simple.  First, there must be a range defined for the data.  The range must be exactly the number of columns returned by the SelectSQL of the SQLPassthroughDataSet object.  The range must also be exactly 2 rows tall or, if you intend to show the column headers returned by the relational query, exactly 3 rows tall.   This range can optionally contain formulas which will automatically be both preserved during the retrieve process and copied to each data row inserted into the worksheet.

Optionally, you can also include subtotal blocks.  The subtotal block typically includes the entire data range and may include one or more rows, and or one or more columns, before and/or after the data range.  This subtotal block typically contains formulas and labels to summarize the detail rows contained within the data range.

The range names should be defined with sheet level scope.  Sheet level names are mandatory when the relational view is cascaded.  Here is a screen shot showing the data range name definition.

DataRange definition in the Excel template

For my example, I am going to insert subtotal blocks to summarize the operation and break on the server / application / database.  The operation block will contain a row for the average, minimum and maximum values for each operation.   Here is my completed spreadsheet template.

Completed spreadsheet with OperationBlock range selected

In the final post of this series, I will setup the SQLExcel view that will join together the SQLPassthroughDataset from part 1 and the Excel template from part 2 to display and summarize Essbase performance data.

Relational Functionality of Dodeca 5.0 - How To, Part 1

In my last post about the relational functionality of Dodeca, I talked about why the relational integration is necessary and discussed some of the features.  In this post, I am going to walk through a simple how-to to show how easy it is to setup a relational view in Dodeca.  For my example, I am going to write a simple report from the new Dodeca Essbase Performance logging table that is part of the application.  In practice, however, you can use any relational data source that you can connect to via JDBC from our server.

There are four steps I need to follow to set up this relational view:

1.  Determine the SQL data you want to display, and then set up the corresponding SQL Connection and SQL Passthrough DataSet objects in Dodeca.
2.  Design a simple Excel template to display the data.
3.  Set up the view definition and any related objects.
4.  Deploy it on a View Hierarchy.

In this post, I will cover step 1 of the process.  For my relational view/report, I am going to use a table in the Dodeca metadata database that tracks Essbase performance.  Here is the SQL Query I created in SQL Server to get the data I want to display.

(click to enlarge)

To get access to the data in Dodeca, first I must create a SQL Connection object.  The SQL Connection object defines all of the parameters necessary to connect to the target relational database from the server and, as the server connects to the database using Java JDBC technology, the parameters are specified in JDBC format.  Below is a screenshot of the parameters in the Dodeca SQL Connection editor.  Note that the Username and Password are masked in the editor and are stored using industry-standard TripleDES encryption.

(click to enlarge)

The next step is to define a SQL Passthrough DataSet object in, of course, the SQL Passthrough DataSet editor.  This editor allows the Dodeca administrator to create new SQL DataSet objects.  Each SQL Passthrough DataSet  object may contain multiple Query objects which will be executed against the database in a single trip to the server.  In my example, I am going to create a single Query object the editor.  The SQL may optionally contain tokens which are string substitutions that are used a placeholders for user selections; in my case I did not choose to tokenize my SQL.  Further, if I were planning to allow users to write back to the relational tables, I could also provide insert, update and delete statements.  Here is a screenshot showing the SQL Passthrough DataSet definition including the SelectSQL property of the Query object.

(click to enlarge)

Once I have entered the SelectSQL and the SQLConnection property values, I can close the Query Editor and test it using, of course, the 'Test Data Set' button.  Here is what my test looks like:

(click to enlarge)

I have covered quite a bit in this post, so I will stop here for now.  In the next blog post, I will cover designing the Excel template for the Dodeca view/report.

Essbase Windows 7 Installation Issue - Resolution

One of my sons worked with a partner last week who was having problems installing 64-bit Essbase on 64-bit Windows 7.  It took a little bit to get the issues ironed out, but here is a summary of what happened.  Hopefully this post will prevent similar problems for you.

On the initial installation, the partner tried to install Oracle EPM System without using UAC (a/k/a right click, Run as Administrator) to run the installtool.bat.  This caused the installation to be incomplete and the subsequent configuration thus failed.

To try to fix the installation, the first thing that was attempted was an uninstall.   The uninstallation appears to complete successfully, but on the subsequent reinstall, not all of the components, specifically the http server, were not enabled for selection.  Of course, this did not look right, so upon further investigation, it was found that the uninstaller did not remove the dirty folder C:\Oracle\Middleware.   We manually removed the C:\Oracle\Middleware directory, which was improperly installed in the first place, and then installation/configuration completed without a problem.

Relational Functionality of Dodeca 5.0

Dodeca 5.0 shipped about a month ago, so I will expand on some of the new features in a few blog posts. I will start with the most expansive new capability which is the comprehensive new relational functionality.

Back in the old days with Essbase, relational technology was almost considered a dirty word. In fact, the Arbor product manager who I worked for on my first Essbase project, which was demoware for Arbor, told me I couldn't use a relational database on the project and, in fact, never to mention the word relational in their office! Of course, they were competing heavily against relational systems at the time so I can understand his position. Still, he didn't have a good answer for me when I asked where he would recommend storing addresses in Essbase. Of course, at the time it was not uncommon to see dummy Essbase outlines with this type of information but it was probably one of the worst cases of 'when all you have is a hammer, everything looks like a nail' that I have ever seen. These days, with Oracle in the picture, there is much more opportunity to use Essbase for what it does best, storing and aggregating dimensional data, and to use relational technology for what it does best which is storing and retrieving transactional data for running businesses.

As the technology has evolved over the years, there have been a number of ways to bring together the power of Essbase with the details stored in relational databases. The first major effort to accomplish this task was Essbase Integration Server ("EIS"). By my rough estimate, EIS was used by less than 10% of Essbase customers to provide drill-through. Other Hyperion/Oracle products such as Financial Data Quality Management ("FDQM"), Essbase Studio and the new ERPi provide drill-through capability as well but have some limitation, version requirements or require a number of Oracle products, including Oracle eBusiness Suite in the case of ERPi, to work. While I am thrilled to see Oracle innovate in these ways, my thought is that is should be easier, faster and less expensive to provide drill-through to customers. This is what led us to invest so much time in relational technology for Dodeca.

The idea for drill-through in Dodeca is that you can target any relational database directly from any Essbase database. With this design goal in mind, we set out to make it reality and what we ended up with exceeds even my expectations. Some of the features we implemented include:

• One or more queries on a worksheet
• Automatic grouping based on column values or expressions
• Use any Excel function in subtotal header/footer blocks
• Write back data to the relational database (optional)
• Relational query ranges on the same sheet as Essbase data ranges
• Cascade based on relational values

Here is a screenshot of a relational view that shows a view with 2 different worksheets containing the results of 2 different queries. The first tab has data grouped by product and the second tab is grouped by salesperson. The grouping automatically included the Excel grouping controls which were configured to be collapsed; I expanded open the 'Classic Vest, L' group to display the details. The selected subtotal cell is an Excel SUM() function that was placed in the template by the view developer and whose range was automatically expanded when the data rows were inserted into the view. The performance, though, is the really amazing thing. This view, which runs a query for each sheet that together return over 120,000 rows, generated and displayed in 25 seconds. When it displays in Dodeca, the data and formatting is in the native Excel xls or xlsx format, so 'exporting' to Excel means that Dodeca saves the Excel file it is displaying as a temp file, then opens it in Excel.

Dodeca Relational View (click to enlarge)

Though this screenshot does not show an Essbase drill-through, the Intelligent Navigation feature of Dodeca makes drill-through almost trivial and I will show an example in an upcoming blog post. I also plan a post or two to talk about the technical aspects of how the relational functionality works.

Kaleidoscope 2010 - Java API Session Slides / Source Posted

As promised, I have posted the slides and source code examples from my Introduction to Development with the Essbase Java API session at Kaleidoscope 2010.  There is a readme file that gives you the basics of how to run them from the command line.  If you are serious about learning the Essbase Java API, I strongly recommend you download one of the free Java IDE's available.  My favorites are:

• IntelliJ
• Oracle JDeveloper
• Eclipse

You can download the files from the Blog-Content section of the Applied OLAP website at http://www.appliedolap.com/downloads.

Kaleidoscope 2010 Review

Kaleidoscope 2010 wrapped up a week ago Thursday and, not surprisingly, I haven’t had time to write about it until now.  Here is a summary of some of the highlights.

Community Service

The Community Service project this year provided labor to the Ronald H Brown Middle School in northeast DC.  The school is was named for a US Secretary of Commerce who was killed in a plane crash in 1996 in Croatia.  The ODTUG crew worked on refurbishing a playground, did landscaping and sorted books in the library.   Joe Aultman from AutoTrader, Jeff Sims from Tethsys and I worked on the basketball courts which literally had no lines or layout when we started.  Cameron Lackpour and my son Billy worked in the library and Whitney, my daughter, worked on the hopscotch and four-square areas with an EPM consultant I had never met before, Jessica Cordova.  Here is a picture (thanks to Larissa Stamey from ODTUG and Wayne Van Sluys for the picture).

For what it is worth, I placed the basketball in the center of the circle for this picture (and laid out the lines and painted much of the blue court.  I am standing on left end just in front of my daughter, Whitney.  My son, Billy, who labored for weeks on the Windows 7 installation blog, is standing just in front of the guy holding the letter 'B'.

Sunday Symposium

The Sunday EPM Symposium is one of the unique things about Kaleidoscope as it is the only forum where customers and consultants can meet the Hyperion development teams face-to-face.  There was some great exchanges of information but, of course, as Oracle was talking about futures, everything presented came with heavy disclaimers.

During the Symposium last year, the product manager from Smart View held an impromptu ‘tell me why you like the classic add-in better than Smart View’ session that lasted nearly his entire allotted time.  From his presentation this year, it is very apparent that he listened to the feedback.  The Smart View team is working hard on classic add-in parity and are they are starting to get Smart View much closer to the point where it will gain more widespread acceptance.

Keynote / General Session

The General Session had a huge announcement for us Hyperion people as one of our own, Edward Roske from interRel, is the Conference Chair for Kaleidoscope 2011 in Long Beach.  Edward has worked very hard on the previous conferences and has thus earned his way to a very tough job.  For us Hyperion people, however, having a Hyperion person as the Conference Chair shows the importance of Hyperion in the ODTUG world.   I expect that next years conference will have even more Hyperion content and will be even more of a can’t miss event.

The keynote was by Lee Rainie, Director of the Pew Research Center's Internet & American Life Project.  Lee discussed some very interesting facts gathered from their research.  One interesting trend he discussed is ‘lifelogging’ where people document everything they do on-line.  I instantly recognized my daughter follows this tendency and, if fact, we always kid her about her natural ability to take pictures of herself with her iPhone for her Facebook page.  I encourage you to read the findings at http://www.pewinternet.org/Presentations/2010/Jun/The-Future-of-the-Internet--National-Geographic.aspx. This presentation features the same content contained in the keynote

Sessions
I normally don’t get the opportunity to go to many sessions other than my own and this year was no exception.  I did get to attend the Smart View sessions on Thursday and they were all very good and thought provoking.

I gave three presentations in DC as well.  The first session was a vendor session which highlighted the features and benefits of Dodeca.  It was well attended and we gave away a bunch of Dodeca T-Shirts at the end.

The second session was a new presentation intended to educate Oracle database developers about Essbase and where it fits into the enterprise.  This was a fun topic and I think it is becoming more relevant with each passing day as Essbase gets integrated deeper into the Oracle stack.

The last session was a fill-in session on the Java API.  My friend Jason Jones was scheduled to do the session but, at the last minute, was unable to attend.  This session was the most fun for me as I really dig into Essbase at the API level and foster new understanding of how Essbase works ‘under the covers’ to even experienced Essbase people.   A number of people of have asked for the sample code from this session; I will make it available on our website soon.

Overall / The Future

This Kaleidoscope was the best one yet but I still expect it to be bigger and better next year, but there is still a lot of work to do.  Many people, including me, still long for the days of the Hyperion Solutions conference.  The Solutions conference had the advantage of being run by the vendor, so the marketing department could devote many dollars to make sure it was ‘the place to be’ for Hyperion people.

In the Oracle world, the comparable conference is Oracle Open World but, as Hyperion is a small percentage of Oracle as a whole, Open World will never have the Hyperion focus that Solutions had or that Kaleidoscope has today.  Kaleidoscope, on the other hand, is run by the Oracle Development Tools User Group which means that it is completely controlled, and funded, by the users.  That means you can make difference on the direction of this conference.  To help make Kaleidoscope even better, I plan to run for the ODTUG Board of Directors in order to provide Hyperion users a voice on the governing body.  Elections are coming up in the fall and paid members are eligible to vote.  I encourage everyone to join, participate and please vote for me for the Board of Directors!

Essbase Outline Performance Testing - Do It Yourself Kit

I decided I would post my code for the Essbase Outline Performance Testing for a couple of reasons:

• So you can try it on your own outlines; and
• To make more Essbase Java API examples available online.

To use the code below, follow these steps:

• Make sure you have a Java JDK installed on your system and referenced with the JAVA_HOME environment variable.
• Make sure you have a backup of your Essbase outline (just in case).
• Create two text files; name on file 'EssOutlineOpenTimingsTest.cmd' and the other 'EssOutlineOpenTimingsTest.java'.
• Copy the following to code to the EssOutlineOpenTimingsTest.cmd (and I apologize in advance for the small size of the code; I had to shrink it for the blogger software to properly display all of the code):

@echo off

rem Change the directory below to point to your jar file 
set CLASSPATH=%CLASSPATH% ;C:\Hyperion\products\Essbase\aps\lib\ess_japi.jar;
echo Compiling ...

"%JAVA_HOME%\bin\javac" *.java -d .

echo Running test class ...
echo . 
"%JAVA_HOME%\bin\java" -ms128m -mx512m EssOutlineOpenTimingsTest

echo . 
echo . 
echo Done ... 
pause

• Copy the following code to the EssOutlineOpenTimingsTest.java file:

import com.essbase.api.base.*;
import com.essbase.api.session.*;
import com.essbase.api.datasource.*;
import com.essbase.api.domain.*;
import com.essbase.api.metadata.*;
import java.text.DecimalFormat;

public class EssOutlineOpenTimingsTest {
    // TODO: CHANGE THE VARIABLES BELOW TO USE YOUR INFORMATION
    private static String _user = "timt";
    private static String _password = "essbase";
    private static String _server = "mustang";
    private static String _url = "http://mustang:13080/aps/JAPI";

    public static void main(String[] args) {
        IEssbase ess = null;
        IEssOlapServer server = null;

        try {
            // create api instance
            ess = IEssbase.Home.create(IEssbase.JAPI_VERSION);

            // signon to the domain
            IEssDomain dom = 
                ess.signOn (_user, _password, false, null, _url);
            
            // connect to the server
            server = (IEssOlapServer)dom.getOlapServer(_server);
            server.connect();
 
            // print the column headers
            System.out.println(
        "Try #|Application|Cubename|Milliseconds|Filesize (Mb)|Members"
            );

            // TODO: CHANGE THE NUMBER OF LOOPS BELOW AS DESIRED
            // open each outline 3 times in a loop
            for (int i = 1; i <= 3; i++) {
                // TODO: CHANGE THE APPLICATIONS/DATABASES BELOW, 
                // AND ADD/DELETE OPENOUTLINE CALLS, AS DESIRED
                openOutline(i, server.getApplication("Sample")
                    .getCube("Basic"));
                openOutline(i, server.getApplication("ASOSamp")
                    .getCube("Sample"));
                openOutline(i, server.getApplication("Big1")
                    .getCube("Big1"));
                openOutline(i, server.getApplication("BigASO")
                    .getCube("BigASO"));
                openOutline(i, server.getApplication("BigASO_C")
                    .getCube("BigASO_C"));
                openOutline(i, server.getApplication("zzz")
                    .getCube("zzz"));
                openOutline(i, server.getApplication("zzz_C")
                    .getCube("zzz_C"));
            }
        } catch (EssException e) {
            System.out.println("Error: " + e.getMessage());
        } finally {
            try {
                if (server != null && server.isConnected() == true)
                    server.disconnect();
            } catch (EssException e) {
                System.out.println("Error: " + e.getMessage());
            }

            try {
                if (ess != null && ess.isSignedOn() == true)
                    ess.signOff();
            } catch (EssException e) {
                System.out.println("Error: " + e.getMessage());
            }
        }
    }

    static void openOutline(int tryNumber, IEssCube cube) 
        throws EssException {
        
        IEssCubeOutline outline = null;

        try {
            // stop/start the cubes to get a fair timing
            try {
                cube.getApplication().stop();
            } catch (EssException e) {
                // fails if not started, so just ignore
            }

            // start cube
            cube.start();

            // let the machine catch it's breath
            try {
                Thread.sleep(3000);
            } catch(InterruptedException e) {

            }

            // get the start time
            long startMillis = System.currentTimeMillis();

            // open the outline
            outline = cube.openOutline();

            // compute the time to open
            long totalMillis = System.currentTimeMillis() - startMillis;

            // declare variables for the output string
            int memberCount = 0;
            String filesize = "";

            if (tryNumber == 1) {
                // get the dimensions
                IEssIterator dims = outline.getDimensions();

                // loop the dimensions
                for (int i = 0; i < dims.getCount(); i++) {
                    // get the dimension
                    IEssDimension dim = (IEssDimension)dims.getAt(i);

                   // count the members
                    memberCount += dim.getDeclaredSize();
                }

                // get the size of the outline file
                byte[] bytes = cube.copyOlapFileObjectFromServer(
                    IEssOlapFileObject.TYPE_OUTLINE, 
                    cube.getName(),
                    false);

                // count the bytes
                filesize = new DecimalFormat("0.0")
                    .format(bytes.length / (1024 * 1024));
            }

            // print the result
            System.out.println(tryNumber + "|" + 
                               cube.getApplication().getName() +
                               "|" + cube.getName() + "|" + 
                               totalMillis + "|" + filesize + "|" + 
                               memberCount);
        } finally {
            // cleanup
            if (outline != null && outline.isOpen())
                outline.close();
        }
    }
}

• Modify the parameters in the Java code were noted.  These parameters will set the code to user your server, username, password and databases.
• Save both files, then double click the cmd file to run.

Remember to backup your Essbase outline file before you start as, by using this code, you acknowledge that you are responsible for the result and agree to hold me and my company harmless for any use of the code, in whole or in part.

Let me know your results!

Essbase Outline Performance Testing - Exciting Update

I had a number of comments and emails following my post the other day about the performance of opening an outline.  Many of them shared their belief that ASO databases did open more slowly, and why, and I had a couple of suggestions:

• Try converting the Big1.Big1 outline to ASO using the wizard, then compare performance; and
• Try compacting the outlines using the free Oracle ESSCMDQ utility that Oracle QA uses for testing.

I did that and here is the result.

Updated Analysis (click image to view in a larger window)

As you can see, converting Big1 to ASO caused the outline to grow by over 300% and the time to open to grow over 600%.  Subsequent compacting of BigASO did not cause that much of a decrease in size or time to open.

There was, however, a big impact on the zzz outline that was generated by Essbase Studio.  The filesize was reduced by 91% and the time to fell by an average of 75% (or nearly 45 seconds).

The customer who provided the zzz outline compacted their outline and have reported it opens much faster in their environment.  As a result, it looks like compacting may have an impact on outline open operation performance with more significant results on Essbase Studio generated outlines.

Thanks to my friends Glenn Schwartzberg and Edward Roske for pointing out the availability of ESSCMDQ which is available at http://www.oracle.com/technology/products/bi/esscmdq_sampleapps.html.  I would heavily recommend you backup your outlines before running this utility as it is not available for every minor version of Essbase and I had it crash on my laptop a couple of times.   By the way, on my 11.1.1.0 laptop, I had to use ESSCMDQ 9.3.1 as ESSCMDQ 11.1.1.1 threw an exception.

Finally, I am thinking of doing a poll on the blog about results you see when compacting your outlines.  Would you like to participate in that poll?   Also, would you be interested in the Java code I used to run the testing?  If so, I will do another blog post to explain how it works and will make it available from our website.

New Functionality in Dodeca 5.0

We have been hard at work on Dodeca 5.0 for nearly a year now, and it is nearly ready to ship. Some of our customers have heard about the significant new functionality coming in this release and some have used it in our beta. To summarize, there is a lot of excitement about this release. This blog post will summarize the changes; later blog posts may delve deeper into individual items.

Relational Functionality

Probably the biggest feature coming in Dodeca 5 is new and expansive relational capabilities. We have a number of customers who have used the SQLPassthroughDataset capabilities of Dodeca 4.1, so we listened to their feedback and desires to add to these capabilities. This new functionality includes a new Dodeca view type, the SQLExcel view type, which allows administrators to configure one or more SQL retrieval ranges, from one or more SQL databases, on each worksheet in a workbook template. The configuration supports automated, data-based Excel grouping with any Excel formula allowed in the group summary blocks. Further, the administrator can configure the ranges to allow direct writeback to the relational database. The same relational capabilities are also fully supported in the Essbase Excel view type; you can have Essbase and relational data on the same worksheet and can even cascade sheets with both Essbase and relational data.

Here are a couple of sample screenshots of the relational capability.

Relational View with multiple levels of grouping (click to expand)

Relational View with Excel-based grouping subtotals (click to expand)

We expect the relational capability will be used heavily by Essbase customers who want to implement drill-through to relational sources without the need to go through EIS or Essbase Studio.  Further, we believe the Dodeca relational capability may be some of the most robust spreadsheet relational technology available anywhere, so we expect this functionality to be a popular even among non-Essbase customers.

Essbase Clustering

We have implemented robust Essbase clustering in our Dodeca-Essbase services. The clustering functionality reads the APS cluster definitions and manages connections to the appropriate clustered databases. Clustering is supported on both stateful and stateless connections. The stateful connection clustering connects to a given server/application/database and stays connected to the same database until that database becomes unavailable. In that case, the connection rolls to the next available cube in the cluster. Based on a customer request, stateful clustered connections can be configured to optionally throw an Exception if the 'originally' connected database becomes unavailable so they can guarantee a number of sequential operations occur against the same database.

Essbase Selector Improvements

We have made a couple of important improvements to Essbase selectors. First, we optimized the search algorithm for our find dialog and made it up to 90% faster. This optimization really isn't that exciting unless you have really large outlines and have configured your selectors to show limited numbers of members such as would be the case if you were using metaread filter security in Essbase (which Dodeca selectors honor automatically).

The second improvement is that we now have exposed associated attributes in our MemberTips. Here is a screenshot that shows a MemberTip with attributes displayed.

MemberTips displaying Attributes

Request/Response Logging

Ever wonder how Dodeca is communicating via web-services with our server? This new functionality will tell you. The request/response logging feature traps all xml inbound requests, and all xml outbound responses, to xml files on the server for the Dodeca service, the Dodeca-Essbase service, or both. The logging can be enabled or disabled by the administrator for the entire application and/or for individual Essbase connection definitions plus it can be enabled 'on demand' for an individual user. 

Another use for these xml files is in stress testing your system.  We have created a stressor that will resend the captured requests to the server using a specified number of threads.  We use this stressor internally for stress testing our servers and, although we are not yet packaging the stressor with Dodeca, we will make it available to customers upon request.

Essbase Performance Logging

The Essbase performance logging tracks the performance and associated metrics, such as the Essbase server, application and database, username, number of threads active on the server during the transaction, size of the grid before and after the call, number of filled cells in the grid before and after the call and number of members returned, if any, along with the number of milliseconds it takes to complete. This data is stored in a relational database for later analysis. Like the request/response logging feature discussed above, performance logging can be enabled or disabled for individual Essbase connection definitions plus it can be enabled 'on demand' for an individual user.  Ever want to know who is hitting Essbase and how much data they are pulling back?  Here is your way to find out.

Installation Improvements

We have made a number of installation improvements including the elimination of the requirement for a Java JDK and the .NET SDK on the machine used to prepare the Dodeca service Web Archive ("war") file for deployment. We have a utility that does this preparation and it previously used the JDK for packaging the war file and the .NET SDK for digitally signing our deployment. The utility now supports these capabilities without the need for the SDKs and yes, we are creating a Java war file in .NET code!

We also made some minor adjustments to our core Framework code that will allow customers to run multiple Dodeca versions simultaneously and to fully support SSL for all transactions. We are introducing a new utility that helps customers determine the appropriate connection string for the Dodeca data store.

Cultural Settings

We have done some infrastructure work to assure that Dodeca is Locale aware. The work covers all aspects of Dodeca including the Essbase view types, SQL view types and workbook scripts. 

Summary
 
There are dozens of other minor new features that we added to the software such as improvements to the paste options available where, for example, you can configure paste special, values to be the default paste action or improvements to the documentation which is now part of our automated build process.  I plan to blog in more detail on some of the new features in the near future. Meanwhile, we expect to release Dodeca 5.0 on or around June 11.

Essbase Outline Performance Testing

I posted a blog entry last week about getting member information in the Essbase API and made a comment about how opening an Essbase outline can be slow.  We have seen anecdotal evidence over the years that outlines created in EIS/Essbase Studio seem to open more slowly which, incidentally, led us to write metadata caching into our Dodeca-Essbase service years ago.  If I remember correctly, the Java API developers told me back then that opening the outline copies the outline file to the client machine, so some of the performance problem may be due to the file size that must be passed across the network; this is the same with the C and VB APIs.  Based on these things, I decided to do some testing to try and get to the bottom of it (and perhaps help our friends at Oracle understand how the APIs are used out here 'in the wild' so they can better optimize the operations).

For my test, I wrote a Java method to open an outline and output the time it takes to complete the action.  I then wrote code to call the method 5 times for each of four cubes/databases to make sure I was getting consistent timings.  The testing was done completely on my laptop with the Java code, Essbase 11.1.1.0 and APS 11.1.1.0 all running on the same machine.  I picked these four cubes for different reasons.  The four cubes are:

• Sample.Basic.  I picked this cube as everyone has it and it can provide a comparison baseline.  The filesize for the test was 9.1 Mb.

• ASOSamp.Sample.  I picked this cube as it gave me an ASO comparison baseline with 17,711 members in 14 dimensions.  The filesize for the test was 5.2 Mb.

• Big1.Big1.  I picked this (renamed) customer cube as it is a very large BSO outline, built with build rules, with 337,272 members in 6 dimensions including 45,985 Accounts and 331,226 entities.  The filesize for the test was 64.1 Mb.

• zzz.zzz.  I picked this (renamed) customer cube as it is an average ASO cube built by Essbase Studio with 55,284 members in 11 dimensions.  The filesize for the test was 133 Mb.

Here are the results of my test summarized in a pivot table; click on the graphic to view the entire sheet.

I found the zzz.zzz outline was, by far, the slowest to open.  When compared to the ASOSamp baseline outline, it took approximately 9 times longer to open zzz.zzz despite the fact that it has only 3 times more members.  Big1.Big1, which has 6 times more members than zzz.zzz, opened in just over 50% of the time.  Based on my tests, it appears the filesize is a major factor in the performance and that the outline built with Essbase Studio is significantly larger than the outline built with build rules.

So, how does outline performance affect you?  Other than the obvious wait times in EAS, there may be some things that are not as obvious. The two most glaring examples are the inability to get all of the available information about associated attributes and the inability to get member comments.  In any case, wouldn't it be great if all member queries were equal and outlines opened really fast?

How To Trash An Essbase Database (and how to fix it)

I was stress testing some new Essbase clustering functionality that we put into Dodeca this weekend and decided to let it run on my laptop for a couple of hours while I relaxed at home. However, I had forgotten that the new battery on my laptop seems to act strangely and not alert my system that it is running out of juice and, as a result, it just immediately turns off.. This is definitely *not* what you want to do to Essbase, especially when you are changing the configuration.

In my testing, I wrote a little C# routine that retrieved data into a grid every 1/2 second. The application also had a background thread that was running a maxl script every 3 seconds to turn off one active database in my cluster and start the other database in the cluster. I doubt anyone would need to swap clusters this fast, but I wanted to make sure it worked, so I wrote the test routine.

It just so happened that my machine quit in the middle of one of these maxl runs. I didn't think anything of it and put my laptop away until this morning when I noticed my Essbase service wasn't running. When I tried to start it, it immediately shut down. I started Essbase in a command window and it looked like it was working, but it didn't behave properly when I tried to connect to it.

Apparently, several files got trashed when the laptop ran out of power. To get everything back to normal, I basically had to create a new application and copy the files from the old application over to the new application. Not very pretty but it worked. Thank goodness it was only my laptop system and not a production system.

Lesson to be learned.. Make sure your production systems have good UPS's or other backup power behind them.

When Is A Member Not A Member?

When is a member not a member? Sometimes in the Essbase Java API, that's when. Depending on how you obtained your IEssMember object instance, it may only partially describe the member. What I mean by that is that is that some properties are not available, and are thus either blank/null or throw an Exception when you try to access them. This is why it can be frustrating to work with the Essbase API. For what it is worth, this problem is not limited to the Essbase Java API but is also a present in the C and VB APIs as well. In this blog, I will focus on the Java API but for those of you using the C and VB APIs, see if you can spot the corresponding API calls in those languages.

To start, let's look at 3 common ways to get an IEssMember object in the Essbase Java API:

• IEssCube.getMember()
• IEssMemberSelection.executeQuery()
• IEssCubeOutline.findMember()

Each of these methods have their advantages and their disadvantages. The IEssCube.getMember() method is fast but doesn't return all properties. IEssMemberSelection.queryMembers() allows you to find multiple members based on relationships or wildcard searches but, again, doesn't return all of the properties; it also will cause hanging Essbase connections if you don't properly close the IEssMemberSelection object. IEssCubeOutline.findMember() gives you basically all of the information but requires that you open the outline. Opening the outline can be terribly slow if you have a large outline or, in our experience, if the outline is built using EIS or Essbase Studio. The fact that there are multiple ways to get the member information can certainly be frustrating.

One example of frustration occurred this spring when we decided to add attribute information to the MemberTips we optionally display in a Dodeca member selector. We uncommented our server code that calls the IEssMember.getAssociatedAttributes() method and were not surprised that this method threw an Exception. We have gone to great lengths to make Dodeca performant and, for the most part, don't open the outline. As a test, however, we decided to open the outline only when the query was returning this information. As soon as we tested this on one of our larger customer outlines with 385,000 members in the Entities dimension, we knew this approach would be much too slow to put into production. Upon further testing, we found that we could get 2 of 4 attribute-related properties without opening the outline and that is what we have now implemented.

Dodeca MemberTips

This summer, I have my lucky intern working on a research project with Essbase member information with the Java API. The first thing I had him do was to write some Java code that used reflection to attempt to find which properties were available to each method of obtaining an IEssMember object. Here is a summary of that work using 11.1.1.3 run against Sample Basic using 100-10 as the target member. An 'x' in the cell indicates that the property value appears to have been returned properly; a blank indicates either an error occurred or, perhaps, that the method didn't work due to member 100-10 not being a dimension root member, etc.

MethodName	Cube	Member Selection	Outline
getAggregationLevelUsage	x	x	x
getAssociatedAttributes			x
getAttributeAssocLevel	x	x	x
getAttributeMemberDataType	x	x	x
getAttributeValue	x	x	x
getChildCount		x	x
getChildMembers			x
getConsolidationType	x	x	x
getCountChildMembers	x	x	x
getCountOfDupMemberNameInDim*
getCurrencyCategoryOrName		x	x
getCurrencyConversionType		x	x
getDescription	x	x	x
getDimensionCategory		x	x
getDimensionName	x	x	x
getDimensionNumber	x	x	x
getDimensionSolveOrder	x	x	x
getDimensionStorageCategory		x	x
getDimensionStorageType		x	x
getFirstChildMemberName	x	x	x
getFormatString	x	x
getFormula	x	x	x
getGenerationNumber	x	x	x
getHierarchyType*		x	x
getLastFormula	x	x	x
getLevelNumber	x	x	x
getLinkedAttributeAttachLevel	x	x	x
getMemberComment			x
getMemberId	x	x	x
getMemberNumber	x	x	x
getMemberType*
getName	x	x	x
getNextSiblingMemberName	x	x	x
getOriginalMemberName	x	x	x
getParentMemberName	x	x	x
getPreviousSiblingMemberName	x	x	x
getPropertyDataTypes	x	x	x
getPropertyModes	x	x	x
getPropertyNames	x	x	x
getRelatedMemberNames			x
getRoot	x	x	x
getShareOption		x	x
getSmartList*
getSolveOrder	x	x	x
getTimeBalanceOption		x	x
getTimeBalanceSkipOption		x	x
getUDAs		x	x
getUniqueName		x	x
isAttributesAssociated	x	x	x
isClientCachingEnabled	x	x	x
isDimensionRootMember	x	x	x
isExpenseMember		x	x
isFlowType	x	x	x
isIndependentDim	x	x	x
isInitialized	x	x	x
isMemberNameUniqueWithinDim*
isNameUnique		x	x
isRelationalDescendantPresent	x	x	x
isRelationalPartitionEnabled	x	x	x
isTwoPassCalculationMember		x	x

* Indicated results may be due to the member tested, 100-10, instead of the availability of the information exposed by the IEssMember.

As it appears that basically every property value is available when the outline is open, my intern is now working on prototyping a faster methodology for getting member information that I engineered. I will discuss this methodology once we have it implemented and shipping in Dodeca.

Speaking of Dodeca, we have been hard at work on Dodeca 5.0 and are now at the beta 2 milestone (which explains the sparseness of my blogging). I plan to start blogging on the numerous new features of Dodeca 5.0, along with continuing the Dodeca architecture overview I started in the fall, within the next few days.