Report approach
Hi all, we are starting our report development now and some doubts have come up to my mind. I figure out some possible solutions, as below:
1) create a view and associate it to the report.
Good > easy to develop
Bad > limited when we need to compute complex things
2) create a SProcedure and associate it to the report
Good > powerful when needed to compute complex things
Bad > not portable among DBs; difficult to mantain
3) code all behind (C# ou VB) and then populate a "temporary table" with all prepared result
Good > it is able to compute powerful things; easy to mantain; easy to develop
Bad > has to work with a temporary table; has to handle concurrent users
What would be the best solution my friends? and why?
Thanks to all !
Unless you just need sporadic reports here and there, I usually find it is better to prepare a separate database for reporting. This database is optimized for reporting (e.g. pre-calculated fields etc.). The reporting database is updated in the background so it stays current (replication or SSIS ). You can then use a dedicated reporting tool (such as reporting services) to build reports on that database
The advantage is that reports takes less time to complete, you can handle concurrent requests, reports can and usually do cut across entities boundaries so it isn't efficient to deal with them using domain objects (which I what I usually have in the business layers), you can add/change reports easily
I've written about it in the context of SOA in a pattern I call Aggregated Reporting here:
Arnon
The solution I talked about would have a set of tables with data optimized for reports (a mall Datamart) and not tables that are created per report
The reporting database is updated in the background as updated to the OLTP database happen (the frequency of update depends on your needs)
Since the running reports would only perform read operations on this database concurrency isn't that much of a problem
Arnon
