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.
by Mark Frawley
Jul 19, 2004
Page 2 of 3
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 managementdata 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 tablein 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 transparentthey 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 hierarchywhich 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.