Hurray! Version 1.0 of Alfresco Business Reporting is released! After some blood, sweat and some tears (what did the world do to deserve the Oracle SQL dialect?) this new release is finally there. The long requested feature of supporting other database vendors than MySQL is finally there. You can store the reporting database in MySQL, Postgresql and Oracle. The report execution is also able to report against these database vendors (of course!) and store these reports back into the Alfresco repository.
The report generation is upgraded to the 5.0 version of Pentaho Reporting. The advantage of this is that you can generate Excel output again. (Technically: Pentaho Reporting and Alfresco use the same version of Apache POI libraries again!) Personally I like the newest Pentaho Reporting (5.0); a nice and stable version, hosting more features than I could think of.
The cron jobs are implemented using the JobLockService, so you can safely deal with an Alfresco Repository cluster, or (initial) harvest actions that take quite a long time. Two te same jobs will not overlap in time. Nor will they block starting-up your repository by the way. They wait until some minutes of uptime (default: 5 min) before firing of queries against the repo. (You might have experienced jobs kicking off repository actions when the repository has not fully started yet… Can be killing…)
I put in place some additional test to validate the harvesting mechanisms. Since there are 3 database flavors in play, there is quite some work to do in order to make sure all works as intended against each of the major database brands. This can always be extended, so if you have some additional time and/or java/testing experience available, please stand up!
The current version is quite stable, and I (actually; ‘we‘) have it in use at some of my/our customers already. (Thanks John and Nick!) However, building the code is still Ant based and Maven is on my radar. This would make it more easy to integrate with other projects and customers. Next to that I have quite some work in progress to harvest workflow and workflow-tasks, in order to have tables of all metadata available in those. The trick will be to incrementally harvest only the changes since last successful run (just like most cm:document/cm:folder based tables), but at this point in time there is no Alfresco/Activiti API to do so. (So I did it myself, relatively easy due to the new framework.) I intend to support Activiti only (no JBPM)
Where is it?
You can find the AMP and the Share-jar at Google Code. All documentation can be found there too, how to install the amp, how to add the JNDI context, and how to enable this in report execution. Also find tutorials on how to use the harvesting, simple or highly advanced, The same for report execution, how to create a basic report, or how to create one report definition and execute against many different sites, for example. If you can’t get it to work, please issue a ticket, or find your answer in the FAQ or previous tickets.
What is Alfresco Business Reporting anyway?
Alfresco Business Reporting is a module for Alfresco Community and Enterprise. Basically It does two things: 1) ‘Harvest’ metadata from Alfresco objects into a reporting database based on tables and content relevant for your business domain. 2) Execute reports against these tables, and store these reports back into Alfresco. The harvesting can be based on queries in Alfresco’s Lucene language (working against Lucene indexes as well as Solr!), but additionally stuff like users, groups, categories, audit-framework are harvested too. Harvesting is typically triggered y time.
The report execution is based on (an embedded version of) Pentaho Reporting. Pentaho Report definitions are executed against the same reporting database. The result can be a single report stored behind Alfresco’s powerful permissions. Alternatively you can design a single report definition, and execute it against a (subset of) Sites (or other containers/folders) generating a specific report for each of those Sites or containers (or Persons!), stored relatively against that Site (or container or Person’s home-folder).
The harvesting brings the full super-set of properties into a for the business meaningful table. All properties or associations the module is able to determine will end-up as columns in this table. If there is one aspect added to 1 instance of that query, you will find its properties as attributes, and the aspect listed in the list of aspects for that object. You can define new tables, based on new Alfresco queries. The module will create new tables, and add columns to the table/schema. Once you have your reporting database filled, you can leverage your existing reporting infrastructure of any brand or vendor, or you can use the embedded Pentaho-based version of reporting, that is up to you. The more advanced reporting tricks are available for the embedded Pentaho reporting only.