Reporting and Data Mining: Reconciling Disparate Data Sources
Facilitated by Leo D'Angelo, CTO MPower Open
This session will focus on the challenges that arise for many organizations around reporting, especially when confronted with multiple data sources like a donor database, a web database, and events database, etc. We'll be discussing innovative approaches to solving this problem using open source business intelligence solutions like Jasper and innovative data pooling with federated tables.
Session Notes
History - needed cross DB solution that could be used across the non-profit sector. Goal is to provide reporting solutions for end-users by leveraging Jasper server.
Database and schema agnostic.
You create a metadata layer to describe and group the data - datasource can be table, view, stored procedure
Report layouts are stored as templates in Jasper Server
Standard Filters are Date Range types (e.g. current fiscal year, past 2 fiscal years, etc). Additional filter values can be pre-populated for prompted from user at run time Wizard creates a report definition file which can be subsequently edited in the ireport (jasper report designer) So 80% of reporting needs should be met by wizard, remaining 20 using report designer Currently in beta and available for download
Can use Jitterbit - ETL ("extract, transfer and load" from a remote DB via SOAP or REST interfaces) - For example could connect to a SugarCRM or SalesForce instance (for example) and extract and pull down data to a local data warehouse.
Many complex use cases are harder to handle. Joins, nested filter sets are 2 examples. The tool is evolving to handle layers of filters.
Charts can be configured to portlet containers (e.g. a Key Performance Indicator chart on a dashboard).
App is built in Spring and can authenticate against a number of repositories (LDAP, Active Directory, local db table, etc.). Access controls available down to report level.
Reports are segments by Personal, Role or Organization level.
Recurring report creation can be scheduled with formats and delivery options (e.g. deliver a CSV for mail-merge to a mailing house).
In January will have admin interface to populate metadata tables for a specified datasource(s). If you're using MPower, the definitions are delivered "out of the box".
Will be integrated into Jasper server next summer. Currently downloadable from MPower and soon from SourceForge.