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?

The Best Laid TimeKeys

This is what I get for trying to be clever. See my date key post for background.

I'm writing this post from the Central Standard Time Zone in the US. It's March 11, 2009 at 2:30 pm. Here's what I'm writing into my Management Studio instance. Watch closely:

And now:

Apparently when you cast a datetime to an int, it rounds the int up to the next day if it's past noon. Argh. So I've ripped out my clever TimeKey logic and replaced it with the YYYYMMDD format as an int (easily done with "CAST(CONVERT(char(8), @date, 112) as int)"). I guess I deserve this one.

Time Dimension Key Field

So I was building this OLAP cube for a new application and looking at how to populate the time dimension, which is on a date level. I was sure I wanted to have an integer key on the table to make joining to the fact table as painless as possible. But the problem is I also wanted all of my dates in key order. If I used an Identity for my key field, I'd never be guaranteed that my dates were in key order. I could insert 12/31/2007 today and it would take key 1, and then I could insert 12/30/2007 tomorrow and it would take key 2. How would I ever sleep at night?

What I realized I could do, though, is cast my datetime field to an int when I inserted.

SQL Server 2005 will give you the number of days between 1/1/1900 and the date you specify in your cast. Now I can ensure that each row represents a different date and still not be tied to joining to another table while running ETL jobs or digging in the data.

It's the little things.

WCF Proxies and Abstract Factories

Okay, so you've built your WCF service for your distributed application.

[ServiceContract]
public interface IMyService
{
[OperationContract]
void DoSomething(bool Fail);
}

And you're consuming it from your client application, and because you're a smart guy you know that your proxy implements IDisposable and you can call it with a 'using' statement.

using (MyServiceClient proxy = new MyServiceClient())
{
proxy.DoSomething(true);
}

So now you know that even if your call fails for some reason your proxy will still clean up and go home. But then your service throws a FaultException one day and passes it to your client application, and your client application wants to tell you about this exception really really bad but all it can say is "The communication object, System.ServiceModel.Channels.ServiceChannel, cannot be used for communication because it is in the Faulted state." What gives?

And then it hits you: this exception is happening when you try to dispose of your proxy and it's covering up the exception that should be getting thrown outside of the using statement.

See, there are two ways to close your client proxy: you can call Close() on the proxy, which only works when the proxy is not in a Faulted state. You can also call Abort() on the proxy, which closes it regardless of what state it's in. Guess which one the proxy's Dispose() method calls?

So now your proxy call looks like this:

using (MyServiceClient proxy = new MyServiceClient())
{
try
{
proxy.DoSomething(true);
}
catch (Exception)
{
proxy.Abort();
throw;
}
}


(This is an over-simplification. Of course you're going to do something with that exception like email it to the support team and buy the user a drink.)

And now you have to do that for every service call you make from your client application. Three lines are now twelve lines. In the grand scheme of things, it's a minor annoyance. But it got me thinking. I use an abstract factory to return my proxy instance because I have a different endpoint for my production and development regions.

internal static class ProxyFactory
{
internal static MyServiceClient GetProxy()
{
return new MyServiceClient();
}
}

I'd love to be able to write this new boilerplate code into my abstract factory method instead of including it in every call I make. And with anonymous methods I can do it. It only requires a little more complexity than my abstract factory method.

First I create a delegate with one input parameter for the interface our proxy supports.

internal delegate void MyServiceProxyHandler(IMyService proxy);


Then I change my factory method to do this:

internal static void MakeProxyCall(MyServiceProxyHandler OurMethod)
{
using (MyServiceClient proxy = new MyServiceClient())
{
try
{
OurMethod(proxy);
}
catch (Exception)
{
proxy.Abort(); throw;
}
}
}

Now every call I make looks like this:

ProxyFactory.MakeProxyCall(delegate(IMyService proxy)
{
proxy.DoSomething(true);
});

Or this (if you're a lambda cowboy):

ProxyFactory.MakeProxyCall(p => p.DoSomething(true)); -- one line. woop woop!

If my call fails for some reason, I don't need to worry about closing the proxy because it's already done for me. And any exceptions still get thrown outside the using statement and passed on to the calling code. I can even centralize my notification logic inside the factory method. Say I wanted to send an email every time a CommunicationException was encountered. I could create a separate catch block for CommunicationExceptions, add my notification code, and throw the error back out to the calling code.

An additional benefit comes from the fact that each WCF call is now completely focused on the interface which allows me to abstract away all the plumbing and even stub in a local mock class that implements the same interface for testing purposes. Plus if I wanted to change the way I was making every single WCF call, I'd have to search through all of my code for each service call and change the structure of each call. With the new ProxyFactory method, I change the code in one place.

There are two things that I lose in doing all my calls this way:

1) I'm instantiating my proxy every time I make a call, so any benefit I might gain from having a stored proxy is lost. This, of course, could be worked around in the ProxyFactory class by keeping a static instance of the proxy around, not encapsulating the call in a using statement, and only aborting the proxy when the call fails and re-creating it (I'll leave this as an exercise for the reader).

2) I can't make asynchronous calls with the proxy, again because I'm closing the proxy in the factory method so I don't have anything to call the End* method on. This doesn't concern me that much because any long running calls can be handled in my code by a BackgroundWorker process. And if it really got to be a pain, I'm sure I could figure out a way to overcome it in my ProxyFactory (I'll leave this as an exercise for an even more eager reader).