Considerations for a large report model

Customer report models that vary in size from a few to a few hundred entities. Little Northwind with its 10 or so entities comes in around 200K, but we've seen models a hundred times that size (over 20 MB). One of the key drivers of model size is the constraint that you cannot build a query across multiple models, so the tendency is to pull more and more data into the "main" model.

 

Given that, I thought I’d share a few tips that may help if you want to build, deploy, and use a large report model.

 

1. Configure the report server allow upload of large files. By default ASP.NET limits the file upload size to 4 MB. You will need to modify this setting if your model exceeds the limit.

 

2. Consider removing the diagrams from the DSV. Typically the diagrams in a DSV account for ~20% of the uploaded model size. This information is only used by Model Designer, however, so removing it will not affect either the report server or a client like Report Builder. The DSV editor will not allow you to remove the default “<All Tables>” diagram, and if you do remove it, the editor will recreate it with a default layout the next time you open the file, so you will need to remove this diagram from the XML manually before publishing.

 

3. Define perspectives in the model. Perspectives allow users to self-select a convenient subset of the model at the time they design the report. This reduces download time (only the subset is retrieved by Report Builder) as well as clutter during the design experience. In extreme cases, you may want to require users to choose a perspective. Note that perspectives are not a security feature. Also note that it is possible, though not particularly easy, to change the perspective used in a query after the query has been created.

 

4. Use model item security. This reduces the subset of the model available to specific users and groups, which in turn reduces the download time and visual clutter for those users. Note that as of SQL 2008 report subscriptions are not supported when using model item security.

 

If you are using a large report model and have additional tips or questions, please contact me. We'd love to get your feedback.

Comments

• Anonymous
  April 27, 2009
  PingBack from http://www.anith.com/?p=32957
• Anonymous
  June 22, 2009
  Eso es lo que me pasó el otro día cuando intentaba subir un modelo de poco más de trece megas al Report
• Anonymous
  June 22, 2009
  Eso es lo que me pasó el otro día cuando intentaba subir un modelo de poco más de trece megas al Report
• Anonymous
  June 22, 2009
  Eso es lo que me pasó el otro día cuando intentaba subir un modelo de poco más de trece megas al Report
• Anonymous
  April 02, 2011
  The site SQL Reports has a great SQL tutorial. Highly recommended for people just getting started on SQL selects.Sql tutorialwww.sql-reports.net/.../sql-select-tutorials.html