Microsoft released Cumulative Update Package 5 for SQL Server 2008 Service Pack 1 (build 10.00.2746). Among other things, if fixes an SSRS issue with printing and conditional visibility of recursive groups which I reported.
One year after submitting this small article, SQL Server Magazine has finally published it. This article demonstrates how you can use table-value parameters (TVPs), a new feature in SQL Server 2008, to let you pass multivalued parameters from a report to a stored procedure.
The code can be downloaded from the SQL Server Magazine site or from here.
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
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.
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.
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.
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.
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])
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.
Microsoft has released the first for SQL Server 2008 cumulative update package. Among other issues, it fixes the SSAS bug with requesting measures with mixed aggregation functions which I previously reported.
