When to build a separate reporting database?
We're building an application that has a database (yeah, pretty exciting huh :). The database is mainly transactional (to support the app) and also does a bit of "reporting" as part of the app - but nothing too strenuous.
Above and beyond that we have some reporting requirements - but they're pretty vague and high-level at the moment. We have a standard reporting tool that we-use in-house which we'll use to do the "heavier" reporting as the requirements solidify.
My question is: how do you know when a separate database for reporting is required?
What sort of questions need to be asked? What sort of things would make you decide a separate reporting database was necessary?
Answer
In general, the more mission critical the transactional app and the more sophisticated the reporting requirements, the more splitting makes sense.
- When transaction performance is critical.
- When it's hard to get a maintenance window on the transactional app.
- If reporting needs to correlate results not only from this app, but from other application silos.
- If the reports need to support trending or other types of reporting that are best suited for a star schema/Business Intelligence environment.
- If the reports are long running.
- If the transactional app is on an expensive hardware resource (cluster, mainframe, etc.)
- If you need to do data cleansing/extract-transform-load operations on the transactional data (e.g., state names to canonical state abbreviations).
It adds non-trivial complexity, so imo, there has to be a good reason to split.