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?