Dodeca 5.3.0.2646 Available for Download!

We have been busy working on some new functionality for our 5.3.0 release and have finally got it out the door!  This release features a number of new capabilities requested by customers along with other product improvements.  Our release notes contain an exhaustive list of the changes, but here are some of the highlights:

Support for PDF

We have added native capability to write data in a grid or Excel based Dodeca view directly to a PDF file.  The output PDF file is PDFA-1b compliant.  Additionally, the generated PDF file can be opened locally in a Dodeca WebBrowserView or externally in the default PDF viewer.   We have also added programmatic support for PDF by way of the SaveWorkbookAsPdf method (or "action") and the BeforeSaveAsPdf and AfterSaveAsPdf event links.

Dodeca SQL Excel View Rendered as PDF (click to enlarge)

Excel AddIn Mode

Dodeca was created to target the need for a bulletproof spreadsheet environment for planning, analysis and reporting on Essbase and relational data sources, but we have had requests to make Dodeca work even more like the classic Excel add-in.  We are happy to announce this new release features our new Excel AddIn mode.  This mode, which is implemented as an optional configuration setting on our Adhoc Essbase view type, allows an end user much more flexibility to choose their own Essbase connection, load their own Essbase-aware Excel workbooks from their hard drive and retrieve/drill just like they are using the classic addin (except without the bugs!)




Copy Comments

We have had many customers ask if they could copy their analytic commentary from one set of datapoints to another. For example, one customer wanted to copy their comments relating to January Forecast for their 25 assigned markets to the February Forecast. In response, we have added server-side support for copying comments. Additionally, we have added the CopyComments workbook script method/action to facilitate end users initiating the copy comment process.



CopyComments Workbook Script Method (click to enlarge)













Support for Adding and Removing UDAs

User Defined Attributes are often used to mark Essbase members for special usage in an Essbase application.  This new functionality provides a new workbook script method, SetUDA, with both AddToMember and RemoveFromMember overloads.  The workbook script method allows an adminstrator to include functionality in an application to add, or remove, one or more UDAs from one or more Essbase members in a single call to the server.  Of course, with any workbook script, the functionality may be attached to a toolbar button, menu item or context menu item to make it easy for users to complete the task.

Improvements Working with Stored Procedures

In a Dodeca SQLPassthroughDataSet object, the select, insert, delete, and update statements defined for the object can be defined to execute a stored procedure.  Here is an example of a typical stored procedure:

execute @argument1,@argument2,…

Inside Dodeca, we take the SQL statements, parse them, and create prepared statement objects.  In this process, we also modify insert SQL on the fly to eliminate columns for which there is a parameter defined,

but which do not have a parameter passed for the record. We did this to allow for database default column values to be returned after an insert statement. Nice feature and I had just bunches of fun writing that one! It works very well except, for stored procedures, arguments are typically required (vs. SQL which is very flexible). We have changed the behavior of an insert when we detect a stored procedure to not modify the SQL.

Other New Workbook Script Events

BeforeDataTableRangeAddRow / AfterDataTableRangeAddRow - Occurs before / after a row (or rows) is added to a DataTableRange either by a user adding/inserting a row(s) or when an empty row is automatically added.

BeforeDataTableRangeDeleteRow / AfterDataTableRangeDeleteRow - Occurs before / after a row (or rows) is deleted from a DataTableRange.

Other New Workbook Script Methods

SaveDataTableRangeRow supports saving the changes to a specified row within a DataTableRange. The row can be an added (or inserted) row or a modified existing row.

Other New Workbook Script Functions

The DataTableRangeRowHasChanges function returns True or False based whether a row within a DataTableRange contains unsaved changes, which includes a new row and a modified existing row.

The DataTableRangeRowState function determines whether a row was Added, Modified, or Unchanged.
This is a summary of some of the new things in the Dodeca 5.3.0 release.  We have worked hard to get this release out the door.  Next on our agenda, we expect to release a 5.3.1 version with some functionality that didn't meet the deadline for 5.3.0.  We are also working in the background on our next big release; we hope to start talking about the contents of that release in the not too distant future.

Essbase Java API bug: IEssCubeOutline.executeQuery()

I have been working on some cool new things in our Dodeca Essbase web services server using the Java API and have found a few interesting things that I will try to post over the next couple of weeks. 

I found this first item about a month ago when I was working on member information.  If you have read my blog for a while, you may remember my comments last summer on how getting all information about a member can be quite hard and that you have to really open the outline to get the information.  I wish it had only been that easy.

One of my thoughts was to use the executeQuery method on the IEssCubeOutline object to query the data.  Theoretically, the members that are returned from that call should be 'opened' from the outline and thus all of the information is available for the member.  I say 'theoretically' because I couldn't get it to work.  No matter how I tried, the method always throws an EssException with the following error message:

Cannot query members by name. Essbase Error(1060000): Invalid outline handle

I wrote some sample code, against Sample Basic in version 11.1.2, and sent it over to some friends in Oracle tech support and they confirmed it was a bug within a couple of days.   This won't help me though as we support all versions of Essbase back to 6.5.3.  Even if they get a fix into the upcoming 11.1.2.1 release, it will be 5 to 10 years before I could consider using it.

Here is the code I sent to Oracle:

import com.essbase.api.base.*;
import com.essbase.api.session.*;
import com.essbase.api.datasource.*;
import com.essbase.api.metadata.*;

public class EssQueryOverOutline {
  private static String _username = "admin";
  private static String _password = "password";
  private static String _url = 
                 "http://localhost:13080/aps/JAPI";
  private static String _server = "localhost";

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

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

    // connect to the Essbase server
    server = ess.signOn(_username, _password, false,
                 null, _url, _server);

    // get the cube
    IEssCube cube = server.getApplication("sample")
                        .getCube("basic");

    // get the outline
    IEssCubeOutline outline = cube.openOutline();

    // Note: the next line throws the following 
    //exception:
    // com.essbase.api.base.EssException: Cannot query 
    // members by name. Essbase Error(1060000): 
    // Invalid outline handle
      
    // execute the query
    IEssIterator members = outline.executeQuery("Diet",
         IEssMemberSelection.QUERY_TYPE_DESCENDANTS,
         IEssMemberSelection.QUERY_OPTION_MEMBERSONLY,
         null, null, null);

    for (int i = 0; i < members.getCount(); i++) {
      // get the member
      IEssMember member = (IEssMember)members.getAt(i);

      // print some properties
      System.out.print("Member:");
      System.out.print(member.getName());
      System.out.print("; Parent:");
      System.out.print(member.getParentMemberName());
      System.out.print("; Is opened from outline:");
      System.out.print(member.getParent() instanceof 
                       IEssCubeOutline);
      System.out.print("; Parent from 
                       getRelatedMembers():");
      System.out.print(member.getRelatedMemberNames()[0]);
      System.out.print("\n");
    }
  } catch (EssException e) {
    e.printStackTrace();
  } finally {
    try {
      if (server != null && server.isConnected())
        server.disconnect();
    } catch (Exception e) {
      e.printStackTrace();
    }

    try {
      if (ess != null && ess.isSignedOn())
        ess.signOff();
      } catch (EssException e) {
        e.printStackTrace();
      }
   }
 }
}

And due to the wrapping problems, here is a jpg of the code from my Java dev environment, IntelliJ:

Oddly enough, the same week I was working on this, another Essbase Java API fan, and friend, Joe Aultman, gave me a call and asked me if I had ever successfully got executeQuery to work..  Boy, was I ever prepared for that question!

A Geek's Valentines Day

Probably many of you have read my bio and have read how great my wife is, but few of you know that our wedding anniversary is Valentines Day.  Darlene and I were married twenty one years ago today on Valentines Day of 1990.  Of course, we had to get married on a Wednesday to make it Valentines Day.  I am actually heading home early today (before 4 pm!) to take a walk on the nearby greenway with Darlene, but first I thought I would leave a little Valentine for all my fellow Essbase devotees..

Back when I was 13 years old, I had my first job pumping gas in my Grandpa's gas station.  Between customers, I always tried to read the newspaper and even had a favorite columnist.  I know what you are saying, what a geek, right?  The columnist was Sidney J Harris and my favorite column ran every Monday.  It was always entitled "Things I Learned En Route To Looking Up Other Things."  This nugget fits into that category.

I was recently looking for some specific documentation on the Oracle website when I ran across this gem, a PDF full of Tallyrand (11.1.2) infrastructure tips.  It also has some answers to questions where I have not previously seen documentation.

Here is a link to the file:
http://www.oracle.com/technetwork/middleware/bi-foundation/tallyrand-tips-157617.pdf

I thought that perhaps I was just late to the party finding this information, so I pinged my friend Cameron Lackpour.  He was aware of a version for 11.1.1.1.   I did a search and found a few other similar documents on the Oracle website:

http://www.oracle.com/technetwork/middleware/bi-foundation/epm-tips-issues-1-40-167121.pdf
http://www.oracle.com/technetwork/middleware/bi-foundation/epm-tips-issues-40-up-167122.pdf

Enjoy!

Brief Interview with new ODTUG Board Member, and Friend, Cameron Lackpour

Here is a short video clip of a brief interview with friend, fellow blogger and fellow ODTUG Board Member, Cameron Lackpour at the recent ODTUG Board Meeting near Oracle Headquarters in Redwood City, California. 

I was on-site at the hotel when this video was recorded, but was (luckily) working on my laptop in my room.  They did, however, get me to record some things that will be on-screen, apparently, at KScope11 this summer.  

Good job Cameron! 



KScope 2011 Discount Code

As a vendor supporting the KScope conference, Applied OLAP is able to offer this discount code for the best Hyperion conference in the world.  The Applied OLAP discount code, AOLA, will entitle you to an extra $100 off a conference pass.  Further, if you come to the Hyperion Networking Event at the conference, let me know you registered using the AOLA code and I will buy your first drink!

As a reminder, to get the lowest price on the conference pass, be sure to register before the Early Bird registration deadline of March 25th.   See you in Long Beach!

Getting back to SQL - Copying Dodeca commentary

I have been working on a new feature in Dodeca that relies heavily on SQL processing on the server, so I had to brush up on some of the SQL skills that I had not used much since I started with Essbase 16 years ago. Only a few of my friends know that, before focusing exclusively on Essbase, I worked on an Oracle Financials project at the last 'real' job I had at Intergraph here in Huntsville, AL. I got pretty good at SQL in that position and even architected and implemented an entire royalty processing module that bolted onto Oracle Financials Inventory, interfaced with the Oracle General Ledger package, and was fed by a Microsoft Access driven user interface that talked directly to the Oracle tables (back in 1994!)

The SQL knowledge came in handy when I started working on the Dodeca feature which will allow users to copy comments from one set of key combinations to another. We have had a number of requests for this functionality over the years and I have read many requests from frustrated Hyperion Planning users who desperately needed that functionality.

In our case, we store commentary in a relational table on the Dodeca server. The comments table has the COMMENT_TENANT and the COMMENT_ID fields as the primary key. The tenant code is a unique identifier for a Dodeca application. The comment ID is a GUID (Guaranteed Unique ID) obtained from the operating system. Here is a screenshot of the COMMENTS table.

The key/value pairs that identify the datapoint, or partial datapoint, that identify the comment are stored in a separate table that has a one-to-many relationship. Here is a screenshot of the COMMENT_KEY_ITEMS table.

The SQL problem I needed to solve was ‘How do I get a list of comment ID’s that have, for example, both the Scenario equal to Budget and the Year equal to Apr? My initial thoughts focused on the fact that no matter how I wrote the where clause, I would still get two rows back if my query returned the rows that I wanted. Myopia nearly got the best of me until I remembered the SQL GROUP BY clause. Using the GROUP BY clause, I could flatten the two rows into one. I still needed to make sure the original query, however, returned two rows, so the SQL HAVING clause did exactly what I wanted.

The next complication is that we use Hibernate Object-to-Relational Mapping technology in our server in order to support many relational data sources. This technology handles the translation of Plain Old Java Objects (POJOs) to, and from, relational sources. So, I had to write my Hibernate Query Language, or HQL, using GROUP BY and HAVING clauses:

select c.CommentId 
from Comment c join c.keyItems i 
where c.Tenant = 'SAMPLE' 
and (i.Key = 'Scenario' and i.Value = 'Budget')
or (i.Key = 'Year' and i.Value = 'Apr') 
group by c.CommentId 
having count(c.CommentId) = 2

Of course, in my code this HQL is created on the fly based on tenant code and the variable number of key/value pairs passed into the routine. Hibernate, in turn, writes the SQL. Here is an example of the SQL in SQL Server syntax:

Select comment0_.COMMENT_ID as col_0_0_
from COMMENTS comment0_ 
inner join COMMENT_KEY_ITEMS keyitems1_
on comment0_.COMMENT_TENANT=keyitems1_.COMMENT_TENANT
and comment0_.COMMENT_ID=keyitems1_.COMMENT_ID
where comment0_.COMMENT_TENANT='SAMPLE'
and keyitems1_.KEY_ITEM_KEY='Scenario'
and keyitems1_.KEY_ITEM_VALUE='Budget'
or keyitems1_.KEY_ITEM_KEY='Year'
and keyitems1_.KEY_ITEM_VALUE='Apr'
group by comment0_.COMMENT_ID
having count(comment0_.COMMENT_ID)=2

This SQL gave me the comment ID’s I needed to copy which was just the start. To complete the project, I also had to update the comment ID with a new Java UUID, update the related KeyItem records with the new UUID, recompute the COMMENT_KEY_HASH that is used to reduce the network traffic and required server resources by allowing for a query filter based on a single column value instead of a one-to-many join, and track/update the comment ID’s in the PARENT_ID field which is used to track threaded comments.

The code is now complete and will be checked into our source code control system today. QA can then get started with their testing so this feature can be released in the next Dodeca release.