MySQL Forums
Forum List  »  Data Warehouse

MySQL data warehouse? Newbie
Posted by: Maximilian Seifert
Date: July 02, 2010 04:01PM


we have 2 MySQL databases of about 30 GB each (for 2 completely different products, the structure is very different). They are used as transactional databases.

The thing is we also want to do complex analytical queries on this data and even though it's "only" 30GB some queries are simply not possible at all. For example grouping a non-indexed table by 3 different columns at once and joining it with 2 other tables, all of which have several million rows, and without using a where condition. But we really need this data and we need it within < 1 second response time, so obviously we need a new solution. We didn't put indexes on the tables because it's actually a transactional DB and bad for OLTP performance (?), plus it probably wouldn't give us the sub-second response times we want either.

My question is how we should go about setting up a data warehouse. Our requirements are as follows:

- We would like to use a BI platform like MicroStrategy

- If possible, we'd like to have real-time synchronization between the data warehouse and the operational MySQL DB. If not possible, hourly synchronization. But it has to be reliable.

- The data warehouse should be synchronized with both (completely different) operational databases, plus some files that are generated by some scripts (web crawler stuff) - but those files can be updated like every day

- The data from the data warehouse actually has to be DISaggregated, like a table with 3m rows has to be turned into 20m rows and to be made MORE granular that way. Just imagine you have a table "slot_machine_displays" and it includes a "symbols" string column including the ids for the symbols that showed up. Well, we want to turn that into a table "symbols_that_showed_up", making it more granular. That's just an example, we're not a casino ;)

- It should not be a pain to set up. For example, if I have to think about in detail what aggregate tables I want to create, that would be a pain. It would be nice to have a data warehouse where I just push a button and it loads all the data in real-time from the operational databases, and all analytical queries, even the most complex ones that analyze data for an entire year across 5 dimensions are carried out within seconds.

-We're a company with 40 people and basically have no real budget to afford enterprise-level solutions. Anything >5000 EUR would probably be not possible.

I am COMPLETELY lost on this so maybe someone can explain to me in some detail of what we should explore in this situation.

I already looked at InfoBright but I don't know how it's supposed to synchronize with the operational database? ETL tools like Talend seem to be able to do that but the enterprise edition is ridiculously expensive.

Is there a really easy solution that I simply didn't realize? I mean isn't that the no.1 problem any small web company has? Having to analyze their MySQL data?

Options: ReplyQuote

Written By
MySQL data warehouse? Newbie
July 02, 2010 04:01PM
July 03, 2010 09:15PM
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.