Re: A design quiz

[Rick James]

> Currently we finished our OLTP design and I wonder where should we go from here...
That statement scares me. The design is not "finished" until all aspects of the project are included.

> To load ROLAP with suitable data we need to define ETL processes
Are you suggesting having two databases with the same data? One for OLTP, then copy the data (via ETL) to another database?
But...
* What about how stale the data will be after each "copy process"?
* How much will the OLTP system be "locked" during the copying? That is, can you afford the downtime to grab a _consistent_ copy? (Perhaps Xtradb can do this will minimal impact.)

You have thrown around lots of buzz words, should of which I am not familiar with. Please explain your needs without the buzz words; they get in the way. Dissecting your original statement...

> Normally two to three milions of records are inserted into database per day. -- use Batched INSERT or LOAD DATA.

> A realtime monitoring is also needed over the latest incoming data. -- PARTITION on date, index somewhat. Let's see the tentative SELECT statements.

> The historical data must be stored in a way suitable for future analysis and business applications. -- Summary tables. Let's see the tentative SELECT statements.

And let's see the SHOW CREATE TABLE(s).

I don't yet see the need for two databases and the ETL between them. I may recommend ETL up front to populate both the one database, plus the summary table(s).