Monday, July 26, 2010

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.

Friday, July 16, 2010

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.

Monday, July 12, 2010

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!