Analysis Services Comparison: SQL 2000 vs. SQL 2005

Analysis Services Comparison: SQL 2000 vs. SQL 2005

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.

devx-admin

devx-admin

Share the Post:
Poland Energy Future

Westinghouse Builds Polish Power Plant

Westinghouse Electric Company and Bechtel have come together to establish a formal partnership in order to design and construct Poland’s inaugural nuclear power plant at

EV Labor Market

EV Industry Hurting For Skilled Labor

The United Auto Workers strike has highlighted the anticipated change towards a future dominated by electric vehicles (EVs), a shift which numerous people think will

Soaring EV Quotas

Soaring EV Quotas Spark Battle Against Time

Automakers are still expected to meet stringent electric vehicle (EV) sales quotas, despite the delayed ban on new petrol and diesel cars. Starting January 2023,

Affordable Electric Revolution

Tesla Rivals Make Bold Moves

Tesla, a name synonymous with EVs, has consistently been at the forefront of the automotive industry’s electric revolution. The products that Elon Musk has developed

Poland Energy Future

Westinghouse Builds Polish Power Plant

Westinghouse Electric Company and Bechtel have come together to establish a formal partnership in order to design and construct Poland’s inaugural nuclear power plant at the Lubiatowo-Kopalino site in Pomerania.

EV Labor Market

EV Industry Hurting For Skilled Labor

The United Auto Workers strike has highlighted the anticipated change towards a future dominated by electric vehicles (EVs), a shift which numerous people think will result in job losses. However,

Soaring EV Quotas

Soaring EV Quotas Spark Battle Against Time

Automakers are still expected to meet stringent electric vehicle (EV) sales quotas, despite the delayed ban on new petrol and diesel cars. Starting January 2023, more than one-fifth of automobiles

Affordable Electric Revolution

Tesla Rivals Make Bold Moves

Tesla, a name synonymous with EVs, has consistently been at the forefront of the automotive industry’s electric revolution. The products that Elon Musk has developed are at the forefront because

Sunsets' Technique

Inside the Climate Battle: Make Sunsets’ Technique

On February 12, 2023, Luke Iseman and Andrew Song from the solar geoengineering firm Make Sunsets showcased their technique for injecting sulfur dioxide (SO₂) into the stratosphere as a means

AI Adherence Prediction

AI Algorithm Predicts Treatment Adherence

Swoop, a prominent consumer health data company, has unveiled a cutting-edge algorithm capable of predicting adherence to treatment in people with Multiple Sclerosis (MS) and other health conditions. Utilizing artificial

Personalized UX

Here’s Why You Need to Use JavaScript and Cookies

In today’s increasingly digital world, websites often rely on JavaScript and cookies to provide users with a more seamless and personalized browsing experience. These key components allow websites to display

Geoengineering Methods

Scientists Dimming the Sun: It’s a Good Thing

Scientists at the University of Bern have been exploring geoengineering methods that could potentially slow down the melting of the West Antarctic ice sheet by reducing sunlight exposure. Among these

why startups succeed

The Top Reasons Why Startups Succeed

Everyone hears the stories. Apple was started in a garage. Musk slept in a rented office space while he was creating PayPal with his brother. Facebook was coded by a

Bold Evolution

Intel’s Bold Comeback

Intel, a leading figure in the semiconductor industry, has underperformed in the stock market over the past five years, with shares dropping by 4% as opposed to the 176% return

Semiconductor market

Semiconductor Slump: Rebound on the Horizon

In recent years, the semiconductor sector has faced a slump due to decreasing PC and smartphone sales, especially in 2022 and 2023. Nonetheless, as 2024 approaches, the industry seems to

Elevated Content Deals

Elevate Your Content Creation with Amazing Deals

The latest Tech Deals cater to creators of different levels and budgets, featuring a variety of computer accessories and tools designed specifically for content creation. Enhance your technological setup with

Learn Web Security

An Easy Way to Learn Web Security

The Web Security Academy has recently introduced new educational courses designed to offer a comprehensible and straightforward journey through the intricate realm of web security. These carefully designed learning courses

Military Drones Revolution

Military Drones: New Mobile Command Centers

The Air Force Special Operations Command (AFSOC) is currently working on a pioneering project that aims to transform MQ-9 Reaper drones into mobile command centers to better manage smaller unmanned

Tech Partnership

US and Vietnam: The Next Tech Leaders?

The US and Vietnam have entered into a series of multi-billion-dollar business deals, marking a significant leap forward in their cooperation in vital sectors like artificial intelligence (AI), semiconductors, and

Huge Savings

Score Massive Savings on Portable Gaming

This week in tech bargains, a well-known firm has considerably reduced the price of its portable gaming device, cutting costs by as much as 20 percent, which matches the lowest

Cloudfare Protection

Unbreakable: Cloudflare One Data Protection Suite

Recently, Cloudflare introduced its One Data Protection Suite, an extensive collection of sophisticated security tools designed to protect data in various environments, including web, private, and SaaS applications. The suite

Drone Revolution

Cool Drone Tech Unveiled at London Event

At the DSEI defense event in London, Israeli defense firms exhibited cutting-edge drone technology featuring vertical-takeoff-and-landing (VTOL) abilities while launching two innovative systems that have already been acquired by clients.

2D Semiconductor Revolution

Disrupting Electronics with 2D Semiconductors

The rapid development in electronic devices has created an increasing demand for advanced semiconductors. While silicon has traditionally been the go-to material for such applications, it suffers from certain limitations.

Cisco Growth

Cisco Cuts Jobs To Optimize Growth

Tech giant Cisco Systems Inc. recently unveiled plans to reduce its workforce in two Californian cities, with the goal of optimizing the company’s cost structure. The company has decided to

FAA Authorization

FAA Approves Drone Deliveries

In a significant development for the US drone industry, drone delivery company Zipline has gained Federal Aviation Administration (FAA) authorization, permitting them to operate drones beyond the visual line of

Mortgage Rate Challenges

Prop-Tech Firms Face Mortgage Rate Challenges

The surge in mortgage rates and a subsequent decrease in home buying have presented challenges for prop-tech firms like Divvy Homes, a rent-to-own start-up company. With a previous valuation of

Lighthouse Updates

Microsoft 365 Lighthouse: Powerful Updates

Microsoft has introduced a new update to Microsoft 365 Lighthouse, which includes support for alerts and notifications. This update is designed to give Managed Service Providers (MSPs) increased control and

Website Lock

Mysterious Website Blockage Sparks Concern

Recently, visitors of a well-known resource website encountered a message blocking their access, resulting in disappointment and frustration among its users. While the reason for this limitation remains uncertain, specialists