Re: MySQL data warehouse? Newbie

[Themba Ngobeni]

Hi,

I have been faced with the similar problem, using online surveys where the tables in the database were not designed for reporting or they never thought of reporting at all. it was just a push method with little or no pull method. Now the need of reporting has raised to 90% The problem and its setting:
1. A table column name is made out of 3 different table column id's
2. the are no indexes, no common field naming, no standards how so ever the column data has different set of information with a similar column title e.g Lookup,Multi select,Number,Short text,exclamations etc

To cut story short our solution was:
1. create a datawarehouse using MYISAM ENGINE for mysql.
2. create dynamic sql scripts to do extracts
3. create Transformation using Pentaho PDI(SPOON)
4. create/load Fact/Dimensions
5. create Analysis with Mondrian
6. create Dashboard reporting using Pentaho BI

our settings for the warehouse allows us to retrieve transactional data of all surveys in less than an hour. all we used was pure open source and what I can say sofar I found Opensource ETL Spoon/KETTLE to be one of the best in the market sofar.