Dave's Adventures in Business Intelligence

Changing Data Providers in Complex Reports

We have some fairly complex reports at my current client. Of course the word “complex” is not very clear; what is simple to me might be complex to someone else. So let me quantify what I mean by “complex” in this case. The report in question has multiple data provders. Each data provider generates multi-pass SQL because of contexts. Altogether there are 29 data providers with an overall total of over 100 SQL passes. 😯 That’s what I mean by a “complex” report, and I think most folks would agree with that assessment.

The challenge is that for this (and other) complex reports we have been encountering severe issues. In some cases we are unable to edit the queries without restarting our browser session. We also have encountered a number of problems during the process of repointing the document from one universe (development) to another (user acceptance testing) within the same environment.

Current versions of Web Intelligence offer the ability to repoint a document using the following steps:

• Open the report
• Edit the query
• Click the […] button next to the universe in use
• Select the desired destination universe from the list
• Review the object mapping and click OK to continue

Side note: I really really like the fact that I can remap individual objects rather than having the process fail as a result of something being missing, but that’s another blog post…

Our problem is that after performing the steps listed above, we wait. And wait. And wait. 🙄 Eventually the session times out. Finally (after much beating of head on desk) I remembered to activate the Java console. (Click Tools and then Sun Java Console from the Internet Explorer menu bar to start the console.) The console shows what’s going on within the Java environment. It seems that I was running out of memory during the attempt to switch universes, and Webi was not capturing the exception properly. Instead of letting me know there was an issue, it was simply… not responding. Nice.

Setting Java Heap Size

Long story short, the memory exception was related to the heap size. So I did some searching on the Sun Java forums and found a number of tips about how to increase this parameter. The exact process will be different based on the version of the JRE that you are using, but the desired parameter is the same either way. If you open a command window and type java -X (which is case sensitve, it must be a capital “X”) the program will return a list of valid parameters. For my version I got this:

java -X
    -Xmixed           mixed mode execution (default)
    -Xint             interpreted mode execution only
    -Xbootclasspath:
                      set search path for bootstrap classes and resources
    -Xbootclasspath/a:
                      append to end of bootstrap class path
    -Xbootclasspath/p:
                      prepend in front of bootstrap class path
    -Xnoclassgc       disable class garbage collection
    -Xincgc           enable incremental garbage collection
    -Xloggc:    log GC status to a file with time stamps
    -Xbatch           disable background compilation
    -Xms        set initial Java heap size
    -Xmx        set maximum Java heap size
    -Xss        set java thread stack size
    -Xprof            output cpu profiling data
    -Xrunhprof[:help]|[:=, ...]
                      perform JVMPI heap, cpu, or monitor profiling
    -Xdebug           enable remote debugging
    -Xfuture          enable strictest checks, anticipating future default
    -Xrs              reduce use of OS signals by Java/VM (see documentation)
    -Xcheck:jni       perform additional checks for JNI functions

The -X options are non-standard and subject to change without notice.

The one I am interested in is Xmx which can be used to adjust the maximum heap size used by the JVM. Based on some reading, setting this value too high can be just as bad as having it set too low, so I didn’t want to go nuts right away. 🙂 I tried several different values, using my complex report as a test case, and ultimately found that 128m was just fine for me.

The screen used to update this changes based on the version of Java that you are using. I have included screen shots for both 1.4.2 and 1.5 at the end of this blog post.

Conclusion

After making this simple change I was not only able to change the data provider source for my complicated report, but other operations became much more stable as well. I also think that many operations were faster.

Or maybe it was just the fact that they completed at all that made them seem faster. 😉

Java 1.4.2 Heap Configuration

Java 1.5.0 Heap Configuration