A good day

Today was a good day. I love walking out of work having solved more problems than I caused.

Today there were two staring me in the face. First, my WPF client was timing out calling a long-running WCF method over NetTcpBinding. This is actually the second or third time this problem has reared its ugly head. I was calling the method asynchronously (client-side asynchronously), and I was hoping that would let me circumvent the timeouts like it does with traditional web services. No dice. Then I tried increasing the ReceiveTimeout, which several posts recommended (and which appears to have worked for the NetNamedPipesBinding call I'm making inside the service), but it still failed. In the end, Juval Lowy was my personal Jesus once again (if I run into him at PDC this year, I'm going to buy him a beer). I pulled Programming WCF off of the shelf and found the section that talked about session timeouts in the NetTcpBinding. It turns out that you need to enable ReliableMessaging (on both sides of the pipe, or you'll get a Contract mismatch error) and set the InactivityTimeout to override the idle timeout for NetTcp services. And since the long-running method was part of a big service contract that we used frequently, I decided to move the method to another service contract so the reliable messaging wouldn't add too much overhead. This was a big win for us, because this method kicks off an ETL package, reprocesses our cube, and pushes out all the report subscriptions.

The next problem we had was an OLAP-sourced report that's had terrible performance ever since we started accruing data snapshots in the cube. The report has two different measures that share two common dimensions, and then I have a third dimension that is only related to one of the two measures. The report is structured as a two-tier report with the two shared dimensions
on the top level and the third dimension as a drilldown into Measure Group 2. But the SSRS-generated MDX was a hot mess. For a long time, I thought it was the fact that it was trying to pull back Dimension 3 and Measure Group 1 in the same report and it was doing some kind of weird cross-join that was killing my performance. But a more astute cube jockey took a look at it and pointed out that it was trying to return back way too many rows. All of the measures have to be on the same axis in your cube, and my Measure Group 1 was returning a value for every Dimension 1/Dimension 2 combination. Result: 350,000 rows, and a memory spike in SSMS of 1 GB (yikes). So I sat down and figured out what rows I really needed and got it knocked down to about 30,000 rows and 200 MB.

Enough challenges for one day. Tomorrow is another full day.

Reporting Services 2008 API

This afternoon I dug once again into the Reporting Services API for SSRS 2008 in SharePoint Integrated Mode. And boy, let me tell you, that is FUN. I've been working on integrating schedules and subscriptions into my application that are event-driven rather than schedule-driven.

Currently in Reporting Services 2008 you can set up a subscription and have it run at a particular interval (e.g. every morning at 8 am) and deliver a report to your inbox. If you then want to make sure all of these subscriptions fire off together and all change together (in case you need your subscriptions to run at 6 am instead of 8 am), you can create a shared schedule (let's call it "First Thing In The Morning") and associate all of your subscriptions with this shared schedule.

What you can't do easily is say "I want all of the subscriptions on this shared schedule to run right after my OLAP cube refreshes, no earlier and no later". At least not from the front end.

So we turn to the Web Service API built into Reporting Services.

The first nightmare is permissions. If you're calling a method like FireEvent, your calling identity needs to have the right permission on the Report Server. This was my three months ago headache. Apparently someone thought it was a good idea to remove the Permission notes from the SQL Server 2008 and 2005 Books Online. You can find it for SQL 2000, sure. But do you really want to hang your hat on that? No. Let's pull out Reflector.

And even after you find the right Reporting Services permission, you realize that this permission can't be configured because you're running in SharePoint Integrated Mode, which means that all reporting services permissions are now packaged up into corresponding SharePoint permissions (there's a really good reference for that here). This was my two months ago headache.

Today's headache came from trying to figure out how to locate a shared schedule and programmatically launch it. You can't. So here's how you work around it:

1) Call ListSchedules to get the set of all schedules on your site, each of which is associated with a GUID. Find the GUID for the schedule you want to fire off.
2) Call ListAllSubscriptions to get all the subscriptions on your site.
3) Loop through the subscriptions and for each one call GetSubscriptionProperties to get the MatchData property (which, incidentally, is the only missing property from your Subscription instance. How conveeenient). The MatchData property (string) will either be a GUID or an XML string. If it's a GUID and it matches the GUID from your schedule object, you know that the subscription belongs to that schedule object. Call FireEvent and pass in the subscription GUID to launch the subscription.

Wouldn't it be nice if there was a ListAllSubscriptionsForSchedule method in the API?