Flexible Data Model

By supporting various data and storage models, Microsoft® SQL Server™ 2000 Analysis Services helps you create and maintain a system that meets your organization's needs.

Multiple Data Storage Options

Analysis Services offers three storage modes for dimensions, partitions, and cubes:

• Multidimensional OLAP (MOLAP)

  The underlying data for a cube is stored along with aggregation data in a high-performance multidimensional structure. MOLAP storage provides excellent performance and data compression.

• Relational OLAP (ROLAP)

  The underlying data for a cube is stored along with the aggregation data in a relational database. ROLAP storage enables you to take advantage of your investment in relational technology and enterprise data management tools.

• Hybrid OLAP (HOLAP)

  The underlying data for a cube is stored in a relational database and the aggregation data is stored in a high-performance multidimensional structure. HOLAP storage offers the benefits of MOLAP for aggregations without necessitating duplication of the underlying detail data.

Note  Dimensions with more than 10 million members must use the ROLAP storage mode. This feature is available only if you install Analysis Services for SQL Server 2000 Enterprise Edition.

Partitioned Cube Storage

You can partition a cube into separate physical sections. Each partition can be stored in a different mode, in a different physical location, and with a level of aggregations appropriate to the data in the partition. The result is that you can fine-tune the performance and data management characteristics of your system.

Note  This feature is available only if you install Analysis Services for SQL Server 2000 Enterprise Edition.

Partition Merging

You can combine a cube's multiple partitions back into a single physical partition. For example, you can use partition merging to consolidate portions of cube data, such as data for a just completed quarter into a single partition for the year.

Write-Enabled Cubes

You can enable a cube for write access by multiple simultaneous users. User-initiated changes to the cube data are logged to a special, physically separated partition table associated with the cube and applied automatically as cube data is viewed. To the user it appears as if the data in the cube has changed. The changes can be discarded or made read-only at the discretion of the database administrator (DBA).

Balanced, Unbalanced, and Ragged Hierarchies

You can create dimensions with balanced or unbalanced hierarchies. Dimensions with balanced hierarchies have all branches of the hierarchy end at the same level while branches of unbalanced hierarchies terminate at different levels.

Ragged hierarchies are also supported. This allows dimensions in which at least one member does not have its logical parent in the level immediately above the member to accommodate levels where no values exist; the logical parent of a member in a ragged hierarchy can be two levels above the member.

Parent-Child Dimensions

You can create a dimension based on two dimension table columns that together define parent-child relationships between rows in the dimension table. Parent-child dimensions support balanced, unbalanced, and ragged hierarchies; complex hierarchical relationships can be easily created using parent-child dimensions.

Write-Enabled Dimensions

You can enable a dimension for write access by multiple simultaneous users. User-initiated changes to the dimension data are recorded in the dimension table. Users can manipulate the dimension data to see the immediate effect on the cube data.

Note  This feature is available only if you install Analysis Services for SQL Server 2000 Enterprise Edition.

Virtual Cubes

You can join cubes into virtual cubes, much like tables can be joined with views in a relational database. A virtual cube provides access to data in the combined cubes without necessitating the construction of a new cube, while it allows you to maintain the best design for each individual cube.

Calculated Members

You can create calculated measures and calculated dimension members by combining Multidimensional Expressions (MDX), mathematical formulas, and user-defined functions. This facility enables you to define new measures and dimension members based on a rich yet easy-to-use expression syntax. You can register additional libraries of user-defined functions to use in calculated member definitions.

Custom Unary Operators

Custom unary operators use simple math operators, called unary operators, stored in a column to determine how the value of a level member affects the value of the parent. Custom rollup operators are unique per level member.

Custom Rollup Formulas and Custom Member Formulas

Custom rollup formulas and custom member formulas are MDX expressions that determine cube cell values associated with members. Custom rollup formulas apply to all members of a level, whereas custom member formulas apply to individual level members.

Calculated Cells

Similar to custom member formulas, calculated cells are MDX statements that determine cube cell values associated with a specified group of cells. Calculated cells apply only to specified cells in a cube, whereas custom member formulas must apply to all of the cells for a given member.

Note  This feature is available only if you install Analysis Services for SQL Server 2000 Enterprise Edition.

Member Properties

You can define properties for dimension members and use data for these properties within a cube. For example, if the members of a Product dimension are SKUs, there are likely to be several properties associated with SKUs, such as size, color, fabric, and so on. You can specify such properties as member properties and use them in analytical queries.

Virtual Dimensions

A virtual dimension can be created from member properties or levels of another dimension. A virtual dimension can be used to evaluate the properties of a dimension's members against the members themselves. For example, measures can be evaluated for SKUs against size, color, fabric, and so on. Virtual dimensions and member properties are evaluated as necessary for queries and require no physical cube storage.