Analytic / Reporting - same or separate database, and which DB?

Question

I have a user content website with some business functionality. All tables are in 1 database. Now I am adding analytic with in dept reporting based on the activity and user log tables - breaking it down to have in dept reports by each day of the year, by each activity type, etc. The question is: do I create a separate database for analytic (or as people call it the data warhouse) or do I just add those new tables to existing database? If I have to create a separate DB for this, then that means I need to load in all the data from the main DB into temp tables in the Analyic DB then load that data into the analytic tables I assume?

The analytic requirements is as close to real time as possible so based on this I am not sure which DB to use if I do go for a separate one. Can MySQL that I use do the job of providing real time analytic, that is user takes an action and the next second if he views a report the numbers will be already aggregated?

Answer 1

It depends on the amount of reporting you're expecting. Transaction processing databases are generally designed in 3NF for efficient inserts.

Reporting is more complex due to the number of joins required. Also adding an significant extra number of SELECT transactions from reporting has the potential to degrade performance, hence why reporting databases are used.

It's up to you to weigh the likely reporting load and performance impact against setting up a reporting replica and ETL to populate it. Also you need to determine if you have a replica, how often to replicate. There is an argument you can use against the 'real time' requirement that the business reporting may be more 'consistent' if the business are reporting against a fixed snapshot of data (e.g. a daily copy).

See Strategies for populating a Reporting/Data Warehouse database for approaches to load data into a reporting database.