Skip to end of metadata
Go to start of metadata
Table of Contents

Introduction

PUM Senior Experts is a Dutch organization. The PUM Senior Experts (all volunteers) share hands-on business knowledge with organisations in developing countries and emerging markets, taking away bottle necks, and facilitating sustainable paths for growth. To learn more about what PUM Senior Experts do check their website at https://www.pum.nl/

PUM has been using CiviCRM (in combination with their Drupal website) as their main information system since 2015. They rely heavily on CiviCase, as almost all of their activities are captured in cases. They also have quite a few extensions they use, some of them developed specifically for them as the challenge was very specific but also quite a few where they funded the development but the extension is on the extension directory. For example:

They are supported by the CiviCRM partner CiviCooP (more about CiviCooP on https://www.civicoop.org/) that are based in The Netherlands, as is PUM.

One of their challenges now is to get business information from their CiviCRM. Most of the transactional reports that are used by daily users are solved with either standard or customized CiviCRM Reports, and a bunch of Drupal Views show data from CivICRM to the users of the extranet part of the website. However, they also want to have stuff like how many projects do we do in which country, what age bands do we have experts in etc. On top of that they also have Exact as a financial software package, historical data from PRINS (the Lotus Notes based system they were using before they started using CiviCRM) and some important data in Excel sheets. And ideally they want to be able to do data analysis on a combination of these data sources.

At CiviCon London in October 2015 some of the functional experts at PUM spoke with various people on data integration and reporting. Based on that experience we are now embarking on a series of sprints with Pentaho. And Pentaho was selected because Owen Bowden from Bloodwise (a UK based charity that has been working with CiviCRM for a number of years) was prepared to come over to the Netherlands to work together with PUM and  Jaap Jansma & Erik Hommel from CiviCooP to get them started.

This cookbook does not aim to be a complete tutorial or guide on how to use the Pentaho Business Intelligence suite in combination with CiviCRM. It does aim to tell all of you what we have done and why, and show you the steps. We strongly believe in sharing what we do and we trust there will be someone that we can help in making a little progress.

Overview of Pentaho components at PUM

Using Pentaho

CiviCRM version at PUM

PUM Senior Expert uses CiviCRM versions 4.4. Although this is the wiki for the latest version of CiviCRM (and 4.4 is not that anymore) I still think this cookbook should reside here as all the principles and explanations will be valid whatever the version of the CiviCRM database you are using.

In the child pages we will explain the details of what we have done, but in bold strokes this is what we have done with the Pentaho Community Edition that we started to use at PUM Senior Experts:

  1. Extract data from the CiviCRM database, transform it and load it on the PUM data warehouse using Pentaho Data-Integration
  2. Create a Mondrian schema of the data cubes usings Pentaho Schema Workbench (and PHP Storm to edit the XML files directly because we found it easier)
  3. Use Saiku Analytics to do ad-hoc reporting on the data warehouse
  4. In the longer run, use Pentaho Report Designer to create static reports where appropriate
Labels
  • None