I was lucky to learn from a failure of analytics (kind of reporting) feature which was built directly on operational database.

My notes on this

bliki: ReportingDatabase
A reporting database takes data from an operational database, reorganizing it to better support reporting.

Some key insight:

  1. Operational databases need to be in high performant state.
  2. Adding reporting tasks (calculating, aggregating, even reading) into operation increased the load of operational database, and high chance results in performance degradation.
  3. Changing is hard: reporting task may be handled by a separate tech team, which will highly depend on the operation development team when coming into data changes.
  4. Lack of derivative data storage: Working on reporting requires the generation of derivative data. It does not make sense if those data (for report task only) are saved into operational database. This leads to repeated computation tasks, using more computation power of the database servers.