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.