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.