Forum OpenACS Q&A: OLAP, OACS and nsJava

I was doing some surfing and came across this application Mondrian which is an Open Source OnLine Analytic Processing (OLAP) server. It is written in Java, works with both PG and Oracle and seems to be quite actively developed. I hadn't visited the site in a few months and in that time they had released two version updates.

For many of my clients, reporting on database info is a rather important feature. While doing basic Tcl reports is good, the ad hoc reporting solution is impossible to provide. OLAP tools are purpotedly supposed to help with such problems by aggregating information and providing GUIs to play games with that data.

MS has taken a lead in the developing OLAP tools on the server side, but they have done so by also building analysis tools into Access and Excel. Particulalry pivot tables. It would be cool to provide a link from OACS/AOLserver systems to Excel via some kind of OLAP system. This may be overkill, of course, but it's at least the "industry way".

Mondrian is written entirely in Java and uses Tomcat as its container. I was wondering whether Mondrian might be a good test case for OACS + Java integration?

talli

Excel can create pivot tables from arbitrary ODBC sources.  If you have the pg odbc driver,  and access from the user's desktop to the web site,  you can do this without OLAP.

There are typically two problems with this approach if it's a large amount of data - security and bandwidth

Normally you would deploy your web app somewhere on the 'net where you would prefer not to grant direct database access.  Tech savvy end-users could do ssh tunnelling, or you could set up some similarly-encrypted VPN between end-user desktop and database server,  but the tried-and-true way to go is to dump and load your production database to a reporting server that's on the same lan as the end user.  That way even if you have a huge amount of data going back and forth between db and server,  it's going across a 100MBit lan,  and not the internet.