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.

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.