MySQL Forums
Forum List  »  Data Warehouse

Re: MySQL data warehouse? Newbie
Posted by: Themba Ngobeni
Date: August 30, 2010 04:56AM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
4607
July 03, 2010 09:15PM
Re: MySQL data warehouse? Newbie
4845
August 30, 2010 04:56AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.