Suggestions on Reporting Database

[Amanda Machutta]

Hello Everyone,

I'll be working on a solution for creating a database strictly for reporting. I was wondering if I could get some suggestions on what schema/engine is best for this type of data access.

Currently the way we have this all setup is we have a main database that stores all of the customer data, and then we replicate that data using MySQL Replication down to a replicated database where all the reports are run off of. This is fine to an extent, except that the tables are not optimized for reporting. So the queries for these reports often include 10 or more joins just to get the data and as we grow this is not scaling well at all.

What we would like to do is come up with a way to flatten the data and store it in a way that is optimized for reporting. Ideally this solution would allow our clients to access historical data for years to come so these tables could end up to be very very large (row count).

One of the ideas that have been kicked around is to use a nightly process (most likely written in ColdFusion or Java, that will go through and flatten the data in manual queries to outfiles, then import the data into the Reporting MySQL Database (which would use the MyISAM Storage Engine).

Another possible idea is to use triggers to write a flattened version of the data to the reporting database on INSERT or UPDATE of the data.

I'm looking forward to hearing your thoughts and suggestions. Thank you in advance.

Amanda