In-Depth

Caveat Migrator: BI Changes Abound in SQL Server 2005

SQL Server 2005 BI enhancements could pose significant migration, re-education issues

• By Stephen Swoyer
• 08/04/2004

When a product’s been delayed as long as Microsoft’s SQL Server 2005 database (code name: Yukon), you can bet it’s going to pack a lot of changes.

For customers tapping SQL Server’s BI capabilities, that's just what's in the offing, thanks largely to an increased emphasis on BI in SQL Server 2005.

Microsoft released SQL Server 2000 almost four years ago. Since then, the software giant has shipped a variety of SQL Server 2000 service pack updates and feature or function add-ons, even as it’s pushed back the delivery date for the much-anticipated Yukon release on several occasions.

Microsoft first released Analysis Services (nee “OLAP Services”) and Data Transformation Services (DTS) for SQL Server 7.0 in 1998, but the software giant effectively fleshed out its BI strategy in the SQL Server 2000 timeframe, delivering revamped versions of Analysis Services and DTS—along with a first-of-its-kind reporting services component—for that product. In many respects, the BI-related changes that the software giant has announced for Yukon are even more ambitious, starting with a substantial overhaul of DTS, along with a refresh of SQL Server Analysis Services.

That’s just the beginning, writes Stewart McKie, an analyst with consultancy Ventana Research. “The SQL Server Yukon release is chock-full of business intelligence improvements. Among other things, Microsoft is promising better support for real-time analytics, a completely overhauled … ETL subsystem, new data mining algorithms, improved Microsoft Reporting Services and better key performance indicator … management,” he writes.

These enhancements and others are likely to be warmly received by developers and customers that have invested in SQL Server BI technologies, to be sure, but they also pose significant migration issues, McKie cautions. If nothing else, he argues, “Microsoft has a massive education job on its hands to ensure that end-user organizations take advantage of the new functionality.”

For example, the software giant has promised that Yukon will boast a simplified multidimensional expressions (MDX) implementation, but little has been said about the manner in which this is related to another new Yukon feature, a Unified Dimensional Model (UDM) that combines the characteristics of relational and OLAP data models. “[T]o take advantage of the new Unified Dimensional model as a way to manage both relational and multidimensional data via a single layer depends on the use of Microsoft’s Multidimensional Expressions (MDX) language,” McKie writes. “MDX is a proprietary SQL-based language that has the potential to become ‘BI’s SQL’ but is nowhere near as widely used as SQL and presents a steep learning curve, even for SQL-savvy programmers.”

For that reason, McKie and Ventana recommend a strong dose of MDX 101 for organizations that plan to migrate to SQL Server 2005.

Elsewhere on the migration front, Microsoft plans to transition Analysis Services from a multi-cube/virtual-cube paradigm to a single-cube/multi-perspective paradigm—a shift that McKie says “could require substantial reorganization of the underlying logic of many front-end BI applications that currently leverage Analysis Services Cubes.”

Much has been said about the achievements and shortcomings of Microsoft’s inaugural Reporting Services release, but it’s safe to say that many current users of that product are optimistic that Yukon will address at least some of these deficiencies (see http://info.101com.com/default.asp?id=8732). That may be so, but McKie identifies one issue that probably won’t be addressed in the inaugural Yukon release: “[D]eveloping Reporting Services reports depends on a Visual-Studio-based UI. This UI has a developer orientation that may be hard to adapt to for end users and managers who want to create reports for themselves rather than rely on IT.”

Microsoft may have signaled its plans to offer such a solution with the acquisition in April of ActiveViews, a Web-based report authoring and end-user query tool based on Microsoft’s .NET framework—and designed to exploit SQL Server 2000 Reporting Services. Although the software giant still hasn’t said how it plans to incorporate the ActiveViews technology, some analysts—including long-time industry watcher Mike Schiff, a senior analyst with consultancy Current Analysis—speculate that Microsoft will expose the technology in the form of an end-user-oriented query and report-authoring tool designed for Reporting Services.

That’s McKie’s take as well: Once Microsoft integrates the ActiveViews technology, Reporting Services "will begin to threaten the dominance of Business Objects Crystal Reports.”

All indications are that Microsoft takes seriously the need to educate customers about SQL Server 2005. The company recently announced a new SQL Server 2005 “Ascend Program” that offers would-be adopters extra training and hands-on lab support to help with their migration efforts. This is a good start, McKie says, but Microsoft could do still more: “[O]rganizations leveraging the SQL Server approach to BI should press Microsoft to address migration and other adoption issues by means of a significant upgrade to the SQL Server Best Practices Analyzer tool,” he writes.

In spite of SQL Server 2005’s steep learning curve, Szymon Slupik, CTO with CDN SA, a Polish ISV, is excited by the BI enhancements Microsoft has planned for its next-generation database offering. CDN SA is using SQL Server 2000 to develop a packaged BI solution for customers that, in some cases, have no BI or OLAP expertise whatsoever. “In this respect [SQL Server 2000 Analysis Services] is very difficult to program. The main issue—solved in Yukon—is split between design and deployment. In [Analysis Services] you cannot just design the structures, take them, deploy later and then fill with data,” he explains.

In particular, says Slupik, Yukon will address several structural and performance issues that are endemic to Analysis Services, including memory problems; the absence of real hierarchies in dimensions; and performance issues with write-back, especially for non-leaf level write-back.

Elsewhere, Slupik says that SQL Server 2005 DTS introduces several welcome enhancements as well. “The other issue is DTSes—their development consumes about 80 percent of total BI costs. Once you have your star/snowflake schema done, building cubes is a piece of cake. DTS development environment in 2000 is very limited, and we are looking to many improvements in Yukon in this area,” he comments.