You may have heard by now that the report processing engine of Reporting Services 2008 works significantly different underneath the covers, compared to previous releases. While I plan to dive into more technical details in future postings, I wanted to provide a few examples upfront explaining why you as a report author or end user might care:
- On-demand processing of reports with many datasets and data regions.
For instance, if a user only views page one through three, and those pages only refer to data regions / expressions referencing DataSet1, all other data regions bound to other datasets are not necessarily processed immediately. Processing can be delayed until a user navigates to a particular page showing that data region or hitting an expression that references information from those other datasets.
- Reports with heavy usage of interactivity can be significantly faster.
The main reason for the speedup is that certain interactivity related calculations (e.g. toggles, drillthrough actions) are now performed on-the-fly as pages are viewed by the user, instead of having to do these upfront before the first page is shown.
- Faster page-by-page navigation when viewing reports interactively.
The new architecture allows us to cache a few bytes per page that enable quick restart of pagination of a partially/fully paginated report. As a result, navigating to a page of a report which was already previously accessed during the same session is now faster, particularly the larger the report in size. You can verify this with a report with several thousand pages. Navigate to the last page of the report (which triggers paginating the entire report), and then try to randomly navigate to any page within the report. Reporting Services 2008 will be faster.
- Smaller memory footprint.
The memory footprint of requests is generally smaller with the on-demand processing engine, particularly for very large reports. However on a system with low load, you may not see any significant working set difference in task manager due to various types of caches utilized within the processing engine.
So what is the main architectural difference with on-demand report processing?
Unlike the RS 2005 processing engine that processed the report as a snapshot evaluating all expressions exactly once upfront (including textbox values, style expressions, etc.), the RS 2008 on-demand processing engine pre-computes and persists only certain invariants, such as grouping, sorting, filtering, aggregates, and report/group variables. All other expressions (style expressions, textbox values) are evaluated on-demand based on the invariants, at the time a rendering extension requests a specific page containing those values. Hence, you could also call it rendering-driven on-demand processing engine.
You probably now think, thanks that’s nice; and where is the catch? J
Well, most expressions in a report, particularly textbox values and style expressions, are evaluated on-demand in Reporting Services 2008 as you navigate to a particular page. If you go there multiple times, it might be re-evaluated again. Try this to see the effect of on-demand processing:
- Generate a multi-page report with a textbox value of =Now on the first page.
- View the first page in HTML and note the textbox timestamp value.
- Navigate to the second page, then go back to the first page.
- Note that the textbox was re-evaluated as the first page was rendered again in RS 2008. The timestamp changed.
If you do not want this on-demand behavior in particular cases (e.g. time-dependent calculations, custom code, or expensive calculations requiring caching), you should use so-called Report Variables or Group Variables, and then reference the variable value in the report (e.g. =Variables!V1.Value). Variables are covered in more detail in a previous posting. If it is just about consistent time stamps, you should instead use =Globals!ExecutionTime, regardless of the version of Reporting Services (more info on global built-in collections is available on MSDN).
Among other topics, future postings in the on-demand processing series will also provide report design tips to better leverage the new processing engine in RS 2008.
If you had Visual Studio 2008 RTM (or a Beta of SP1) already installed on your machine, the installation of SQL Server 2008 requires to install the final SP1 build of Visual Studio 2008 as a prerequisite (explained in more detail here). The good news is that Visual Studio 2008 SP1 and .NET Framework 3.5 SP1 are now available as well.
On a related topic, SQL Server 2008 Express is available now for download. Details about the feature sets of the current Express Edition and the two upcoming, further enhanced Express Editions can be found in this posting on the Data Platform Insider blog.
One of the questions I seem to answer somewhat frequently is related to dataset executions in Reporting Services.
A Reporting Services report can contain multiple dataset definitions. By default, datasets are executed in parallel. Now, in some situations you may not want this parallel query execution behavior - for example:
- Your data source system cannot handle many too parallel connections.
- You are executing queries with side-effects (e.g. update statements) and need a particular sequence of dataset executions (e.g. the first datasets returns one set of data and prepares temporary tables, that are retrieved by a second dataset query)
There are several ways of serializing dataset executions.
Serializing dataset executions for a particular data source:
Since the initial release of Reporting Services, there is a RDL setting for serializing dataset executions on the same data source connection (and therefore only using one data source connection for multiple datasets).
To apply this setting, open the data source dialog in report designer, and select the "Use Single Transaction" checkbox. Once selected, datasets that use the same data source are no longer executed in parallel. They are also executed as a transaction, i.e. if any of the queries fails to execute, the entire transaction is rolled back.
The order of the dataset execution sequence is determined by the top-down order of the dataset appearance in the RDL file, which also corresponds to the order shown in report designer.
Serializing dataset executions when using multiple data source:
Note that datasets using different data sources will still be executed in parallel; only datasets of the same data source are serialized when using the single transaction setting. If you need to chain dataset executions across different data sources, there are still other options to consider.
For example, if the source databases of your data sources all reside on the same SQL Server instance, you could use just one data source to connect (with single transaction turned on) and then use the three-part name (catalog.schema.object_name) to execute queries or invoke stored procedures in different databases.
Another option to consider is the linked server feature of SQL Server, and then use the four-part name (linked_server_name.catalog.schema.object_name) to execute queries. However, make sure to carefully read the documentation on linked servers to understand its performance and connection credential implications.
SQL Server 2008 was released to manufacturing (RTM) today (see press release).
You can download it on MSDN Subscriber Downloads and TechNet Subscriber Downloads. Either search for SQL Server 2008 or navigate under Servers then to SQL Server 2008.
SQL Server 2008 is a big release and includes very significant changes and enhancements in Reporting Services 2008. Over the next months, you can expect blog postings with drill-downs into particular functionality areas. For now, you may want to take a closer look at some of the major new functionality areas in Reporting Services 2008:
-
Improved Report Designer in Business Intelligence Development Studio
-
Report Builder 2.0 Stand-alone report designer; more details about the release plan for Report Builder 2.0 are available in this
blog posting.
-
Author reports with any structure, using the unique Tablix grouping and layout capabilities.
-
Leverage the enhanced Textbox (aka "RichText") to define mixed formatting within the same textbox. In addition, HTML strings of text can be imported into the report from a database or other source.
-
Enhanced
scalability and performance; more details covered in a previous
posting.
-
Render reports to Microsoft Office Word format.
-
SharePoint Integrated Mode
Integrate Reporting Services with Microsoft Office SharePoint Server 2007 for central delivery and management of business insight, or run it stand-alone in native mode.
-
Data Visualization
Gain insight into complex sets of data by displaying data graphically with enhanced visualization capabilities (chart, gauge).
-
New on-demand report processing and rendering architecture, including a new RenderingObjectModel.
-
New and enhanced data source types (Teradata).
-
New memory configuration options in the report server. Parts of the underlying infrastructure rely on the so-called SQLOS, which has been used by the SQL Server database for several major releases.
-
No IIS dependency. HTTP.SYS, and the CLR including ASP.NET are now directly hosted within the report server.
-
New and enhanced Reporting Services Configuration Tool.
Enjoy!
Introduction
SQL Server 2008 Reporting Services (SSRS 2008) features an on-demand report processing engine. This on-demand architecture has a number of key advantages over the processing engine design that existed in previous major releases. The most significant benefits are vast improvements to report engine scalability and performance (you can read a bit more about it here). Because of this fundamental change from previous versions, there are some specific design patterns that have changed. This post is a discussion of one scenario that, due to the new processing engine, requires a different design pattern in 2008 than was required in 2005 and 2000.
Special thanks to my esteemed colleagues Chris Baldwin and Chris Hays, who helped with the contents of this posting. Note: screenshots and how-to steps in this post are based on the currently available SQL Server 2008 RC0 release of Report Builder 2.0. Future releases of this product may change. The attachment at the bottom of this posting contains both, a 2005 version and a 2008 version of the final reports. The reports are based on the Northwind sample database (download link).
On-Demand Report Processing
The new processing engine in Reporting Services 2008 still retrieves datasets upfront, but only pre-computes certain invariants, such as grouping, sorting, filter expressions, aggregates, subreport parameters and queries. Everything else are "on-demand" evaluated expressions; most notably, textbox values, and style expressions.
Furthermore, the processing engine now exposes a cursor-based report structure as so-called RenderingObjectModel. Rendering extensions, responsible for translating the processed report to the desired output format, traverse the report using a hierarchy of RenderingObjectModel cursors. This is in contrast to the processing engine in 2005 and 2000 in which the entire report was fully processed upfront. A couple implications of this on-demand model are that
a) objects are evaluated hierarchically throughout the report,
b) hidden textboxes are not evaluated, and
c) the concept of Report and Group Variables has been introduced
Report and Group Variables
In Reporting Services 2008 / RDL 2008/01 namespace, one can declare variables that are global throughout the report or local to particular group scopes and refer to them in expressions. Report and group variables can only be set/initialized once and have a read-only semantics.
Typical use cases for variables include:
- Caching values:
Report/group variables can be used to make an expensive call to an external assembly once, cache the result, and then reference the variable value from other expressions in the report.
- Time-dependent calculations:
For example, retrieve a currency conversion value and store it in a report/group variable to be applied consistently during the lifetime of the rendering session.
- Specific execution-order semantics:
Achieve particular expression execution order semantics that is independent of the rendering extension used later for generating the report output.
The latter use case of group variables will be discussed in more detail in the remainder of this blog posting to implement custom aggregation in a Reporting Services 2008 report.
Custom Aggregate Scenario
The scenario discussed is one where a report author implements a custom aggregate, illustrated by an implementation of a Median function.
A common pattern for implementing a custom aggregate such as Median in Reporting Services 2005 is like this.
With the custom code for GetMedian and AddValue as follows:
Dim values As System.Collections.ArrayList
Function AddValue(ByVal newValue As Decimal)
If (values Is Nothing) Then
values = New System.Collections.ArrayList()
End If
values.Add(newValue)
End Function
Function GetMedian() As Decimal
Dim count As Integer = values.Count
If (count > 0) Then
values.Sort()
GetMedian = values(count / 2)
End If
End Function
What happens here in SSRS 2005 is that for each instance of the detail row, the value gets passed to AddValue() and then added to the values ArrayList. A textbox in the Table header, then, makes a call to GetMedian() which performs a calculation on the values in the ArrayList, and displays it.
It's important to note that this wasn't exactly supported in SSRS 2005 and it wouldn't even work properly in most cases. For example, if you were to add end-user sorting to the table, then the processing would go through a different code path that would evaluate the headers before the details. This would mean that the GetMedian() function would be called before AddValue has a chance to add any values are added to the ArrayList. It just so happens that in this particular case, when there is no end-user sort, the details are processed first.
Whether or not it was officially supported, a number of people got this to work and are relying on this behavior. In order for the same pattern to work in SSRS 2008, the report needs to be slightly redesigned. Detailed, step-by-step, instructions are provided below. Note that the pattern of using group variables outlined below is not limited to custom aggregation, but can be expanded into more complex solutions. We can show you the path and the pattern, but you will have to apply it to your unique situation. YMMV.
Implementing this in SSRS 2008: Step-by-Step
The report needs to be slightly revamped in 2008 in order for this to work. The custom code itself, however, doesn't have to change at all. This is going to be a step-by-step procedure by which you can port this pattern from your 2005 report to 2008. Note that the attachment at the bottom of this post contains a 2005 report and a 2008 report, both implementating this custom aggregation approach.
-
Open the report in Business Intelligence Development Studio (BIDS) 2008 or Report Builder 2.0. When you open the 2005 report in a 2008-based tool, the RDL schema will be automatically upgraded to 2008. This is what you'll see in Report Builder 2.0:
-
In on-demand processing, items are generally evaluated from the top-down. This means that in order to add the values of your detail rows into the ArrayList from which you will calculate the Median value, you need to add a "dummy" tablix to your report with its own detail row. This row can be hidden, as it's used solely for calculation purposes. Specifically, its purpose is to make calls to the AddValue function to populate the ArrayList. So that this table can "share" values with the table that will be visually presented in the report, it they both need to be part of the same table. Add a single static row above the header row in the table. Right-click in the blue Product Name cell, and select Insert Row > Above. In the newly inserted row, merge all of the cells. This is what you should see:
-
Click the new cell (as shown above), and from the Insert tab on the Ribbon, select Table. Delete the top row from the newly created table, and merge the cells together:
-
Select the detail group for this new inner table, and set the Hidden property to True. Since this is used only for calculations, it doesn't need to be visible in the rendered output of the report:
-
Now, you need to add the call to the AddValues function within the context of the nested table. As I mentioned above, a hidden textbox's value will not be evaluated due to the new on-demand processing architecture. In order to make sure the call to AddValues is made regardless of the visibility of the group, add it as a group Variable:
-
The original rows of the table need to be slightly restructured so that the original row functions as a group header.
Step 1: Right click in the Product Name cell and select Add Group > Row group > Parent group
Step 2: In the Tablix group dialog: Group by: 0 (constant value); Select Add Group header
-
Select the newly created group header textbox (with the 0 in it), right click, and select delete column. You should then have this:
-
Copy the contents of the blue cells into the row below it, so that it's inside the group. Then, delete the row from which the values were copied. Re-add the blue background to the other row if you want. Now you should have this:
-
Now, in order to properly retrieve the calculated Median value, you need to add the call to GetMedian into a group Variable for the group that contains the header where you want to value to be displayed. Select the group from the grouping pane, and add this group Variable:
A few months ago, leading up to the release of RC0 of SQL Server 2008, I was involved in working with the SQL Server Customer Advisory Team. They were investigating and comparing the scalability of Reporting Services 2005 and Reporting Services 2008, using common customer report scenarios and workloads. A more detailed whitepaper is planned for the future, which will also provide the underlying code and test framework so that you could plug-in your own reports, define your workload, and run these tests on your particular hardware and environment.
In the meantime, if you are thinking of moving to Reporting Services 2008, I’d strongly encourage you to read the following technical note titled Scaling Up Reporting Services 2008 vs. Reporting Services 2005: Lessons Learned.
The key take away from the Technical Note is that SQL 2008 Reporting Services can scale-up better than SQL 2005 Reporting Services. Summarizing the results, quoting from the technical note:
1) "Reporting Services 2008 was able to respond to 3–4 times the total number of users and their requests on the same hardware without HTTP 503 Service Is Unavailable errors compared with Reporting Services 2005, regardless of the type of renderer."
2) "With our test workload, Reporting Services 2008 consistently outperformed Reporting Services 2005 with the PDF and XLS renderers on the four-processor, quad-core hardware platform (16 cores) both in terms of response time and in terms of total throughput."
The SQL Server Customer Advisory Team did a great job of pulling together report scenarios and workloads, testing on three different hardware platforms, testing a few configuration options, and identifying key results. These results validate some of the quite significant work we have been doing in Reporting Services 2008. Furthermore, note that the scalability testing was performed with a non-optimized pre-release RC0 build of Reporting Services – an RTM deployment of Reporting Services 2008 is expected to show even slightly improved results.
The underlying architecture has changed considerably in Reporting Services 2008 (e.g. removing IIS dependency), and we added a number of quite significant new capabilities in the report processing engine and rendering extensions (e.g. tablix with arbitrary grouping and flexible layout support, vastly improved scalability support). We have been doing ongoing and extensive performance, throughput, and scalability testing internally within the Reporting Services team – so I guess some of these great results shown in the technical note weren’t all that surprising to members of the RS team. There was also anecdotal evidence and excitement by customers in blog postings and other forums, impressed with some of the improvements in Reporting Services that were visible as early as with the public SQL Server 2008 CTP release back in July 2007.
In future postings on this blog, I plan to cover important tips and tricks related to diagnosing and improving performance of complex reports (e.g. involving custom code) in RS 2008. This will hopefully enable you to even better take advantage of some of the underlying architecture changes in the RS 2008 report processing engine, such as dynamic on-demand processing.
I have been putting this off for a while. But not out of concern with sharing myself in public - I've been posting on the net in various forums for years. Anyone good with a search engine can find several thousand of my postings on public SQL Server newsgroups and the MSDN forums over the years. You might recognize my name in the context of SQL Server Reporting Services, from Reporting Services 2005 whitepapers (e.g. getting more out of charts, report design tips and tricks), past scientific conference/journal papers, and you may have seen me speaking at database conferences.
I joined the SQL Server Reporting Services development team in early 2003, at a time when the product was just publicly announced, and Beta 1 of Reporting Services 2000 came out. My core areas have been the design and development of the data and report processing engine. The engine is running inside server and client components of Reporting Services, as well as in the Visual Studio Report Viewer controls in local mode. Since my main focus over the years has been the Report Definition Language (RDL), data processing, report processing, data visualization, and performance/scalability, the majority of my postings answered questions in those areas.
In the past, I have frequently contributed material to other blogs as well. Brian Welcker, Chris Hays, Teo Lachev, and others have done a great job of transforming some of my Microsoft internal and public newsgroup/forum postings into blog postings. Some bloggers even offered me an ‘exclusive contract’ so that I just put together contents and they will take full responsibility of publishing it on their own blog ‘exclusively and free of charge’. Right, Lukasz? ;-)
Anyway, with the RTM release of SQL Server 2008 imminent, I realized there are so many exciting new features, tips, and tricks for Reporting Services 2008 to share, that I should really start my own blog. The focus of this blog is going to be somewhat technical. Occasionally it will cover what some readers might consider quite advanced techniques that one will hardly find explained in other blogs or articles. From the questions I have been seeing and answering e.g. on the SSRS Katmai forum over the past few years, I’m confident there is a need for an ‘Advanced Reporting Services’ blog, and the information provided here will be highly useful to a broader audience.
I hope you are going to enjoy this blog. I can show you the path to some advanced Reporting Services techniques. You are the one who has to walk it.
-- Robert
And not to forget the obligatory disclaimer :-)
This blog is provided 'AS IS' with no warranties, and confers no rights. All rights reserved. Some assembly required. Batteries not included. Your mileage may vary. No user serviceable parts inside.
