Releational Database options for Reporting and Scaling / Performance with XPages and General Notes / Domino

Chris Connor  May 22 2012 03:38:41 AM

At the moment considering options for XPages application architecture for a customer. Forming options based around the need for building reports (traditionally a poor area in Lotus Notes). Client is very keen on the use of relational database access for this so that they can easily write queries to give them the reports the need with SQL / stored procedures or use standard "Reporting Tools" to access the structured data.

Based on this I was considering evaluating

1. DomSQL - built for this very purpose. Uses SQLLite to "duplicate" view data. Some initial things to consider are
(a) Easy to setup - Install some OSGI plugins on the server for XPages and configure some XML settings
(b) Synch happens with Notes / Non Notes without having to do anything
(c) Performance - a concern. Dont think this is the sort of thing we can just start to "switch on" on a production server without serious consideration of traffic / transactions, databases / data volumes etc.
2. Build some scheduled / triggered agents that move the data between both RDBMS data and NSF (one way from NSF to RDBMS)
3. Writing some sort of triggered event (on save of the document) in XPages that pushes the data over to the RDBMS  (Say their corporate SQLServer / Oracle database structure)
4. Using the Relation Data Access stuff released last year to read / write to the SQL store.

These options all mean "duplication of data" on both sides of the system

Don't really see that as a big issue - as you can still take advantage of the XPages framework for workflow / mail and RAD. Its only a subset of the data that would be copied to the relational structure (towards the end of the project once we start honing reporting requirements). All of the points (except 1) involve writing code to push data to the RDBMS - there is a time and cost factor here.

Will be progressing these investigations - hopefully document findings here on approaches. Keen to hear your thoughts / comments...




Tweet

• Comments [16]