Many 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:
+ 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