I'm evaluating data cubing tools for Oracle 10g and it is turning into a far more daunting task than it should be. Oracle hasn't made my life easier by bundling (and sometimes bungling) so many different products in so many different releases. Most of the data cubing tools I'm looking at tie into JDeveloper directly, and there are three different versions of JDeveloper, each dependant on specific versions of the database or OLAP tools. The tools I'm evaluating don't have any documentation and Oracle has too much. I have to figure out the dependencies by trial and error as I try to figure out how to configure the tools by trial and error. It's kind of driving me crazy.

If I was just Joe DBA and all I cared about was the database, I'd be fine - focus on the database and forget about that Java nonsense. However, no such luck. I love Java nonsense and I'm really excited about data cubing tools and BI Beans. Problem is that many of those tools rely on BI Beans, and BI Beans rely on a very touchy set of database APIs for OLAP. I won't dismiss any tool until I've gotten it running and played with it, but based the configuration management mistakes these folks made, I am tempted to call a few of them garbage and be done with them.

First huge problem. One of the tools I looked at embedded their database connection data in an XML file in the distribution. So I have to find this file, replace it, and rebuild the ear file. It was like they didn't understand the concept of a datasource. All you have to do is document a database connection lookup file in $ORA_HOME/j2ee/home/config. Don't assume that the user is connecting to ORCL on localhost, and don't use your own stupid connection mechanism. Add the following to data-sources.xml

            <!-- Edit the following datasource for your database -->
    	<data-source
    		class="com.evermind.sql.DriverManagerDataSource"
    		name="OlapDS"
    		location="jdbc/OlapCoreDS"
    		xa-location="jdbc/xa/OlapXADS"
    		ejb-location="jdbc/OlapDS"
    		connection-driver="oracle.jdbc.driver.OracleDriver"
    		username="bibcat"
    		password="bibcat"
    		url="jdbc:oracle:thin:@solitude:1521:lter"
    		inactivity-timeout="30"
    	/>
    

The second problem is that these tools share a dependency on Oracle OLAP and the example dataset provided by yet another tool BI Beans. If performance of Oracle Olap is far and above an OLAP engine you could write yourself (I haven't really dug into it enough yet) then use it. But if it doesn't, don't bother. It is generally broken in the first few releases of the database and configuring it can be a real bear. If you wanted to use the Oracle OLAP engine, why not just have your own example data and create your own OLAP structure within Oracle's OLAP? If you wanted to avoid the Oracle's OLAP you could still use the GROUP BY (CUBE) syntax.

The point is that Oracle has a lot of releases out there and if you can minimize dependencies on specific versions your users will have a lot easier time with your software. Don't try and create your own database connection standards and use the J2EE standard method of using data sources. Also, it pays to write a little documentation and don't just assume that your users will decompile your code to figure out how to use it.