any SQL Server developers eagerly anticipate the long-awaited release of SQL Server 2005, formerly code-named “Yukon.” Major goals for Business Intelligence (BI) in the 2005 release are better integration of the somewhat loosely-coupled BI components of SQL Server 2000, provide enterprise reporting capability in the form of Reporting Services, and enable faster and easier development of “pervasive” BI, which can reach people at every level of the organization. To achieve these goals, each existing component of the SQL Server suite has been changed or replaced in the 2005 release. Reporting Services, originally slated to debut as a new component of SQL Server 2005, has recently been released independently?but remains an integral component of the SQL Server 2005 vision.

SQL Server BI developers are well-advised to learn as much as possible about these changes and to start planning how to capitalize on them. This article focuses on some of the most basic differences between the 2000 and Beta 1 2005 versions of Analysis Services, and addresses these differences in three overlapping areas:

• User Interface
• Architecture
• Programming

Beyond these three major areas, there are many other changes in Analysis Services 2005 (AS 2005) that are outside the scope of this article. I’ve listed (but not discussed) these at the end of this article. Also not covered but worth noting are enhancements to Transact-SQL in SQL Server 2005 itself which help facilitate BI analysis. For more information, you can find details on all the changes in this Microsoft whitepaper.

Keep in mind that the SQL 2005 version discussed in this article is still in beta; therefore nothing should be regarded as finalized.

User Interface
Analysis Services 2000 (AS 2000) developers use Analysis Manager 2000 (AM 2000) to create and manage OLAP (Online Analytical Processing) cubes. They also use the SQL Server 2000 tools Query Analyzer and Enterprise Manager to develop the relational star schemas on which the OLAP cubes are based, and the Extract Transform and Load (ETL), in the form of stored procedures and Data Transformation Services (DTS), to load them. In AS 2000, these three tools are independent, the user experience is inconsistent, and it is almost impossible to establish development standards.

AS 2005 incorporates the functionality of those three former tools into two new tools hosted by the Visual Studio 2005 IDE, “BI Workbench” and “SQL Workbench.” You use BI Workbench?like AM 2000?to create and manage cubes; however, unlike AS 2000, BI Workbench also includes design capabilities for DTS (found in Enterprise Manager in SQL Server 2000) and Reporting Services. SQL Workbench merges the functions of both Query Analyzer and Enterprise Manager as well as some of the administrative functions of AM 2000.

The AM 2000 experience has characteristics such as:

• The user must explicitly connect to an existing Analysis Services Server before any other tasks are possible. Each AS server registered with AM 2000 appears as an object.
• The object of coarsest grain is the database, a collection of related connection definitions, cubes, shared dimensions, data-mining models, and roles. The database, archived as a CAB file, is the fundamental unit of deployment between AS servers.
• Cubes must be based on a single fact table at the center of a star or snowflake schema, which is created and populated using other tools.
• Building a cube is a manually-intensive process, even using the cube wizard.
• Analysis Services 2000 stores metadata in either an Access or SQL Server database.

In contrast, your experience using BI Workbench will be considerably different (see Figure 1):

	?
Figure 1. BI Workbench. The figure shows an imaginary AdventureWorks DevX solution loaded into BI Workbench. The solution contains a single Analysis Services project (AdventureWorks DevX).

• There is no explicit representation of an AS server in BI Workbench. In fact, it is not clear in Beta 1 how or if you can administer AS server instances within BI Workbench as you can in AM 2000. You can administer instances of AS server from within SQL Workbench.
• The object of coarsest grain is the solution, which is a collection of one or more projects. These terms derive from the Visual Studio environment, so they’re already familiar to .NET developers writing applications in C# or VB.NET. There are several types of projects, one of which (the “Analysis Services” type) is similar to a “database” in AM 2000. Other project types represent DTS and Reporting Services functions. “Solutions” therefore package and integrate various components of a BI application as compared to AM 2000.
• Cubes can be based on multiple fact tables having different grains and dimensions. This is somewhat similar to using virtual cubes in AM 2000 to present a unified view of multiple physical cubes of varying grain and dimensionality, but is much more seamless (also see the discussion of UDM later in this article).
• BI Workbench introduces IntelliCube technology which aims to automate cube creation through heuristic analysis of the relational schema. In some cases, a single click can build the cube. Despite the automation, you can?when necessary?still build a cube manually using the Cube Editor as in AM 2000.
• Analysis Services 2005 stores metadata in XML files, which means it can be put under source control. There is no longer a metadata database as with AM 2000.

Architectural Changes

• AS 2000 supported a sophisticated mixture of calculation and caching on both the server and client. In keeping with its integration and simplification goals, AS 2005 supports only server-side calculations and caching.
• AS 2000 has no concept of configuration management?data sources are hard-coded in a cube definition and must be manually changed to deploy to different environments. AS 2005 introduces configuration and deployment management via several new utilities.
• AS 2000 essentially required a relational star schema or close variant as the data source for a cube. You can map AS 2005 cubes to arbitrary schemas via Data Source Views (described below). This change is envisioned to not only increase flexibility but also enable low-latency BI applications. In extreme cases, a cube could be mapped directly to an operational database.
• AS 2005 introduces Data Source Views (DSV), which are higher-level organizations layered on a Data Source (DS). In AS 2000, there are only DSs and all objects accessible through the DS provider (tables and views if a relational source) are available. A DSV provides organization, control and other capabilities lacking in a DS, including:
• Selecting a subset of objects in the DS
• Renaming (but at least in Beta 1, unfortunately not hiding) columns
• Adding calculated virtual columns (similar to “multiple-column measures” in AS 2000)
• Describing foreign key relationships for the benefit of UDM (see following)
• Adding named queries, an arbitrary SQL statement returning a result set which is treated as if it were a table?in effect simulating database views without requiring “create” database permissions.

Additionally, DSVs permit working on cube structures without being connected to the data source, unlike AS 2000. Currently, a cube is based on a single DSV which in turn is based on a single DS. Documentation states that DSVs are shareable between Analysis Services and DTS projects but this was not evident in Beta 1.

The Unified Dimensional Model (UDM)

	?
Figure 2. Typical Current Business Intelligence Architecture: The figure illustrates multiple data models and physical stores of the same data, as well as a lack of integration.

AS 2005 introduces the Unified Dimensional Model (UDM), Microsoft’s term for new functionality converging relational and multidimensional/OLAP data stores by allowing creation of a single dimensional model that seamlessly applies to both. This addresses the historical “great divide” which usually exists between OLAP and standard reporting. Figure 2 shows the current typical BI architecture.

Microsoft characterizes UDM as “combining the best of relational and OLAP.” Under UDM, MOLAP data stores become transparent?they are cached and managed automatically. With UDM architecture, relational reporting receives a performance boost from MOLAP caching, while OLAP applications are able to deliver low-latency data and browse detailed attributes. Multiple UDMs provide subject-area views of the data warehouse. UDM changes the overall architecture as shown in Figure 3:

Some of the new features provided by UDM include:

• Attribute-based Dimensions. An AS 2000 dimension has one and only one hierarchy, and with the exception of cumbersome “member properties,” the only dimension attributes allowed are those directly part of the hierarchy?which severely limits options for slicing, filtering, and browsing dimensions as advocated in the writings of Ralph Kimball. By contrast, AS 2005 dimensions consist simply of attributes with no necessary hierarchical relationships. They may be combined into natural and other useful hierarchies. For example, a Customer dimension could have dozens of demographic attributes that can be organized into both a natural hierarchy (Country-Region-State-City) and an analytic hierarchy (City-Age-Gender).

	?
Figure 3. UDM Business Intelligence Architecture: The data warehouse, with its data model, can be the only explicit physical store. All forms of data consumption are integrated. Physical star schemas can still be used as well, but cubes are no longer explicit.

• New Native Dimension Types. While AS 2000 dimensions must be structured as either star/snowflake or parent-child, AS 2005 will additionally support as dimension types role playing (customers which are both consumers and suppliers), reference (currencies in relation to different financial products) and many-to-many (customers and accounts) dimensions. Not all of these features are apparent in Beta 1 however.
• Measure Groups. As previously mentioned, in AS 2005 multiple fact tables of different grains can be combined in a single cube. Measure groups collect measures of like grain together and are used in conjunction with Perspectives.
• Perspectives. Given other capabilities supported by UDM, a dimensional model represented by a cube can become overwhelmingly large and difficult to navigate. Perspectives are simply named subsets of dimensions and measure groups. The same cube can have multiple perspectives for different groups of users.

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.