1 / 25

SSRS – Lessons Learned at the University of Iowa

SSRS – Lessons Learned at the University of Iowa. Russ Allen, Lead DBA Information Technology Services University of Iowa. University of Iowa ITS . Provides Core IT Services to the Academic Side of the University Servers (Windows, Linux, AIX, VMWare) Networking Identity Management

faith-hood
Download Presentation

SSRS – Lessons Learned at the University of Iowa

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. SSRS – Lessons Learned at the University of Iowa Russ Allen, Lead DBA Information Technology Services University of Iowa

  2. University of Iowa ITS • Provides Core IT Services to the Academic Side of the University • Servers (Windows, Linux, AIX, VMWare) • Networking • Identity Management • Email and Messaging • Application and Instructional Support • Application Development (Student Information System, etc.)

  3. University of Iowa ITS • Database Services • Oracle • Database Administration • Development • SQL Server • Database Administration • Database Hosting • SQL Server Business Intelligence • Administration • Hosting • Development

  4. Typical Iowa SSRS Implementation

  5. SSRS Architecture • High Availability – SSRS Back End • SSRS back end databases should reside on a dedicated clustered instance • Well-designed reports will quickly become critical to departmental operations • No amount of system downtime will be tolerated • Consider three node clusters for extremely critical reporting systems • One active, one passive, one offline for maintenance

  6. SSRS Architecture • High Availability – SSRS Web Farm • Create an SSRS web server farm behind a network load balancer • SSRS users connect to an alias that references the network load balancer, not an SSRS web farm server • The load balancer will redirect requests to the least busy SSRS web server • The load balancer can be configured to remove a server from a pool before it is taken down for maintenance • Firewall rules should be created to prevent direct connection to web farm servers

  7. SSRS Architecture • High Availability – SSRS Web Farm • You must configure SSRS web farm servers to be members of an NLB server pool • Modifyweb.config files to include a “MachineKey” entry • Example: <machineKeyvalidationKey="123455555" decryptionKey="678999999" validation="SHA1" decryption="AES"/> • This entry must be identical in all the web.config files on all SSRS web farm servers • Web.config files must be modified for both the ReportManager and ReportServer

  8. SSRS Architecture • High Availability – SSRS Web Farm • You must configure SSRS web farm servers to be members of an NLB server pool (cont.) • Edit the HostName and UrlRoot entries in all RsReportServer.config files • These entries must reference the NLB alias, not the individual web farm servers • For more information, read MS white paper entitled “Configure a Report Server on a Network Load Balancing Cluster”

  9. SSRS Security • Service Principal Names (SPNs) • Required if Windows credentials will be used for both SSRS and SQL Server authentication • Must be created explicitly for each SQL Server • Must be created on all SSRS web farm servers • Service account must be configured to “trust for delegations”

  10. SSRS Security • Generic Report Hosting Server • Create a folder for a department or functional group • Create five security groups corresponding to SSRS security roles and add them to those roles inside the folder • DOMAIN\DeptCode-SSRS-Browser • DOMAIN\DeptCode-SSRS-ContentManager,etc. • Create one security group that contains the other five security groups • DOMAIN\DeptCode-SSRS

  11. SSRS Security • Generic Report Hosting Server (cont.) • Add DOMAIN\DeptCode-SSRS to the Browser role on the Home page • This will allow all members of all AD security groups to see the folder on the Home page • AddDOMAIN\DeptCode-SSRS-ReportBuilder to the SSRS SystemUser role • Users in this group will not be able to launch Report Builder without this

  12. Report Design Demo

  13. Report Design • Popular Report Features • “Smart” cascading parameters • Show only those parameter values from the dimension tables that have related rows in the fact table • Expanding / collapsing rows and columns • Details are initially hidden to produce a “pseudo dashboard” • Locked table/tablix row and column headers • Descriptions remain in place as users scroll report • Background of all locked cells must be set to opaque

  14. Report Design • Popular Report Features (cont.) • Links to related detail reports or web sites • Replace current report with detail report • Open detail report in a new tab / page (URL expression • Open detail report directly in Excel / PDF (URL expression) ="javascript:void(window.open('"+ Globals!ReportServerUrl + "?" + Globals!ReportFolder + "/" + "Report Name"+ "&Param1=" + Parameters!Param1.Value+ "&Param2=" + JOIN(Parameters!Param2.Value,"&Param2=")+ "&rs:Format=Excel"+ "','_blank'))"

  15. Browser Considerations • Internet Explorer • Provides the best overall user experience (duh) • Parameter pull-downs behave like other Microsoft applications • Report state is preserved when using the back button to return from a linked report • Scrolling is a little “jerky” in reports with locked row and column headers • 2083 character URL length limit • Not available for Macintosh, iOS or Unix clients (double-duh)

  16. Browser Considerations • Firefox • Works almost as well as Internet Explorer (with one HUGE exception*) • Scrolling is very smooth for reports with locked row and column headers • Parameter pull-down behavior is a little quirky • Virtually unlimited URL length • *Report state (parameter values, report body, etc.) is completely lost when you use the back button to return from a linked report • Versions available for Windows, Macintosh, iOS, and Linux clients

  17. Browser Considerations • The ASP .NET Report Viewer control • Provides IE-like behavior in non-IE browsers • Report state is preserved when the back button in the report viewer control is used to return from a linked report • Automatic resizing of the report viewer control within the browser page is challenging • Requires complex JavaScript code to be added to a script block on the ASP .NET page

  18. Report Development Options • Visual Studio / BIDS / Data Tools • Many deployment options • Deploy entire projects or individual items • Define multiple deployment targets (test, QA, production) • Select desired target from pull-down list before deploying • Create “Solutions” containing related BI projects • SSIS • SSAS • ASP .NET, etc.

  19. Report Development Options • Report Builder • .NET assembly resides on SSRS server • One-click deployment to client computer via the web • User must be a member of both the “Report Builder” and “System User” roles on the SSRS server • User does not need to download or install Visual Studio / BIDS / Data Tools to edit reports • Report Builder is about 95% the same as the report designer in Visual Studio / BIDS / Data Tools • Only one report can be edited / saved at a time

  20. Oracle Data Sources • Installing Oracle client and ODAC driver software on the development computer • Step 1) Create a system environment variable named TNS_ADMIN that contains the UNC path of a shared network folder containing tnsnames.ora • Extremely Important!!! The user installing the Oracle client software MUST NOT have edit permissions in the shared folder!!!

  21. Oracle Data Sources • Installing Oracle client and ODAC driver software on the development computer • Step 2)Install the full Oracle client for your OS (32-bit or 64-bit) using the “Runtime” option • Step 3) Use SQL*PLUS to test Oracle connectivity • Connect as “USERNAME@DBNAME.WORLD” • Password will not be echoed • If “SQL>” prompt appears, connection is successful

  22. Oracle Data Sources • Installing Oracle client and ODAC driver software on the development computer • Step 4) Install the 32-bit ODAC drivers • Important! Do this on both 32-bit and 64-bit Windows Computers • Step 5) Install the 64-bit ODAC drivers if your computer is running 64-bit Windows • Step 6) Reboot the development computer

  23. Oracle Data Sources • Oracle query development and testing • DbVisualizer is recommended • Works with Oracle, SQL Server and others • Very stable with a nice feature set • Free version is very usable, but pay version contains features that make it worth the money (IMHO)

  24. Miscellaneous Best Practices • Match parameter and dataset column names • Create one denormalized view that contains all the data required to populate both “smart” parameter lists and report bodies • Create parameterizedstored procedures that return the data for individual parameter lists and report bodies if possible • Use SSAS cubes or tabular data models as data sources whenever possible

  25. Planning for the Future • Next Generation Architecture • SharePoint 2013 server • Integrated Reporting Services • PerformancePoint dashboards • PowerPivot and Power View reports • Shared PowerPivot and SSAS tabular data models • Support for a wide variety of mobile clients • Dedicated SSAS 2012 server with TONS of memory • Central SSRS report development server • More support for Excel-based self service reporting

More Related