SQL SSRS very large extract

Question

Hi I support some very large databases (approaching a terabyte). We currently have an in-house reporting solution, but are investigating the use of SSRS (SQL Reporting Services). However our users sometimes need to extract large volumes of data (many millions of records), which may take a few hours to retrieve.

What is an advisable approach to dealing with such large volumes?

Note. Getting the business to redefine there need for such large extracts is not currently an option.

Thanks

Answer 1

Extracting millions of records at the time... are we talking about reporting or analytic? Do they browse through those records, or aggregate over them? If you have lots of aggregation over millions of rows, you may consider SSAS cubes which would store pre-calculated aggregations, and then point reporting to cubes. SSAS server "listens" to the underlying DW/database and updates aggregations periodically (settable).