Under the Hood

One of the major themes in SSRS 2008 is scalability. A significant effort will be made to ensure that SSRS scales well with large reports. Why is this needed? Recall that in SSRS 2005 (and 2000), report processing is instance-based. This means that the report engine would process the entire report as a snapshot, including textbox values, styles, etc. As a result, SSRS 2005 reports are memory-bound. In general, the memory consumed by SSRS 2005 is proportional to the size of the report. The bigger the report, the more memory it would take. Therefore, large reports won’t scale well.

In contrast, the SSRS 2008 processing engine processes the reports on-demand. It pre-computes and saves only certain invariants, such as grouping, sorting, filtering, and aggregates. However, textbox values are calculated on-demand every time the containing page is rendered. To test this, drop a textbox on the report and set its value to =Now(). In SSRS 2005, when you page back and forth, the textbox value will not change. In contrast, in SSRS 2008, the textbox value would change to the most current date each time the containing page is rendered.

As a result of the on-demand processing model, the in-memory presentation of the report is much smaller. If you want to learn more about the engine improvements in Katmai, I highly recommend the Chris Baldwin’s excellent Enterprise Scale Report Engine webcast. At this point, you may wonder how much memory the new engine could save. I tested print preview for an existing 1,270 report with the old and new engine and here are the results.

 SQL Server 2005

Time (s)

Memory SQL (MB)

Memory RS (MB)

TFP (time to first page)

262

130

240

TLP (time to last page)

610

207

312

SQL Server 2008
TFP

218

56

95

TLP

430

44

95

Improvement (TLP)

30%

79%

70%

As you can see, in this case the new engine takes 70% less memory which is a pretty remarkable improvement. This is not to encourage you to author such monster reports of course. However, if you have to, for audit, regulatory, or whatever reasons, the new engine should be able to help.

Identifying and Resolving MDX Query Performance Bottlenecks Whitepaper

Performance is the most common incentive for using Analysis Services. Yet, despite the advances in the SSAS management tools, optimizing the MDX query performance is still considered as “black magic” by many. Graphical optimization tools similar to the SQL query optimizer and query showplan would definitely help but they are not expected anytime soon. Meanwhile, Microsoft has just published a 60-page whitepaper Identifying and Resolving MDX Query Performance Bottlenecks in SQL Server 2005 Analysis Services by Carl Rabeler and Eric Jacobsen. At first sight this resource looks like a must-read for anyone looking for tips to make that query execute a few seconds faster. From the article summary:

“To improve the performance of an individual MDX query that is performing poorly, you must first identify the source(s) of the performance bottlenecks in the execution of the query. This requires that you understand how to use Windows and SQL Server 2005 Analysis Services troubleshooting tools. This best practices article provides information about available troubleshooting tools and demonstrates how to use the most common of these tools to identify and resolve MDX query performance bottlenecks.”

Microsoft Launches Tafiti

Microsoft launches a preview of a cool search portal dubbed Tafiti to bring the web search to a new level and give Google a run for its money. From FAQ “Tafiti, which means ‘do research’ in Swahili, is an experimental search front-end from Microsoft, designed to help people use the Web for research projects that span multiple search queries and sessions by helping visualize, store, and share research results. Tafiti uses both Microsoft Silverlight and Live Search to explore the intersection of richer experiences on the Web and the increasing specialization of search.”

The Silverlight-based UI looks pretty sleek.

Charting the Future

It is old news by now that Microsoft has acquired the intellectual rights to the Dundas Reporting Services controls, including the Dundas chart, map, gauge, and calendar. At this point, it is not known if all controls will make it to Katmai but the chart will definitely will. The July CTP (CTP4) includes a preliminary version of the Dundas chart that has been converted from a custom report item to a native region. Many of the properties are not hooked yet but you can witness some of the new enhancements. Although somewhat unpolished, the following report demonstrates some of these features.

081907_0314_Chartingthe1

The report features two charts in one – a column sales chart and a scattered profit chart. They are projected on two value axes. Having a secondary axis wasn’t supported by SSRS 2005. Also, the first value axis has axis breaks. Since the Bikes sales exceed by far the sales of the other categories, an axis break on Sales is used to prevent the Bikes sales to eclipse the other categories. This wasn’t possible before. Now, it is matter of enabling a checkbox. The chart region will compute the axis break automatically for you.

Of course, this just scratches the surface. The Dundas chart control is one of most complex and powerful controls on the market. The bottom line is that SSRS 2008 will definitively pretty up your reports and dashboards.

RDL Object Model on the Horizon

As I mentioned in a previous blog, SQL Server 2008 will probably include an RDL Object Model. This is great news for developers who generate report definitions programmatically. No more custom RDL object models as the one I talked about during my TechEd presentation. The early incarnation of the promised object model is included in the July CTP (CTP4) and resides in the \Microsoft SQL Server\MSSQL.3\Reporting Services\ReportServer\bin\Microsoft.ReportingServices.RdlObjectModel.dll assembly.

DISCLAIMER Before rejoicing too much, recall the usual disclaimer that everything is in a flux and a subject to change. Although here, the RDL Object Model may very well disappear in the final bits.

Note that I disclaimed myself let me introduce you to the RDL Object Model (don’t try to find it in BOL; long live .NET Reflector!).

using System;

using System.IO;

using Microsoft.ReportingServices.RdlObjectModel;

using Microsoft.ReportingServices.RdlObjectModel.Serialization;

namespace RDL

{

class Program

{

static void Main(string[] args)

{

string idef = @”C:\Reports\Sales by Product.rdl”; // input report in RDL 2008 format

string odef = @”C:\Reports\Sales by Product1.rdl”; // output report in RDL 2008 format

Report report = null;

RdlSerializer serializer;

if (!File.Exists(idef)) return;

// deserialize from disk

using (FileStream fs = File.OpenRead(idef))

{

serializer = new RdlSerializer();

report = serializer.Deserialize(fs);

}

report.Author = “Teo Lachev”;

report.Description = “RDL Object Demo”;

// TODO: use and abuse RDL as you wish

// serialize to disk

using (FileStream os = new FileStream(odef, FileMode.Create))

{

serializer.Serialize(os, report);

}

}

}

}

As you can see, using the RDL Object Model is simple. Once you add the reference to (\Microsoft SQL Server\MSRS10.MSSQLSERVER\Reporting Services\ReportServer\bin\Microsoft.ReportingServices.RdlObjectModel.dll, you are work with RDL in object-oriented way. You use the RDLSerializer class to load a report definition (Deserialize method) or get RDL from a report object (Serialize method). Both methods support various overloads to read/write from/to stream, XmlReader, and more. I load an existing Sales by Product report into the Report object using the RDLSerializer.Deserialize() method. Note that it must be saved in the SSRS 2008 format. You need to use the stand-alone Report Designer to do so. If you use the SSRS 2005 format you will get an exception because you will need to upgrade to RDL 2008 (see next paragraph about how to upgrade RDL). From there, I write the report object back to disk as a Sales by Product1.rdl file. What could be easier? Compare this with XmlDom programming and you will start seeing how the RDL Object Model can make your life easier.

There are also methods for upgrading RDL to SSRS 2008 (Microsoft.ReportingServices.ReportProcessing.RDLUpgrader .UpgradeToCurrent) and SSRS 2005 (Microsoft.ReportingServices.ReportProcessing.RDLUpgrader .UpgradeTo2005) formats.

An RDL object model has been long due on my wish list. Kudos to the SSRS team for materializing it.

How to Get Extended Properties with SSAS OLE DB Provider

Many report developers find the SSAS data provider too restrictive for authoring reports from UDM. Instead, they replace it with the native Microsoft OLE DB Provider for Analysis Services 9.0 which gives them maximum flexibility at the expense of convenience (the query has to be hand-generated, parameters are not supported, extended properties are not available, etc).

By default, the OLE DB provider doesn’t return the extended cell properties, such as format and color settings. Thanks to Robert Bruckner from the SSRS team, here is a precious tip to get the extended properties:

  1. Append Extended Properties=”ReturnCellProperties=true” to the OLE DB connection string of your data source, e.g.:
    Provider=MSOLAP.3;Data Source=localhost;Initial Catalog=”Adventure Works DW”;Extended Properties=”ReturnCellProperties=true”
  2. Request the cell properties in your MDX query as the SSAS provider does, e.g.:

SELECT NON EMPTY { [Measures].[Internet Sales Amount], … } ON COLUMNS,
NON EMPTY { [Sales Territory].[Sales Territory Group].[Sales Territory Group].ALLMEMBERS , …. }
FROM [Adventure Works]
CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

  1. Execute the query. The OLE DB Provider returns the extended properties as fields in the dataset.

You will notice that the OLE DB provider returns the cell colors as numeric values. To convert the SSAS numeric colors to RGB colors for use on your report, you can use this helper function in your report.

int intValue = ConvertToInt32(value, out isValid);

if (isValid) {

return String.Format(“#{0:x2}{1:x2}{2:x2}”,

(intValue & 0x000000ff),

(intValue >> 8) & 0x000000ff,

(intValue >> 16) & 0x000000ff);

}

Tablix – The Crown Jewel

One of most important enhancements coming up in SQL Server 2008 Reporting Services is the new tablix region. In fact, I dare to predict that many folks will upgrade to SSRS 2008 just to get Tablix. What’s tablix anyway?

Tablix = Table + Matrix

So, tablix combines the flexibility of the table region and the crosstab reporting features of the matrix region. Actually, a tablix is table, matrix, and list all in one but I guess the SSRS team decided to keep the name short. Don’t be fooled by the old toolbar buttons because they just provide entry points to the tablix region. If you click the Matrix button, you will get a tablix region preconfigured for crosstab reporting, when you click the Table button you get a table report, and List button will give you a free-form Tablix. So, this is simple. In SSRS 2008, tablix powers all reports.

Why is Tablix so cool? Because it lifts many of the matrix limitations and add features not possible before. Take for example the following report that demonstrates a couple of the new Tablix features.

081307_0232_TablixTheCr1

First, Tablix lets you have stepped columns with crosstab reports. For example, Product Category and Product Subcategory share the same column. This may not sound very exciting but it wasn’t that easy to implement with the old matrix region.

Second, Tablix supports side-by-side crosstab sections. For example, the report has by year and by region sections side by side. This wasn’t possible with Matrix.

Finally, the snapshot doesn’t show this but you have independent group aggregates with crosstab reports. This means that moving to SSRS 2008 you can forget about the InScope() function and all limitations associated with it and matrix-based reports. These limitations made me abandon the matrix region in one of my projects and replace it with programmatically-generated “pseudo” crosstab reports that used the table region.

To take Tablix for a spin, use the standalone Report Designer. Or, open an existing SSRS report in the standalone Report Designer. As mentioned in my previous post, the VS.NET Report Designer doesn’t support Tablix in CTP4.

Matrix is dead, long live Tablix!

The New Kid on the Block (aka Stand-alone Report Designer)

The need for a stand-alone Report Designer is real. True, developers, including myself, enjoyed the VS.NET Report Designer. But novice users were overwhelmed with the complexity of the VS.NET IDE environment. In addition, not all IT shops rejoiced over the idea to install VS.NET or BIDS just to author reports. Enters the SSRS 2008 stand-alone report designer.

080707_1113_TheNewKidon1

As its name suggests, you run this designer completely outside VS.NET. In fact, in CTP4, VS.NET has not been yet integrated with the new designer (it still uses the RS 2005 designer). Therefore, to get the new RDL enhancements in CTP 4, such as tablix, you need to use the stand-alone report designer.

Note that stand-alone doesn’t mean embeddable. While a future release make this possible, it is unlikely that you will be able to embed the SSRS 2008 designer in custom .NET application.

You launch the stand-alone report designer from the Report Designer Preview link in the Reporting Services program group. As you would immediately see, its UI resembles the Report Builder UI. There are a few welcome enhancements. First, there is a new Data Window which shows the Build-in Fields, Parameters, Images, and Datasets all in one place. Next, the Group Task Panes shows conveniently the row and column groups and minimizes the steps required to set up a new group. With SSRS 2005, you had to create a new row in the table region and configure it using the Group properties dialog box. Moving forward, you can just drag a dataset field below a given group in the pane to create a new subgroup.

Clicking inside a textbox shows a Field Picker icon. When you click on it, you will see a dropdown of the dataset fields and conveniently pick the field instead of dragging it from the dataset fields to the textbox. While we are on this subject, you will notice that for the sake of brevity the Report Designer replaces the field expressions with tokens (enclosed with square brackets), such as [Sum(Sales)] instead of =Sum(Fields!Sales.Value). Clicking inside the textbox gives you access to the actual expression.

There is also a new Zoom pane which lets you zoom the report in and out. As you would expect from an early build, many features are not hooked up yet. For example, report preview requires uploading the report to the server.

Oh, yes… the thing in the middle is the new Tablix region but this is a topic for another post.

The Perfect Host

This is my first blog about SSRS 2008. The July CTP (CTP4) of SQL Server 2008 (aka Katmai) includes the new hosting model of Reporting Services. Gone is the dependency to IIS and all the management headaches associated with it. Instead, the SSRS Windows service pulls a nice trick by hosting the http.sys kernel-mode device driver which listens for HTTP requests. Behind the scenes, the SSRS Windows service hosts application domains for the Report Manager and Report Server and forwards the incoming requests to them.

You reserve URL addresses for Report Manager and Report Server by using the Reporting Services Configuration utility. If you have ever set up a web site in IIS, you know everything you need to reserve an URL address. The URL address consists of IP address, TCP port, and vroot. You can specify any unreserved port (in Vista and Windows Server 2003+ you can have SSRS and another application listen on the same port, e.g; IIS and Report Server both listening on port 80). For example, if you reserve port 8080 and accept the defaults, the report server URL address will be http://<servername>:8080/ReportServer. You can optionally use the Advanced tab to specify additional settings, such as host headers. When the SSRS Windows service starts it registers the URL addresses with http.sys. Http.sys listens for http requests and forwards them accordingly.

080407_1538_ThePerfectH1

While we are still on the Reporting Services Configuration topic, here is a nice tip. CTP4 doesn’t officially support upgrading from SSRS 2005. However, you may need to test old reports with SSRS 2008. You can upgrade an old catalog by using the Database tab and pointing to a SQL Server 2005 instance that hosts the SSRS 2005 catalog. The Reporting Services Configuration utility will upgrade the catalog in place. Later, you can back up and restore the catalog on the SQL Server 2008 instance if you need to remove the dependency on the SQL Server 2005 instance.