Posts

Maintaining State in Reporting Services 2008

Sometimes, more advanced reporting needs may require maintaining state using custom code. Recently I had to implement a rather involved report consisting of two sections: a detail section that used a recursive sum, Sum(Fields!SomeField.Value, , True), and a summary section with additional calculations which would reference some of the aggregated values in the detail section. Since Reporting Services is not Excel, you cannot reference arbitrary fields on a report. So, I had to cache the values from the detail section in a hashtable using custom code so the summary section could obtain these values when needed. The following embedded code gets the job done:

Friend Shared _hashtable As New System.Collections.Hashtable()

Function GetKey(ByVal id as Long, ByVal column as String) as String

return Report.User!UserID & id & column

End Function

Function AddValue(ByVal id as Long, ByVal column as String, ByVal value As Object) As Object

Dim key as String = GetKey(id, column)

If _hashtable.ContainsKey(key)=False Then

            _hashtable.Add(key, value)

End If

Return value

End Function

Function GetValue(ByVal id as Long, ByVal column as String) As Object

Dim key as String = GetKey(id, column)

If _hashtable.ContainsKey(key) Then

    Return _hashtable(key)

End If

Return Nothing

End Function

Protected Overrides Sub OnInit()

Dim keys As String() = New String(_hashtable.Keys.Count – 1) {}

_hashtable.Keys.CopyTo(keys, 0)

For Each key as String In keys

         If key.StartsWith(Report.User!UserID) Then

         _hashtable.Remove(key)

        End if

Next key

End Sub

In my scenario, tablix cells in the detail section would call the AddValue function to cache the dataset field values. Then, cells in the summary section would call GetValue to obtain these cached values. This technique is not new and you’ve probably used it to solve similar challenges.

What’s new is that moving to Reporting Services 2008 you may need to revisit your custom code and make changes. First, in prior version of Reporting Services, you probably declared the hashtable variable as an instance variable and that worked just fine. However, Reporting Services 2008 introduced a new on-demand processing engine where each page is processed separately. Consequently, you may find that the report “loses” values. Specifically, if the report fits on one page, everything will work as expected. But if the report spills to more pages, the hashtable collection will lose the values loaded on the first page. The solution is to declare the hashtable object as a static (Shared in Visual Basic) object so it survives paging.

Friend Shared _hashtable As New System.Collections.Hashtable()

But because a static object is shared by all users running the report, you need to make the hashtable key user-specific, such as by using the User!UserID variable which returns the user identity.

Function GetKey(ByVal id as Long, ByVal column as String) as String

return Report.User!UserID & id & column

End Function

Are we done? Well, there is a nasty gotcha that you need to be aware of. If the report takes parameters, you will surprised to find out that the custom code returns the cached values from the first report run and changing the parameters (report data) doesn’t change the values in these cells that call the GetValue function. To make the things even more confusing, testing the report in the BIDS Report Designer or Report Builder 2.0 will work just fine. But you will get the above issue after you deploy and run the server report, such as when you request the report in Report Manager or SharePoint.

What’s going on? As it turns out, the hashtable object survives report requests as a result of parameter changes. Consequently, the code that checks if the hashtable key exists will find the key when the report is reposted and it will not add the new value.

If _hashtable.ContainsKey(key)=False Then

_hashtable.Add(key, value)

End If

The solution is to clear the user-specific hashtable items each time the report is run. This takes place in the OnInit method. The OnInit method is a special method which gets executed before the report is processed. You can use this method to do initialization tasks or, in this case, clear state held in global variables.

Are we there yet? Almost. As the reader samsonfr pointed out in a blog comment, we’ll need to make this code thread-safe because the chances are that multiple users may be running the report at the same time so concurrent threads may need to write and read to/from the static hashtable variable at the same time. As the Hashtable documentation explains “Hashtable is thread safe for use by multiple reader threads and a single writing thread. It is thread safe for multi-thread use when only one of the threads perform write (update) operations, which allows for lock-free reads provided that the writers are serialized to the Hashtable. To support multiple writers all operations on the Hashtable must be done through the wrapper returned by the Synchronized method, provided that there are no threads reading the Hashtable object. Enumerating through a collection is intrinsically not a thread safe procedure. Even when a collection is synchronized, other threads can still modify the collection, which causes the enumerator to throw an exception. To guarantee thread safety during enumeration, you can either lock the collection during the entire enumeration or catch the exceptions resulting from changes made by other threads.”

Although the code adds and removes user-specific values only (the collection key uses User!UserID), Robert Bruckner from the SSRS dev team clarified

“Everytime you have code like this in a multi-threaded environment, there is potential for a race-condition:

if (!_hashtable.ContainsKey(“abc”)) _hashtable.Add(…)

In this case you have to take a lock to ensure ContainsKey and Add are run as an atomic piece of code.”

Keeping this in mind, my proposal for making the code thread-safe code follows (concurrent experts, please comment if you find anything substantially wrong with my attempt to make a hashtable access thread-safe).

Friend Shared _hashtable As New System.Collections.Hashtable()

Dim _sht as System.Collections.Hashtable = System.Collections.Hashtable.Synchronized(_hashtable)

Function GetKey(ByVal id as Long, ByVal column as String) as String

return Report.User!UserID & id & column

End Function

Function AddValue(ByVal id as Long, ByVal column as String, ByVal value As Object) As Object

If id = -1 or id = -2 or id=-3 Then

Dim key as String = GetKey(id, column)

If _sht.ContainsKey(key)=False Then

    _sht.Add(key, value)

End If

End If

Return value

End Function

Function GetValue(ByVal id as Long, ByVal column as String) As Object

Dim key as String = GetKey(id, column)

If _sht.ContainsKey(key) Then

    Return _sht(key)

End If

Return Nothing

End Function

Protected Overrides Sub OnInit()

SyncLock _hashtable.SyncRoot

Dim keys As String() = New String(_hashtable.Keys.Count – 1) {}

_hashtable.Keys.CopyTo(keys, 0)

For Each key as String In keys

     If key.StartsWith(Report.User!UserID) Then

         _hashtable.Remove(key)

    End if

Next key

End SyncLock

End Sub

Transmissions from TechEd USA 2009 (Day 2)

I started the day by attending the Donald Farmer and Daniel Yu’s session “Creating the Right Cubes for Microsoft Excel and Excel Services” hoping that I’ll get a sneak preview of Excel 2010. Alas, it was all about refining the cube definition with display folders, perspectives, hierarchies, etc. so it appears more user-friendly in Excel. Later, I learned that Office 2010 (or whatever it will be called) is under strict NDA which explains the lack of demos. The most interesting thing about that session was that I finally understood why the SSAS team decided to scale down the cube wizard in SSAS 2008 to generate basic dimensions only. The reason was performance. You see, the BIDS 2005 cube wizard would oftentimes suggest non-optimal dimension hierarchies and the modeler wouldn’t revise the design leading to bad performance.

Next, I attended the Thierry D’Hers Top Ten Reasons for Using Microsoft SQL Server 2008 Reporting Services. It’s always good to watch RS-related sessions. Thierry listed the community support as one of the reasons for organizations to consider moving to SSRS. I agree with this and the great community support is applicable to all MS technologies. Speaking about BI only, a few years ago there wasn’t a single book about Cognos for example. Granted, the last I look on Amazon there was one book. In comparison, Microsoft has a vibrant community of book authors, MVPs, trainers, etc. Almost every publisher has a book about SSRS 2008. This is of course good for the community and so good about authors as the competition is tough J

Thierry listed the Report Builder 2.0 as #1 reason to move to SSRS 2008, followed by data visualization, and tablix. Based on my real-life projects, I’d personally have listed them in the reverse order, with tablix being #1. This session officially announced that the map control will make to the Kilimanjaro release (now officially called SQL Server 2008 R2). Later on, Thierry was kind enough to show me a demo of the map pre-release bits. One of the data modes is using the SQL Server 2008 geospatial data types which would let you map any region in the world. Thierry showed a cool report showing the worldwide sales of SQL Server, where each country had a different color gradient based on the sales volume.

After lunch, I hang around the BI area in the learning center to answer questions and rub shoulders with Microsoft employees and peers. I was surprised to learn that there are only two Reporting Services-related sessions for the entire TechEd and one of them is mine! All of a sudden, I felt 2″ taller. Later on, I felt adventurous to learn something completely new and attended a SQL Server 2008 Failover Clustering only to realize how much I don’t know about it since I’ve never used it. BTW, there are great advancements in the SQL Server 2008 failover clustering, such as ability to upgrade or patch a cluster node without stopping it.

Report Builder 2.0 ClickOnce

One important change that Service Pack 1 for SQL Server 2008 (public CTP available here) will bring is ClickOnce deployment of Report Builder 2.0. By default, however, Report Manager or SharePoint (if you have SSRS installed in SharePoint integration mode) still point to Report Builder 1.0. Here is how to reconfigure SSRS to launch Report Builder 2.0 instead. The following procedure assumes SharePoint integration mode but the steps are similar if SSRS is running in native mode.

CAUTION   Steps 1 and 2 below to copy folders are for the CTP bits of SQL Server 2008 SP1 only because the CTP build doesn’t support RB 2.0 ClickOnce in SharePoint integration mode. SQL Server 2008 SP1 RTM will include an installer to make the appropriate folder changes automatically. Therefore, DO NOT manually copy the folder content with the final bits or undo your changes before you install SP1 RTM so the installer doesn’t fail.

  1. SharePoint Integration Mode Only. Back up the content of the C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\12\ISAPI\ReportBuilder folder. This step is not needed with native mode.
  2. SharePoint Integration Mode Only. Copy all files from the C:\Program Files\Microsoft SQL Server\MSRS10.MSSQLSERVER\Reporting Services\ReportServer\ReportBuilder folder to the C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\12\ISAPI\ReportBuilder folder. This step is not needed with native mode.
  3. SharePoint Integration Mode Only. If you install SQL Server 2008 SP1 this will provide an update for native mode only.  In order to get Report Builder ClickOnce for SharePoint integration mode working, you need to download the RB 2.0 ClickOnce update for the RS SharePoint add-in from the feature pack page.  This is necessary because the ClickOnce bits must reside on the SharePoint server. As of the time of this writing, the latest add-in is April 2009 which you can download from here. Install rsSharePoint.msi (or rsSharePoint_x64.msi for x64) to update the SharePoint add-in. Then install RB2ClickOnce.msi which copies the Report Builder 2.0 ClickOnce files to the SharePoint site.Note (7/15/09): There is a known issue that RB2ClickOnce.msi fails to install on x64 environment. Microsoft should fix this in Cummulative Update 3 for SQL Server 2008 Service Pack 1. Meanwhile, if you have a report server running in SharePoint integration mode on x64, manually copy the Report Builder 2.0 files by following steps 1 and 2.
  4. SharePoint Integration Mode Only. Open WSS Central Administration. Click the Application Management tab. Click the Set Server Defaults link found under the Reporting Services section.

022509_0300_rbclickonce20

5. Enter the URL address to the ReportBuilder_2._0_0_0.application file. In SharePoint mode, assuming you followed steps 1-2, the URL address will be:
/_vti_bin/ReportBuilder/ReportBuilder_2_0_0_0.application

For a report server configured in native mode, go to Report Manager, click the Site Settings menu link and enter the following to the Custom Report Builder launch URL setting:

/ReportBuilder/ReportBuilder_2_0_0_0.application

At this point, SSRS is reconfigured to launch Report Builder 2.0 instead of Report Builder 1.0. From the end user perspective, the user would launch RB 2.0 just like they used to launch RB1.0, that is, click New->Report Builder Report in SharePoint or click the Report Builder button in Report Manager. The first time the user does this, the Report Builder 2.0 bits (almost 50 MB) will get downloaded and installed in the ClickOnce local application cache, which is a hidden directory under Local Settings for the current user. The ClickOnce application cache will not be cleared when the user clears the browser cache. Subsequent requests will use the local copy.

UPDATE Now that SQL Server 2008 SP1 is released, it includes a readme file with steps how to configure and troubleshoot Report Builder 2.0 ClickOnce in SharePoint integration mode.

Cumulative Update 2 for SQL Server 2008

Microsoft has released the Cumulative Update 2 build (build number 1779) for SQL Server 2008. The update is not publicly available as of now. It has to be released individually based on whether you are running into a bug fixed in the build and it is password-protected. Among other things, it fixes the freeze issue associated with opening SSAS projects in BIDS.

SSAS WHERE vs. Subselect Puzzle

I’ve been involved recently in a quest to optimize an SSAS 2008 long-running query that would return the 12-month history of a KPI. Since the KPI was displayed on a dashboard page alongside other KPIs and charts, it was important to optimize the query as much possible. Rephrased to Adventure Works, the query went something like this:

WITH MEMBER increaser AS Iif([Measures].[Internet Sales Amount]>([Measures].[Internet Sales Amount], [Date].[Calendar].PrevMember),1,NULL)

MEMBER CustomersWithIncrease as Sum([Customer].[Customer].[Customer], [Measures].[increaser])

SELECT [Measures].[CustomersWithIncrease] on 0,

ParallelPeriod ([Date].[Calendar].[Month], 11, [Date].[Calendar].[Month].&[2003]&[12]) :[Date].[Calendar].[Month].&[2003]&[12] on 1

from (select -{[Customer].[Customer].&[20075]} on 0 from [Adventure Works])

where {[Sales Territory].[Sales Territory Country].&[United States], [Sales Territory].[Sales Territory Country].&[United Kingdom]}

This query compares the sales for each customer with the customer’s sales for the previous month and returns the number of customers who have increased sales for the past 12 months. Also, the query uses a subselect to exclude a customer and a WHERE clause that restricts the results to USA and United Kingdom only. The real-world query would get the members to filter in the WHERE clause from the UI.

The first optimization technique to point out is that instead of filtering the customer set using the Filter function, the query benefits from Mosha’s summator trick, which alone improved the query performance about ten times. However, the query was still taking long to execute (some 30 seconds with set of 150,000 customers). Much to my surprise though, flipping the WHERE and the subselect clauses cut the query time in half.

WITH MEMBER increaser AS Iif([Measures].[Internet Sales Amount]>([Measures].[Internet Sales Amount], [Date].[Calendar].PrevMember),1,NULL)

MEMBER CustomersWithIncrease as Sum([Customer].[Customer].[Customer], [Measures].[increaser])

SELECT [Measures].[CustomersWithIncrease] on 0,

ParallelPeriod ([Date].[Calendar].[Month], 11, [Date].[Calendar].[Month].&[2003]&[12]) :[Date].[Calendar].[Month].&[2003]&[12] on 1

from (select {[Sales Territory].[Sales Territory Country].&[United States],

[Sales Territory].[Sales Territory Country].&[United Kingdom]}

on 0 from [Adventure Works])

where -{[Customer].[Customer].&[20075]}

Here, the member that needs to be excluded was moved to the query WHERE clause, while the rest of the filter went to the subselect. While I don’t know what the server does exactly, my hypothesis is that the WHERE clause is more efficient in restricting the cube space before the rest of the query is executed. I wasn’t able to reproduce the performance gain with the Adventure Works cube probably because is too small or the issue was design specific. Larger cubes may benefit from the filter flip.

IMPORTANT  OOPS [:(]

As Mosha was quick to point out, in particular case where SUM is used, the attempt to exclude a given customer in both cases (subselect and WHERE) will not work. Specifically, filtering out the customer is a no-op, since the Customer.Customer.Customer inside Sum overwrites it. Consequently, the queries don’t exclude this customer from the computations at all and therefore are wrong. A great optimization without QA involvement, indeed :-). However, I still can’t explain why subselect is slower if both options are no-ops. BTW, if the exclude filter was done on another attribute (not Customer), then the subselect would produce the correct results, while the WHERE clause will still be a no-op.

In this particular case, the easiest way to exclude specific customers is to remove them from the set inside the SUM function, as the following query shows. This, of course, will impact the query performance. Based on my test, the member exclusion adds about 25% overhead to the query.

 

WITH MEMBER increaser AS Iif([Measures].[Internet Sales Amount]>([Measures].[Internet Sales Amount], [Date].[Calendar].PrevMember),1,NULL)

MEMBER CustomersWithIncrease as Sum({[Customer].[Customer].[Customer] – [Customer].[Customer].&[20075]} , [Measures].[increaser])

SELECT [Measures].[CustomersWithIncrease] on 0,

ParallelPeriod ([Date].[Calendar].[Month], 11, [Date].[Calendar].[Month].&[2003]&[12]) :[Date].[Calendar].[Month].&[2003]&[12] on 1

from (select {[Sales Territory].[Sales Territory Country].&[United States],

[Sales Territory].[Sales Territory Country].&[United Kingdom]}

on 0 from [Adventure Works])

Report Builder 2.0 RTM’ed

Today, Microsoft released Report Builder 2.0 (build 10.00.1600.60) , as Robert Bruckner announced in his blog. This is a major milestone in the Reporting Services roadmap. In my opinion, Report Builder 2.0 is one of the most important and exciting features of Reporting Services 2008 as it bridges the gap between standard and end-user (ad hoc) reporting. You should definitely consider Report Builder and Analysis Services for your ad-hoc reporting projects.