Raulsassaa

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 !




Re: Architecture General Report approach

Arnon Rotem Gal Oz

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





Re: Architecture General Report approach

Raulsassaa

I also prefer this solution. The big quizz here is how to control several users trying to print the same report. Then n machines would try to access the same table (assuming its 1 table per report) to create the report. How could I control this concurrency Maybe using an unique ID That sounds pretty cumbersome...

any idea






Re: Architecture General Report approach

Arnon Rotem Gal Oz

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