BI and SQL Server 2005

Just to finish up the story I started a few days ago (and get the albatross off my neck, so to speak), here is how the exploration of SQL 2005 DTS and OLAP features turned out. The end result was a cube of all domestic commercial flights for January 2003 (about 560,000 records) with delay, cancellation, flight time, taxi time information and more.

There were a few disappointments, like the handful of unhandled exceptions that crashed the Business Intelligence Development Studio. I have a feeling some of the problems were related to the source control features of the beta, as most of the weirdness would occur after a checkout operation (I’m using SourceGear Vault at home – Vault is free for a single user). I’ve never had a problem with Vault, so I’m guessing all the problems are in the BIDS beta.

The most frustrating problem was getting the OLAP designer to pick up schema changes in the underlying fact table. I had to delete the cube, dimensions, and data source views and start over.

The good news is, 2005 not only fixes many of the frustrations in 2000, but adds a good many features.

It requires considerably less work to get a basic cube up and running thanks to the ability of the IDE to analyze relationships and figure out a base set of dimensions, levels, and measures. Perhaps 160 mouse clicks down to 6.

The brain dead cube browser in 2000 has been replaced with what appears to be a version of the pivot table in Office Web Components.

The GUI remains responsive during cube processing (although it does not appear to show the number of records processed).

Everything appears to save as XML, the project, the structures, the packages. The source control integration and deployment options appear that they will be CM and deployment friendly without hand rolling tools.

The performance of the import and of Analysis Services processing was very good considering this is all running on a Virtual PC with 512 MB of RAM.

It appears everything is looking pretty good, hopefully the documentation and features will shore up by beta 3. There are so many options and buttons it is hard to stay oriented at times. In one week there will be a flurry of SQL Server 2005 webcasts, unfortunately only 1 appears to be related to Analysis Services. Hopefully there will be more coming down the road.