SQL Server 2005: The BI Release

The number of new features in SQL Server 2005 is almost overwhelming. Of all the new features, the one that has drawn the most attention from the media and DBAs alike is the integration of the CLR with the SQL Server database engine, and I can understand why. CLR integration brings with it a host of new capabilities as well as a huge potential for misuse. However, CLR integration is off by default, so you don't need to worry about it unless you decide to use it.

While the inclusion of the CLR is an important step for Microsoft and SQL Server, it's not the most important change. Instead, Business Intelligence (BI) is the big story in SQL Server 2005. At the Professional Association for SQL Server (PASS) conference in 2004, Bill Baker introduced the themes of Integrate, Analyze, and Report to describe SQL Server 2005's BI functionality. Virtually every aspect of these BI areas has been either completely revamped, as with SQL Server Integration Services (SSIS), or significantly enhanced, as with Analysis Services and Reporting Services.

Arguably, the biggest changes are up front, in SSIS. Completely redesigned from the ground up, SSIS is completely different from Data Transformation Services (DTS), which it replaces. DTS was a useful tool, but it had problems with scalability and transportability. SSIS isn't just a data-transfer tool—it's a complete workflow engine. Its performance is significantly improved, and new tasks enable looping, package execution, and enhanced flow control. The new Package Configurations feature lets you easily pass in runtime variables from the registry, XML documents, parent packages, or environment variables to make your SSIS packages more flexible and easily transportable.

The core Analysis Services component has also received many important improvements and new features. In Analysis Services 2005, OLAP cubes use the new Unified Dimensional Model (UDM), which combines the strength of OLAP and relational storage, enabling you to create a custom balance between the performance and data storage your Analysis Services cubes use. Other additions include proactive caching for cube deployment, MDX enhancements, and a new XML for Analysis (XML/A) query language. Microsoft also added several new data-mining models that provide more options for data trending and prediction.

Changes on the reporting end of BI are every bit as significant as those in integration and analysis. The inclusion of Reporting Services provides an enterprise-oriented infrastructure that you can use to design and deploy reports across the organization. In addition, the Report Builder feature brings to the end user the ability to design reports that are completely integrated with Reporting Services.

At 5 years in the making, it's no surprise that SQL Server 2005 is the most feature-rich release since SQL Server 7.0—maybe ever. Though the 2005 release contains many important enhancements, the BI portion has the lion's share of the changes and new features. Many businesses were slow to adopt the BI features of the earlier SQL Server releases, but that won't be the case this time. SQL Server 2005 broadens the definition of BI to incorporate information integration and presentation capabilities that go beyond analytical processing. The nature of the BI changes in SQL Server 2005 enable a wide cross-section of organizations to quickly put them to use. Despite the slew of new features in other areas of the product, SQL Server 2005 could easily be known as "the BI release."