Export (0) Print
Expand All
Creating Audit Tables, Invoking COM Objects, and More
Exception-handling Techniques
Exploring SQL Server Triggers: Part 2
Deliver User-Friendly Reports from Your Application with SQL Server Reporting Services
SQL Server: Display Your Data Your Way with Custom Renderers for Reporting Services
Updating Data in Linked Servers, Information Schema Views, and More
XML Features in SQL Server 2000
Expand Minimize
1 out of 3 rated this helpful - Rate this topic

Microsoft SQL Server 2000 as a Dimensionally Friendly System

SQL Server 2000
 

Joy Mundy
Microsoft Corporation

January 2001

Summary: This document describes how the Microsoft SQL Server 2000 Data Warehouse technologies map to the twenty criteria for a Dimensionally Friendly System, as characterized by Ralph Kimball. (20 printed pages)

Contents

Overview
Architecture
Administrative Criteria
Expression Criteria
Conclusion
For More Information
Endnotes

Overview

This paper maps features and functionality of the Microsoft® SQL Server™ 2000 Data Warehousing Framework to the twenty characteristics of a "dimensionally friendly system" identified by Ralph Kimball (1). These characteristics were introduced in order to provide "good metrics for what makes a system more dimensional or less dimensional" (2).

The Microsoft Data Warehousing Framework describes the relationships between the various components used in the process of building, using, and managing a data warehouse. The Data Warehousing Framework includes the components that are used to build a data warehouse: the relational database, Meta Data Services, Data Transformation Services, Analysis Services, OLE DB, and English Query. The Data Warehousing Framework earns a rating of 91 percent, or 17.8 out of 19.5 points applicable to the system under consideration:

  • Dimensional relational data warehouse in SQL Server 2000 Enterprise Edition.
  • Populated using SQL Server 2000 Data Transformation Services (DTS).
  • SQL Server 2000 Enterprise Edition Analysis Services cubes, typically on the same grain as the relational dimensional data warehouse (DW) (exceptions noted in the text). Cubes also have predefined calculations (computed measures, named sets, custom rollups, and calculated cells) available to all clients.
  • Cubes are populated using the DTS cube population task.
  • This paper does not explicitly consider a specific end-user tool because SQL Server 2000 is an open platform that supports many third-party access tools. Most "query" questions are answered from the perspective of whether the Analysis Services query syntax can support the construct, rather than whether any one tool does so. Very few questions refer directly to the user experience (something the end user would see on the screen), and these have been marked as "N/A."

This paper is structured around Kimball’s twenty characteristics. Each section presents Kimball’s definition of the characteristic, summarizes the Microsoft approach to addressing the issues, and tallies up point by point how well our architecture meets his requirements. Sections labeled "Characteristics" are excerpts from Kimball’s articles mentioned above; these two articles can also be found at http://www.intelligententerprise.com/000428/webhouse.shtml and http://www.intelligententerprise.com/000515/webhouse.shtml. The tables include Features and Feature Weights, which appear in Kimball’s "Twenty Criteria for Dimensionally Friendly Systems" (manuscript pending publication).

Architecture

1. Explicit Declaration
Rating=1.0

Characteristic 1: The system provides explicit database declarations that distinguish a dimensional entity from a measurement (fact) entity. These declarations are stored in the system metadata. The declarations are visible to administrators and end users, and affect query strategy, query performance, grouping logic, and physical storage. Facts can be declared as fully additive, semi-additive, and nonadditive. Default (automatic) aggregation techniques other than summation can be associated with facts. The default association between dimensions and facts is declared in the metadata so that the user can omit specifying the link between them. A dimension attribute included in a query is automatically the basis of a dynamic aggregation. A fact included in a query is by default summed within the context of all aggregations. Semi-additive facts and nonadditive facts are prohibited from being summed across the wrong dimensions.

Analysis Services cubes are defined with explicit declaration of dimensions and measures. The default aggregation technique of a measure (sum, count, min, max, distinct count) is assigned during the cube design process. Custom rollups in cube definitions provide a very flexible implementation of additive, semi-additive, and nonadditive facts. The cube structure provides all joins between dimensions and facts seamlessly to the end user.

Met?Feature Weight (3)Feature
Aa902646.kimballcriteria_checkmark(en-us,SQL.80).gif0.5Explicit differentiation of facts (numeric measurements) and dimensions (descriptive context for measurements) in system metadata
Aa902646.kimballcriteria_checkmark(en-us,SQL.80).gif0.2Declaration and appropriate restriction of facts as additive, semi-additive, and nonadditive
Aa902646.kimballcriteria_checkmark(en-us,SQL.80).gif0.1Default technique declared for dynamic aggregations of a fact
Aa902646.kimballcriteria_checkmark(en-us,SQL.80).gif0.2Default join (association) between facts and dimensions so user need not specify at query time

2. Conformed Dimensions
Rating=0.9

Characteristic 2: The system uses conformed dimensions to implement drill-across queries where answer sets from different databases, different locations, and possibly different technologies can be combined into a higher level answer set by matching on the row headers supplied by the conformed dimensions. The system detects and warns against the attempted uses of unconfirmed facts. This is the most fundamental and profound architecture criterion. This criterion is the basis for implementing distributed data warehouses, and especially Webhouses consisting of far-flung organizations (with no center) sharing data over the Web.

The architecture of SQL Server and its Analysis Services components well supports the characteristics of conformed dimensions. Cubes within the same database fundamentally use conformed dimensions. Virtual and linked cubes are defined across separate physical cubes located on one or more servers, to deliver advanced functionality with excellent performance. Analysis Services query syntax is designed to concatenate disjoint sets and to express queries across different levels of a cube.

Met?Feature WeightFeature
Aa902646.kimballcriteria_checkmark(en-us,SQL.80).gif0.5Multipass queries combining columns from separate answer sets that have same row headers
Aa902646.kimballcriteria_checkmark(en-us,SQL.80).gif0.2Multipass queries from separate databases or cubes
Aa902646.kimballcriteria_checkmark(en-us,SQL.80).gif0.1Multipass queries from separate cubes located on separate machines (Kimball combines this feature with the next one)
 0.1Multipass queries from separate vendor DBMSs (Kimball combines this feature with the previous one)
Aa902646.kimballcriteria_checkmark(en-us,SQL.80).gif0.1Multipass queries using different granularities within a conformed dimension

3. Dimensional Integrity
Rating=1.0

Characteristic 3: The system guarantees that the dimensions and the facts maintain referential integrity. In particular, a fact may not exist unless it is in a valid framework of all its dimensions. However, a dimensional entry may exist without any corresponding facts.

Analysis Services always maintains referential integrity between dimensions and facts. A dimension member may exist without a corresponding fact; a fact may not exist without a corresponding dimension member.

Met?Feature WeightFeature
Aa902646.kimballcriteria_checkmark(en-us,SQL.80).gif1.0Referential integrity guaranteed to be in place at query time

4. Open Aggregate Navigation
Rating=1.0

Characteristic 4: The system uses physically stored aggregates as a way to enhance performance of common queries. These aggregates, like indexes, are chosen silently by the database if they are physically present. End users and application developers do not need to know what aggregates are available at any point in time, and applications are not required to explicitly code the name of an aggregate. All query processes accessing the data, even those from different application vendors, realize the full benefit of aggregate navigation.

Analysis Services manages aggregate navigation for all cubes, no matter what the storage mode (MOLAP, HOLAP, or ROLAP). All query clients implementing OLEDB or OLEDB-OLAP benefit transparently from aggregate navigation. The system automatically chooses the most efficient precomputed aggregates to resolve a query, and over time can restructure the precomputed aggregates based on the actual queries seen by the system.

Met?Feature WeightFeature
Aa902646.kimballcriteria_checkmark(en-us,SQL.80).gif0.5Queries use aggregate navigation
Aa902646.kimballcriteria_checkmark(en-us,SQL.80).gif0.5Aggregate navigation is transparently open to all query clients

5. Dimensional Symmetry
Rating=1.0

Characteristic 5: All dimensions allow comparison calculations that constrain two or more disjoint values of a single attribute from a dimension in computations such as ratios or differences. Also, the underlying database engine supports an indexing scheme that allows a single indexing strategy to efficiently support query constraints on an arbitrary and unpredictable subset of the dimensions in a highly dimensional database.

Analysis Services query syntax easily supports disjoint filters and most front-end tools provide this functionality. The Analysis Services indexing strategy, which is transparent to the database administrator, efficiently supports query constraints on an arbitrary and unpredictable subset of dimensions and attributes.

Met?Feature WeightFeature
Aa902646.kimballcriteria_checkmark(en-us,SQL.80).gif0.5Queries can constrain disjoint values from any single dimension for a comparison calculation.
Aa902646.kimballcriteria_checkmark(en-us,SQL.80).gif0.5All dimensions can be indexed to efficiently support constraints on any subset of the dimensions.

6. Dimensional Scalability
Rating=0.4

Characteristic 6: The system places no fundamental constraints on either the number of members or the number of attributes in a single dimension. Dimensions with 100 million members or 1,000 textual attributes are practical. Dimensions with one billion members are possible.

Analysis Services supports moderately large dimensions, up to approximately 10 million members, in the standard multidimensional dimension storage mode. For very large dimensions, up to 100 million members, the developer can use relational storage of the dimension. There are an unlimited number of textual attributes on a dimension, although a source database table is limited to a row size of 64 KB. Neither the creation of cubes with a dimension having one billion members, nor with 1000 textual attributes, has been tested. Though this is theoretically feasible, until such tests are preformed, we would not claim this scalability as practical on commercially available hardware.

Met?Feature WeightFeature
Aa902646.kimballcriteria_checkmark(en-us,SQL.80).gif0.4Dimension with 100 million members is a practical configuration
 0.2Dimension with one billion members is possible and can be made to function
 0.4Dimension with 1000 textual attributes is a practical configuration

7. Sparsity Tolerance
Rating=1.0

Characteristic 7: Any single measurement can exist within a space of many dimensions, which can be viewed as extraordinarily sparse. The system imposes no practical limit on the degree of sparsity. A 20-dimensional database, each of whose dimensions has a million or more members, is practical.

Analysis Services manages sparsity perfectly, devoting no storage space to a Null fact. A cube with twenty dimensions is possible, practical—even commonplace. A sparse cube with twenty dimensions each with a million members is practical.

Met?Feature WeightFeature
Aa902646.kimballcriteria_checkmark(en-us,SQL.80).gif0.5Twenty dimensions is a practical configuration.
Aa902646.kimballcriteria_checkmark(en-us,SQL.80).gif0.5Twenty dimensions, each with one million members, is a practical configuration.

Administrative Criteria

8. Graceful Modification
Rating=1.0

Characteristic 8: The system must allow the following modifications to be made in place without dropping or reloading the primary database: a) adding an attribute to a dimension; b) adding a new kind of fact to a measurement set, possibly beginning at a specific point in time; c) adding a whole new dimension to a set of existing measurements; and d) splitting an existing dimension into two or more new dimensions.

Analysis Services allows the following modifications to be made in place without reloading the cube:

  1. Add a member property to any level of a dimension
  2. Add, move, rename, or delete levels in a "Changing" dimension
  3. Add a new virtual dimension
  4. Split an existing dimension into two or more new dimensions by using virtual dimensions

To add a new kind of fact to the cube without rebuilding, the administrator would define a second physical cube with all the same dimensions and only the new measure and a virtual cube across the two physical cubes. Query performance is excellent, but the solution does require additional disk space. By following the recommended practice of always using virtual cubes for end-user access, such a modification in the cube definition would not require any change to user applications. Note that as Analysis Services can read, write detailed data, and compute and store aggregations at rates of 35 GB/hour of source data (Sep-2000), the cost of fully rebuilding even terabyte-scale cubes is not necessarily prohibitive.

In the relational DW, the schema structure is easily modified using the ALTER TABLE command.

Met?Feature WeightFeature
Aa902646.kimballcriteria_checkmark(en-us,SQL.80).gif0.1Adding a dimension attribute does not require dropping and reloading the fact data storage.
Aa902646.kimballcriteria_checkmark(en-us,SQL.80).gif0.2Adding a dimension attribute does not require dropping and reloading the dimension table.
Aa902646.kimballcriteria_checkmark(en-us,SQL.80).gif0.2Adding a fact does not require dropping and reloading the fact data storage.
Aa902646.kimballcriteria_checkmark(en-us,SQL.80).gif0.1A fact can be added starting at a point in time without supplying zeros for prior times.
Aa902646.kimballcriteria_checkmark(en-us,SQL.80).gif0.2Adding a dimension does not require dropping and reloading the fact data storage.
Aa902646.kimballcriteria_checkmark(en-us,SQL.80).gif0.2A dimension can be split into two dimensions without dropping or reloading the fact data storage.

9. Dimensional Replication
Rating=1.0

Characteristic 9: The system supports the explicit replication of a conformed dimension outward from a dimension authority to all the client data marts, in such a way that we can only perform drill-across queries on data marts if they have consistent versions of the dimensions. Aggregates that are affected by changes to the content of a dimension are automatically taken offline in each client data mart until we can make them consistent with the revised dimension and the base fact table.

Analysis Services uses cubes that have shared dimensions and virtual cubes to deliver conformed dimension functionality. Analysis Services virtual cubes always use consistent dimension definitions and consistent aggregations; the system always makes accurate information available to users. By default, data loaded incrementally into a cube is not made available to users until the load, including aggregations, is complete. At the option of the administrator, a cube’s new data can be made available to users as soon as the atomic data is loaded and aggregate building would continue in the background. In this case, users would experience slower query performance while the cube is processing as aggregations are computed on the fly to resolve user queries.

Met?Feature WeightFeature
Aa902646.kimballcriteria_checkmark(en-us,SQL.80).gif0.5Multi-pass queries automatically detect and prohibit inconsistent dimension versions.
Aa902646.kimballcriteria_checkmark(en-us,SQL.80).gif0.5Aggregates invalidated by changes made to dimension tables are automatically taken offline.

10. Dimension Notification
Rating=1.0

Characteristic 10: The system delivers upon request all the records from a production source of a dimension that have changed since the last request. In addition, a reason code is supplied with this dimension notification that allows the data warehouse to distinguish between Type 1 and Type 3 slowly changing dimensions (overwrites) and Type 2 slowly changing dimensions (true physical changes at a point in time).

SQL Server Data Transformation Services (DTS) is a general-purpose Extract, Transformation, Maintenance, and Load (ETML) tool that can easily be set up to transfer only changed records, optionally flagged with reason codes, if the source system has that data available. After storing in a dimensional DW, updates into cubes can be incremental. A cube dimension that is identified as "changing" could be overwritten or appended to as appropriate.

Met?Feature WeightFeature
Aa902646.kimballcriteria_checkmark(en-us,SQL.80).gif0.5The data extract system transfers only changed records from the original source to the staging area.
Aa902646.kimballcriteria_checkmark(en-us,SQL.80).gif0.5A reason code is provided in the transfer that allows the decision to be made between overwrite and dimension record creation.

11. Surrogate Key Administration
Rating=1.0

Characteristic 11: The system implements a surrogate key pipeline process or its equivalent for: a) assigning new keys when the system encounters a Type 2 slowly changing dimension; and b) replacing the natural keys in a fact record with the correct surrogate keys before loading into the fact data table. In other words, the cardinality of a dimension can be made independent from the definition of the original production key. Surrogate keys, by definition, must have no semantics or ordering that makes their individual values relevant to an application. Surrogate keys must support not-applicable, nonexistent, and corrupted measurement data. A surrogate key may not be visible to an end-user application.

The Data Warehouse Framework (DTS, RDBMS, and Analysis) does not impose restrictions on key types. DW designs that use surrogate keys are the recommended practice, and are fully supported. Good system design enables nonapplicable, nonexistent, and corrupted data to be identified in the cube.

Met?Feature WeightFeature
Aa902646.kimballcriteria_checkmark(en-us,SQL.80).gif0.5Natural keys and/or smart keys are not used as the basis for linking dimensions to facts.
Aa902646.kimballcriteria_checkmark(en-us,SQL.80).gif0.5Nonapplicable, nonexistent, and corrupted data are allowed and identified.

12. International Consistency
Rating=1.0

Characteristic 12: The system supports the administration of international language versions of dimensions by guaranteeing that a translated dimension possesses the same grouping cardinality as the original dimension. The system supports the UNICODE character set, as well as all common international numerical punctuation and formatting alternatives. Incompatible language-specific collating sequences are allowed.

SQL Server 2000, including Analysis Services, is fully localizable and supports UNICODE data. Analysis Services also provides Caption and Language features that allow a cube to tailor member properties to users with specific language requirements. With this feature, a single cube can serve groups of users who do not share a common language.

Met?Feature WeightFeature
Aa902646.kimballcriteria_checkmark(en-us,SQL.80).gif0.3Support is provided for all standard European accented characters (as in extended ASCII).
Aa902646.kimballcriteria_checkmark(en-us,SQL.80).gif0.7Support is provided for the 16-bit UNICODE character set.

Expression Criteria

13. Multiple Dimension Hierarchies
Rating=1.0

Characteristic 13: The system allows a single dimension to contain multiple independent hierarchies. No practical limit exists to the number of hierarchies in a single dimension. Hierarchies may be complete (encompassing all the members of a dimension) or partial (encompassing only a selected subset of the members of a dimension). Two hierarchies do not necessarily have common levels or common attributes (fields), and may have different numbers of levels. Two hierarchies may also share one or more common levels but otherwise be uncorrelated.

Analysis Services allows a single dimension to contain multiple independent hierarchies. Two hierarchies on the same dimension are not required to have any levels or attributes in common. Multiple hierarchies may have different numbers of levels or share one or more levels.

Met?Feature WeightFeature
Aa902646.kimballcriteria_checkmark(en-us,SQL.80).gif0.5Multiple independent hierarchies in a single dimension allowed
Aa902646.kimballcriteria_checkmark(en-us,SQL.80).gif0.1Complete and partial hierarchies allowed
Aa902646.kimballcriteria_checkmark(en-us,SQL.80).gif0.1Independent hierarchies need have no common levels
Aa902646.kimballcriteria_checkmark(en-us,SQL.80).gif0.2Independent hierarchies may have different number of levels
Aa902646.kimballcriteria_checkmark(en-us,SQL.80).gif0.1Independent hierarchies may have one or more levels in common

14. Ragged Dimension Hierarchies
Rating=1.0

Characteristic 14: The system allows dimension hierarchies of indeterminate depth, such as organization charts and parts explosions, where records in the dimension can play the role of parents as well as children. Using this terminology, a parent may have any number of children, and these children may have other children, to an arbitrary depth limited only by the number of records in the dimension. A child may have multiple parents, where these parents’ "total ownership" of the child is explicitly represented and adds up to 100 percent. With a single command the system must be able to summarize a numeric measure from a fact table (or cube) on a ragged hierarchy for all members:
  1. Starting with a specified parent and descending to all the lowest possible levels summarizing all intermediate levels
  2. Starting with a specified parent and summarizing only children exactly n levels down from the parent or n levels up from the lowest child of any branch of the hierarchy, where n is equal to or greater than zero
  3. Starting with a specified child and summarizing all the parents from that child to the supreme parent in that child’s hierarchy
  4. Starting with a specified child and summarizing all the parents exactly n levels upward in the hierarchy from that child
  5. Starting with a specified child and summarizing only that child’s unique supreme parent. A given ragged dimension hierarchy may contain an arbitrary number of independent families (independent supreme parents with no common children). Conversely, independent supreme parents may share some children as [I] stated previously when discussing total ownership.

The Analysis Services parent-child dimension structure supports hierarchies of indeterminate depth, such as organization charts. The Analysis Services query syntax built-in functions such as "Descendants" and "Ancestors" support the queries described for Criterion 14. Additionally, a child can have multiple parents, with the "ownership" of the child stored in member properties and Custom Rollups on the dimension used to allocate those ownership percentages.

Met?Feature WeightFeature
Aa902646.kimballcriteria_checkmark(en-us,SQL.80).gif0.2Ragged hierarchy of indeterminate depth within a dimension may be specified
Aa902646.kimballcriteria_checkmark(en-us,SQL.80).gif0.2Single command summarizes a fact at all levels below a parent
Aa902646.kimballcriteria_checkmark(en-us,SQL.80).gif0.2Single command summarizes a fact n levels below a parent or n levels from lowest child
Aa902646.kimballcriteria_checkmark(en-us,SQL.80).gif0.1Single command summarizes a fact at all levels above a child
Aa902646.kimballcriteria_checkmark(en-us,SQL.80).gif0.1Single command summarizes a fact n levels above a child or n levels from supreme parent
Aa902646.kimballcriteria_checkmark(en-us,SQL.80).gif0.1Joint ownership of a child by more than one parent possible, with explicit ownership prorations
Aa902646.kimballcriteria_checkmark(en-us,SQL.80).gif0.1Multiple independent families possible within single ragged hierarchy

15. Multiple-Valued Dimensions
Rating=0.0

Characteristic 15: A single atomic measure in a fact table (or cube) may have multiple members from a dimension associated with that measure. If more than one member from a dimension is associated with a measure, then an explicit allocation factor is provided that optionally lets the numeric measure spread across the associated dimension’s members. In such a case, the allocation factors for a given atomic measure and a given multivalued dimension must add up to 100 percent.

Multiple-valued dimensions are used to resolve many-to-many relationships: for example, in an account balances schema, customers have many accounts and each account may belong to multiple customers. Such a system could be implemented in Analysis Services by preallocating and resolving the many-to-many relationships: in this case, by including both Customer and Account as separate dimensions. A separate cube could be built to fully explore the relationships between customer and account.

While this approach would deliver all the specified functionality, fundamentally it does so by increasing the number of facts in the cube, which does not appear to meet the spirit of the requirement. Specifically, while the relational dimensional model would have a single row for an account balance in the schema described above, the cube representation of the schema would contain a fact "row" for each combination of customer and account. Note that because of Analysis Services’ advanced compression algorithms, such a cube in many cases would require less storage and would perform better than the corresponding normalized multiple-valued dimension version stored in the RDBMS.

Met?Feature WeightFeature
 0.4Single measure may be associated with arbitrary number of values of a dimension attribute.
 0.3Different instances of a measure in fact table (or cube) may have different number of multiple dimension attribute values.
 0.3Optional allocation factor allows spreading multiple dimension attributes values across any numeric measure.

16. Slowly Changing Dimensions (SCDs)
Rating=0.9

Characteristic 16: The system must explicitly support the three basic types of slowly changing dimensions: Type 1, where a changed dimension attribute is overwritten; Type 2, where a changed dimension attribute causes a new dimension member to be created; and Type 3, where a changed dimension attribute causes an alternate attribute to be created so that both the old and new values of the attribute are simultaneously accessible in the same dimension member record. Support for slowly changing dimensions must be system-wide, as the following requirements imply:

  • Changes to a dimension that invalidate any physically stored aggregate must automatically disqualify that aggregate from use.
  • A Type 2 change must trigger the automatic assignment of a new surrogate key for the new dimension member, and that key must be used for all concurrent fact records loaded into the system. In other words, the creation of a new Type 2 dimension member must automatically link to the associated concurrent facts without the user or application developer needing to bookkeep beginning and ending effective dates.
  • If the system supports ragged-hierarchy dimensions and/or multiple valued dimensions, then these types of dimensions must support all three types of slowly changing dimensions.

Analysis Services supports slowly changing dimensions types 1, 2, and 3. Type 1 is most efficiently managed if the dimension is identified during design time as "changing." Type 3 is most efficiently managed as a virtual dimension. Type 2 is trivial to implement in Analysis Services when the cube is built from a dimensional relational schema that uses Type 2 SCDs. All types of slowly changing dimensions are managed in the relational DW using standard techniques.

Met?Feature WeightFeature
Aa902646.kimballcriteria_checkmark(en-us,SQL.80).gif0.1A dimension attribute can be overwritten in place (type 1).
Aa902646.kimballcriteria_checkmark(en-us,SQL.80).gif0.6A new record (or member) in the dimension can be created that automatically applies to a set of measured facts beginning at a point in time (type 2).
Aa902646.kimballcriteria_checkmark(en-us,SQL.80).gif0.1An alternate attribute to an existing attribute can be specified to support a "switch of scenarios" (type 3).
Aa902646.kimballcriteria_checkmark(en-us,SQL.80).gif0.1Ragged hierarchies within dimensions support all three SCD types.
 0.1Multiple-valued dimensions support all three SCD types.

17. Roles Of A Dimension
Rating=1.0

Characteristic 17: A single dimension must be associative with a set of facts via multiple roles. For instance, a set of facts may have several independent timestamps that you can simultaneously apply to the facts. In this case, a single underlying time dimension must be able to attach to these facts multiple times, where each instance is semantically independent. A given set of facts may have several different kinds of dimensions, each playing multiple roles.

The functionality of dimension roles is provided in Analysis Services by building separate but identical dimensions named as appropriate for each role. This is similar in spirit to the relational dimensional model, where the typical solution is to have one physical table that has multiple names or views. If the multiple-role dimension is extremely large and hence it is undesirable to instantiate it more than once, the developer should implement the dimension as a ROLAP dimension (stored in the RDBMS), which has multiple aliases.

Met?Feature WeightFeature
Aa902646.kimballcriteria_checkmark(en-us,SQL.80).gif0.5A single physical dimension may be attached multiple times to a set of facts representing separate logical roles.
Aa902646.kimballcriteria_checkmark(en-us,SQL.80).gif0.5More than one type of hot-swappable dimensions may be multiply attached simultaneously to a set of facts.

18. Hot-Swappable Dimensions
Rating=1.0

Characteristic 18: The system must allow an alternate instance of a dimension to swap in at query time. For example, if two clients of an investment firm wish to view the same stock market data through their own proprietary "stock ticker" dimensions, then the two clients must be able to use their versions of the dimension at query time, without requiring the fundamental fact table (or cube) of stock market facts to duplicate. Another example of this capability would let a bank attach an extended account dimension to a specific query if the user restricts the query to a cluster of accounts of the same type.

Analysis Services virtual dimensions provide the functionality of "hot-swappable" dimensions.

Met?Feature WeightFeature
Aa902646.kimballcriteria_checkmark(en-us,SQL.80).gif1.0A different instance of a dimension may be attached to a set of facts at query time. Such a dimension need only to have the same primary keys as the first dimension so as to preserve referential integrity with the fact data storage but otherwise can have completely different structure and data.

19. On-the-Fly Fact Range Dimensions
Rating=1.0

Characteristic 19: The system provides direct support for dynamic value banding queries on numeric measures in a fact table (or cube). In other words, at query time the user can specify a set of value ranges and use these ranges as the grouping criteria in a query. All of the normal summarizing functions (count, sum, min, max, and average) can be applied within each group. The sizes of the value bands needn’t be equal.

Analysis Services query syntax supports user-defined computations that would use the "IIF" function to compute on-the-fly fact range bands. A client tool could easily be developed that would present an appealing user interface for this functionality.

Met?Feature WeightFeature
Aa902646.kimballcriteria_checkmark(en-us,SQL.80).gif0.8Value-banding queries can be executed in a single command that specify an ad hoc set of unequal-sized value ranges and use these ranges as the grouping criteria.
Aa902646.kimballcriteria_checkmark(en-us,SQL.80).gif0.2All summarizing functions can be used in these dynamically defined value bands.

20. On-the-Fly Behavior Dimensions
Rating=0.6

Characteristic 20: The system supports constraining a dimension via a simple list of that dimension. For the sake of vocabulary, call such a list of members a "behavior dimension." The support of behavior dimensions must be system wide, as the following requirements imply:
  • A behavior dimension can be captured from a report showing on the user’s screen from: a list of keys or attributes appearing in a file extracted from a production source; directly from a constraint specification; or from a union, intersection, or set difference of other behavior dimensions.
  • A user may have a library of many behavior dimensions and can attach a behavior dimension to a fact table (or cube) at query time.
  • The use of a behavior dimension in a query restricts the fact table (or cube) to the members in the study but in no way otherwise limits the ability to select and constrain attributes of any regular dimension, including the one the behavior affects directly.
  • A behavior dimension may be of unlimited size.
  • A behavior dimension may have an optional date-stamp associated with each element of the list in such a way that two behavior dimensions can merge so that membership in the combined behavior dimension requires a specific time ordering.

The Analysis Services query syntax supports the full richness of on-the-fly "behavior dimensions." However, some of the functionality described in this criterion is directly associated with client tools. The functionality in question—capturing a list of members from an end-user screen or from a flat file—could very easily be incorporated into a custom or third-party tool. For example, the not-yet-released Microsoft¬ Office 10 product suite supports this functionality.

The query syntax supports constraining a dimension via a simple list of members of that dimension. Most existing client tools support the selection of an arbitrary set of members from the screen; it would be very simple for a tool to add the functionality to select from a flat file or other source. The Analysis Services query syntax is designed explicitly to support other required behavior, such as defining a constraint; selecting members from a set expression such as union, intersection, or difference; or sorting/merging member sets.

Met?Feature WeightFeature
N/AN/AAn arbitrary set of members of a dimension can be captured from an end user screen.
N/AN/AAn arbitrary set of members of a dimension can be specified from a flat file from an outside source containing attribute values drawn from the dimension.
Aa902646.kimballcriteria_checkmark(en-us,SQL.80).gif0.1An arbitrary set of members of a dimension can be specified by a constraint on that dimension.
Aa902646.kimballcriteria_checkmark(en-us,SQL.80).gif0.1An arbitrary set of members of a dimension can be specified via a union, intersection, or set difference of other arbitrary sets of members of that dimension.
Aa902646.kimballcriteria_checkmark(en-us,SQL.80).gif0.3An arbitrary set of members of a dimension can be used as a constraint against a dimension but otherwise does not prohibit other constraints or user interface actions on that dimension, and that constrained dimension can be used with any fact table (or cube) joined to that dimension.
Aa902646.kimballcriteria_checkmark(en-us,SQL.80).gif0.1An arbitrary set of members of the dimension can be time stamped so that membership in derived sets of members can be based on time sequence ordering.

Conclusion

The SQL Server 2000 Data Warehousing Framework is a natural infrastructure for developing a dimensional system. Analysis Services' modern architecture addresses many of the complex dimensional modeling issues outlined in Kimball's "20 characteristics of a dimensionally friendly system."

For More Information

SQL Server 2000 Books Online contains more information about the Microsoft Data Warehousing Framework, the SQL Server relational database, Data Transformation Services, and Analysis Services. For additional information, see the following resources:

For additional information on Ralph Kimball’s twenty criteria for a dimensionally friendly system, see the following resources:

Endnotes

1. Ralph Kimball has published his twenty characteristics in two articles in Intelligent Enterprise. The first twelve appear in “Rating Your Dimensional Data Warehouse,” Intelligent Enterprise 3 no. 7 (28 April 2000) 30, 32-33. The final eight appear in “Is Your Dimensional Data Warehouse Expressive?” Intelligent Enterprise 3, no. 8 (15 May 2000) 40, 44. This article can also be viewed at http://www.intelligententerprise.com/000515/webhouse.shtml.

2. Kimball, Ralph. “Rating Your Dimensional Data Warehouse,” Intelligent Enterprise 3 no. 7 (28 April 2000) 30. This article can also be viewed at http://www.intelligententerprise.com/000428/webhouse.shtml.

3. Features and Feature Weights are taken from Ralph Kimball, “Twenty Criteria for Dimensionally Friendly Systems” (pending publication).

The information contained in this document represents the current view of Microsoft Corporation on the issues discussed as of the date of publication. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information presented after the date of publication.

This white paper is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, AS TO THE INFORMATION IN THIS DOCUMENT.

Complying with all applicable copyright laws is the responsibility of the user. Without limiting the rights under copyright, no part of this document may be reproduced, stored in or introduced into a retrieval system, or transmitted in any form or by any means (electronic, mechanical, photocopying, recording, or otherwise), or for any purpose, without the express written permission of Microsoft Corporation.

Microsoft may have patents, patent applications, trademarks, copyrights, or other intellectual property rights covering subject matter in this document. Except as expressly provided in any written license agreement from Microsoft, the furnishing of this document does not give you any license to these patents, trademarks, copyrights, or other intellectual property.

© 2000 Microsoft Corporation. All rights reserved.

Microsoft is a registered trademarks of Microsoft Corporation in the United States and/or other countries.

The names of actual companies and products mentioned herein may be the trademarks of their respective owners.

Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft. All rights reserved.