Reporting Services (DW)---a Technical Reference Guide for Designing Mission-Critical DW Solutions

Want more guides like this one? Go to Technical Reference Guides for Designing Mission-Critical Solutions.

Standard reports are the crux of BI applications and the foundation from which most companies run their data warehouse. They are typically predefined-format, parameter-driven reports. These reports are work-horse BI applications of the business. These are the reports people look at every day.

In more advanced applications, SSRS has been deployed as an integral part of the corporate reporting infrastructure. As such, reports are often pre-cached and/or advanced data warehouse aggregating techniques are used to dramatically enhance the performance of reporting and reduce the burden of ad-hoc reporting on the data warehouse.

Best Practices

The following resources provide reference material and additional information.

Case Studies and References

The following provide helpful information:

Questions and Considerations

Following are some question and consideration you can use when working with customers.

  • Reporting is a very generic term and often misrepresented. It is imperative to carefully understand an organization’s reporting strategy completely including:

    • Ad-hoc versus pre-canned reports

    • Data latency

    • Deployment strategy (e.g. SharePoint)

  • Define contents, layout and formatting of the reports.

  • Identify and establish Development, Testing, and Production environments for managing development and deployment of reports.

  • Developers will manage report development via BIDS (Visual Studio), while information users can use Report Builder 3.0 to author reports. Here is the link to getting starting with using SSRS: Getting Started with Report Builder 3.06

  • Establish deployment and rollback procedures for SSRS reports as a part of overall development strategy.

  • Integrate SSIS project in Visual Studio with other BI projects (SSIS, SSAS) for a comprehensive BI Solution.

  • It is highly recommended to introduce report development version control early on.

  • Use integration with Source Control systems, preferably with Team Foundation Server 2010: Team Foundation Server 20107

  • Build SSRS templates, and distribute them to development team members. This is very important for development involving multiple developers, often located at different geographical locations, to maintain consistency in presentation and adherence to corporate standards and regulations.

  • Consider integrating SSRS Report Viewer in applications for branding and conforming to corporate standards: Reporting Services and ReportViewer Controls in Visual Studio 20108

  • Define the execution options for reports—scheduled, interactive.

  • Define the distribution options for reports: web, email, file directory, and so on.

  • Participate in existing security systems, and potentially augment them at the user/report level.

  • Facilitate information collected in SSRS service databases to provide insight in reports metadata and information on user activity, report execution times, and so on. See Power of the ReportServer – How to pull data from Reporting Services Database.9

  • Use report execution times to proactively tune reports for optimum performance to maintain high end-user satisfaction level.

Appendix

Following are the full URLs for the hyperlinked text.

1 Report Server Catalog Best Practices http://sqlcat.com/technicalnotes/archive/2008/06/26/report-server-catalog-best-practices.aspx

2 Reporting Services Scale-Out Architecture http://sqlcat.com/technicalnotes/archive/2008/06/05/reporting-services-scale-out-architecture.aspx

3 Planning a Deployment Mode https://msdn.microsoft.com/en-us/library/bb326345.aspx

4 Component Architecture https://msdn.microsoft.com/en-us/library/bb522673.aspx

5 Service Architecture (Reporting Services) https://msdn.microsoft.com/en-us/library/bb630409.aspx

6 Getting Started with Report Builder 3.0 https://technet.microsoft.com/en-us/library/dd220460.aspx

7 Team Foundation Server 2010 https://msdn.microsoft.com/en-us/vstudio/ff637362

8 Reporting Services and ReportViewer Controls in Visual Studio 2010 https://msdn.microsoft.com/en-us/library/ms345248.aspx

9 Power of the ReportServer – How to pull data from Reporting Services Database http://weblogs.sqlteam.com/jhermiz/archive/2007/08/14/60285.aspx