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.

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.