As consultants the first question we are often asked by our customers is can we build reports directly against our transactional systems. While there are some exceptions our general best practice is to recommend against this type of architecture.
The reasons are long and detailed, but simply put, some high level reasons not to build reporting systems off of normalized databases are:
- Query performance
- General production system performance
- Aggregate view of data vs transactional view
- Complex SQL
- Normalized databases are typically tuned for simple queries
The discussion then leads to questions surrounding data mart or warehouse creation. Typical initial concerns are time, money, maintenance, and flexibility. All which are real considerations when traveling this path.
