Thursday, June 18, 2009

Tab Delimited Renderer in SSRS 2005

I was recently tasked with coming up with a tab delimited renderer in SSRS 2005.

My first thought was to use the CSV renderer and add a config entry in the ReportServer.config file to use a tab as a field delimiter.
However, in SSRS 2005, Microsoft clears any whitespace when reading the config file for the CSV renderer, meaning that the tab character does not work.

So I set about researching how to create a tab renderer.

The first article I read stated that you would have to visit Redmond, WA and sit with an SSRS developer to even think about developing a rendering extension. I didn't buy this and kept searching.

There isn't a lot out there on rendering extensions, but I did find several references to the following assemblies:

Microsoft.ReportingServices.Interfaces
Microsoft.ReportingServices.ReportRendering


So with that, I started to develop a prototype solution to the tab rendering problem.

My first decision was to only render tables and matrices. The SSRS CSV renderer tries to render textboxes, which only leads to malformed CSV files. And since most of the time the tab delimited export is going to be loaded into Excel, I decided to just focus on the table and the matrix control.

More to come in the next post...

Friday, March 6, 2009

Last Update Dates

It's been a while... We are well into deployment and finalization of this version of our ETL and reporting system. Things are going well.

Recently I ran into an interesting ETL problem while using a source system "last update" field. Let me give you some background.

We have an ETL process that reads from a source system that was developed in-house. The queries were all based on the last update field in all of the tables.

While in UAT, several reports were reported as missing rows. After investigating, it appeared that the rows had never made it to the data mart. Needless to say, this was very worrisome.

I researched and tried to find out why these rows were excluded. There seemed to be no pattern, just random rows.

While looking at my morning logs, I noticed something strange. The ETL Last Update table showed times from about 5 hours after the ETL had run. It should have been when the ETL had run.

I looked in the source system, and there were 3 rows that had update dates in the future! 5 hours to be exact!

It turns out that under certain circumstances, the source system was using the wrong date/time to update the last update field. And this date was GMT, so it was 5 hours in the "future" when it was applied to the last update field.

The result of this was missed records on the ETL. It would miss 5 hours worth of updates anytime this occured in the source system.

So my recommendation, which I am now kicking myself for not implementing to begin with, is this: Always use a date range, not just a "Greater than" for last update fields. For example:

Where LastUpdate Between '3/5/09 11:00:00' and GetDate()


And the other rule... Never trust the source system to be accurate 100% of the time. Anticipate issues like this.

Anyway, that's all for now.

peace