Upgrading my reports
I’m in the middle of a long (6 months or more) project to upgrade all of my reports. I’m doing this for two major reasons. We’re gradually migrating the data warehouse from SQL Server 2000 to SQL Server 2005, and as I move my projects to a new 2005 server I’m trying to review my code to take advantage of new SQL 2005 features. At least as important is the fact that we’re in the process of putting in a new billing system, which means that most of the data sources for my programs are going to be changing.
I’m preparing for the new data sources by rewriting my programs to refer to views rather than the underlying tables; I’m hoping that will enable me to just change the views when necessary, rather than re-write the stored procedures.
I took advantage of the quiet time between Christmas and New Years to upgrade one of my earliest projects, a series of sales reports I call “POSFlash”. When I first started this job, I inherited a few old Access 97 reports that told our key national account reps what books their accounts sold in the previous couple of weeks. This point-of-sale (”POS”) data is crucial to our business - reps would review the report every Monday morning to find fast-selling titles that they could get reorders on. But the Access 97 reports gave only limited sales data (limited number of titles and only the most recent weeks of sales), and the reports took a long time to run.
So I converted the reports to run directly on SQL Server with stored procedures and used Access XP merely as a front-end. That is, I use Access to create or modify the report definitions (which customers to include, which titles, what fields, etc.), and once the SQL Server stored procedure generates a weekly report and stores it in a report table, the Access program downloads just the report data, loads it into an Excel spreadsheet, and formats it for the users.
Moving the process over to the new 2005 went quite smoothly. The code in the primary stored procedure is a little under 1,000 lines, but it didn’t require a lot of rewriting. (The biggest change was that I was able to use SQL 2005’s new TOP (@x) feature to remove a few dozen lines of code.) The biggest surprise was when I finished debugging the code and discovered that I was able to generate most of the weekly reports in 30 seconds or so - versus the more typical 30-60 minutes required by the old version! (I’m sure that at least 95% of the speed improvement is because the new server is faster, not because of any of my tweaks . . .)
Once the new program was in place on the new server, I set up a Reporting Services report to run the master procedure at the appropriate intervals (4 or 5 times a day on Monday and Tuesday, less frequently as the week goes on - at times chosen based on when the underlying data is loaded into our data warehouse), and for the week of January 5th I ran the old and new reports in parallel.
There were a few glitches during the week, mostly due to typos or other errors in the snippets of code (stored in tables) that specified where the data for each report was to be found. By the end of the week I had quashed those bugs, and for the week of January 12th we switched over to the new version. As far as I can tell, none of my users noticed the difference, except that possibly the reports were available a little bit sooner than before.
Obviously I’m pleased that the upgrade went well!