RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


Analysis Services Comparison: SQL 2000 vs. SQL 2005  : Page 3

Major changes to Business Intelligence (BI) features in SQL Server 2005 promise (or threaten) to alter the way you develop BI applications. Read this comparison article to find out what's changed and start planning today.

Programming Changes
AS 2005 introduces numerous new or changed features in its programming model. Because many of these are not fully implemented in Beta 1, little can be said in detail about them; however, experienced AS developers are likely to be interested in what's coming:

  • MDX. Microsoft claims the calculation model and syntax of MDX is simplified in AS 2005. Significantly, you now write MDX expressions (as opposed to queries) as procedural scripts with ordered statements. This eliminates the complication of "pass order," reduces the risk of infinite recursion and most important, allows step-by-step debugging, which was previously unavailable. Additionally, scripts can be scoped to limit their access, and script results can be cached to boost the performance of other queries.
  • Stored Procedures. Whereas AS 2000 supports User-Defined Functions (UDFs), which must be COM classes, according to documentation AS 2005 supports procedures written in any Common Language Runtime (CLR) language. It is not clear how these are interfaced to AS 2005.
  • Business Intelligence Wizard and Calculated Measure Templates. These templates aid in dealing with common but problematic BI cases, including currency conversion, semi-additive measures, and accounting rollups. However, they appear to be largely placeholders in Beta 1.
  • Analysis Management Objects (AMO). This replaces the DSO object model (although that remains available for backward compatibility) and allows you to create BI objects programmatically.
  • Server Trace Events. As experienced AS 2000 developers know, it's nearly impossible to get low-level information about what's happening inside the AS server. AS 2005 generates trace events, which can be monitored and analyzed with SQL Server Profiler as has been possible for SQL Server events for quite some time.
  • XML for Analysis (XML/A). Enables AS 2005 to present a Web services interface—with all the possibilities that implies for real-time analytics and decoupling. XML/A in fact is the native interface to AS 2005.
  • Translations. AS 2005 includes a translation capability which allows the same cube to be presented in multiple languages.
Other Changes
AS 2005 includes significant enhancements in other areas outside the scope of this article, including:

  • Data Mining
  • Proactive Caching
  • KPI framework
  • Enhanced writeback and count-distinct
  • Security
As with other components of the SQL Server 2005 release, the changes to Analysis Services are extensive. The release includes many long-desired enhancements, improves ease of development and administration, and greatly strengthens Analysis Service's position as a middle-tier BI server for environments of all sizes. The Unified Dimensional Model in particular is of interest not only for simplifying BI architectures but also for facilitating the setting of BI development and design guidelines across the enterprise. Both current users of Analysis Services and users considering it as the core of their BI technology approach will find many reasons to give serious consideration to upgrading.

Mark Frawley is a Technical Business Analyst on the Citigate Hudson Pervasive Business Intelligence team that created an Executive Dashboard for ABN Amro. His focus areas include data modeling, relational and multidimensional database technology, software development, and writing and teaching about the BI industry. Prior to joining Citigate Hudson, Mark worked at a variety of financial institutions and consulting companies on a wide range of applications and technologies, most recently as one of the chief designers and developers of data warehouses and datamarts over a seven year period at Soci�t� Generale.
Email AuthorEmail Author
Close Icon
Thanks for your registration, follow us on our social networks to keep up-to-date