Reporting solutions

I am soliciting for suggestions on reporting solutions

We develop a lot of in house projects(.net and sql server). For larger database we use business objects and b开发者_开发问答uild universes for reporting so that analysts or report writers can build reports and developers dont need to be involved.

Many of our projects house important data, but are not large enough to warrent universes and data warehouses being built on them. We still need to build reports off of this data but we don't want to report off the live databases as that could affect performance of the applications. For some of our projects, we do nightly backups/restores, effectively duplicating the database and then use the copy as a reporting database. Not having a lot of reporting experience, I'm wondering what other solutions people have implemented.

---

In situations like this, we've set up transactional replication from the OLTP database to a secondary database that is used for reporting purposes.

---

If you are running 2008 you can use the resource governor to limit cpu and memory usage on your production database server by your reporting users. Best scenario is to have a dedicated reporting server and db, but this can work.

---

The simplest approach is to set up a server for reporting and replicate your databases onto it. Run the reports against the reporting server.

This may not be very efficient for reporting, and will probably have performance issues if you have a large reporting workload.

Depending on your reporting requirements, you may want something in between a data warehouse and a suite of reports against a copy of your operational database. Simpler, system specific flattened reporting structures can often be implemented fairly quickly. Build a basic ETL process to populate this with a nightly refresh, and you will have something that can be reported off reasonably efficiently.

For more complex analytical requirements or if you want to use cubes you may have to bite the bullet and build a proper warehouse or data mart.

In either of the latter scenarios, SQL server comes with a tool called Report Builder (from SQL Server 2005 onwards), which can be thought of as a poor man's Business Objects. This could be used to provide an ad-hoc reporting capability against a reporting database. However, as you have no control over the SQL produced by the tool, it is likely to perform poorly if you attempt to use it with the raw data structure from your operational databases. Getting good results from a tool of this sort tends to require a database structured to play nicely with the tool and ETL processing that scrubs the data so it's fairly well behaved.