Reporting for multi-tenant web app

Question

We have a multi-tenant web-based Enterprise application using Hibernate over MySQL. We use Jasper Reports for any reports that our customers require, but now we also need to offer ad-hoc reporting so that Users can run their own queries.

How have others accomplished this?

I am thinking that I can either:

1. Provide a full export in excel or xml format where I hydrate the entities so that any @ManyToOne is replaced with the toString() of that entity. This would be so that the data actually makes sense to the user rather than a whole lot of foreign key IDs.

2. Let them run SQL against a database copy. Ensure that every table has a TENANT_ID, let them access the database copy but append the ID to every query behind the scenes. I could even ensure that this database copy only had their data in it. Sort of defeats the whole multi-tenant approach though.

Answer 1

How complex will those user queries be? Arbitrary SQL? Or can you get by using HQL or Criteria (and what I really mean by this is you'll let users define some sort of QBE in UI but you'll be the one building the actual query)? In the latter case filters might help quite a bit too.

I wouldn't bother with (1) unless query results are always plain lists. You can represent hierarchies / relationships in XML but I doubt your users would appreciate this as they'd have to process it. And toString() approach is pretty much guaranteed to backfire because different users are bound to be interested in different renderings of the same object (e.g. if you return list of As which are linked to Bs, user1 would want different outcome from B.toString() then user2).

(2) should work if you truly need arbitrary SQL queries. Depending on how complex your database is and how many users you have, you may be able to get by with creating views (per user) instead of actual database copies.

Answer 2

I have the same problem and am thinking (hard) about implementing a custom ODBC driver that "hides" the multi-tenancy. Check out Data Direct OpenSDK ODBC...

http://www.datadirect.com/products/odbc/index.ssp