How do I integrate LimeSurvey with Pentaho?

Question

I'm trying to integrate LimeSurvey (a PHP web application to develop and collect responses to online surveys) with Pentaho (ETL, business intelligence, reporting, etc.), but I'm facing some serious problems.

LimeSurvey automatically generates a new table for storing the answers of each survey created within my application, and I need to use Pentaho for dynamically generating reports from the answers of the surveys.

How could I deal with the problem of having an unknown number of tables as data sources for Pentaho, where both the name of the tables and the names of its fields are generated automatically by LimeSurvey? What strategies, ideas or mechanisms could I use in Pentaho for loading information from such an unpredictable data source, and generating reports from it?

Alternatively, is there a way to tell LimeSurvey to store all the survey answers in a single table, in a way that is easily extracted from a business intelligence tool such as Pentaho?

Thanks in advance!

Answer 1

Proper: Whip LimeSurvey into shape, a table per survey, come on. A little bit out of scope though.

Workable: Create a view with all tables + a column for the 'survey-id' which Pentaho can use. Recreate on survey adding / removing, pray there are hooks in Limesurvey to easily add those SQL statements.

Answer 2

If you know or can predict the schema on how LimeSurvey names the tables and how to join them, then you could use a "Advanced SQL Datasource" along with a expression to compute the query. It will probably be a BeanShell or Bean-Scripting-Host expression, as AFAIK messing with those tables will be complex early on.

If you want to know more about the Advanced SQL datasource and calculated queries, have a look at Mike Tarrallo's "Guided Adhoc" recording at

http://wiki.pentaho.com/display/COM/April+7%2C+2010+-+Michael+Tarallo+-+Guided+Adhoc+reporting+using+the+BI+Server