Multidimensional Schemas

OLE DB Programmer's Reference

OLE DB for OLAP has interfaces and methods that allow providers to expose schemas. The OLE DB for OLAP specification does not mandate that MDPs expose its defined schema rowsets; it simply enables those schemas to be exposed if the MDP chooses to support them.

OLE DB for OLAP also provides interfaces that enable consumers to browse a set of metadata objects. An application can use these objects to access the data. Most providers expose a subset of the OLE DB for OLAP metadata objects.

The central metadata object recognized by OLE DB for OLAP is the hypercube, often referred to in this documentation simply as the cube. The hypercube consists of a set of related dimensions that defines an n-dimensional manifold. The data points of interest are the points in this manifold: Each point is uniquely identified by a set of coordinates, and each coordinate is a member of one component dimension. For example, SalesData is a cube with seven component dimensions: SalesRep, Geography, Months, Quarters, Years, Measures, and Products.

Note   For complete symmetry between dimensions, OLE DB for OLAP handles the computational dimensions (in this case, the Measures dimension) the same way it handles all other dimensions.

A multidimensional schema is composed of a set of cubes. The main purpose of a schema is to impose high-level authorization policies. A set of schemas is grouped together into a catalog. A provider does not have to support this two-level grouping system just because catalogs and schemas exist. The provider might support catalogs only, schemas only, both, or neither.

Dimensions

A hypercube has a set of dimensions. Each dimension consists of a set of members. The members of a dimension can be consolidated or aggregated along a hierarchy. Some dimensions can have more than one hierarchy. This means that members can aggregate, or roll up, in multiple ways. Each hierarchy has levels, and each level is made up of a set of members that is a subset of the members of a dimension.

For example, consider the SalesData cube Dimensions/Members list. The Geography dimension contains many members grouped under one root-level member: The World. Viewing the dataset object resulting from an unorganized query would be of minimal value. Organizing members into hierarchies increases the power and flexibility of dimensions.

Hierarchies

A natural hierarchy exists in the Geography dimension, as shown in the following illustration:

Each of these categories is a level. Each level has a list of members associated with it as follows:

• The World = {[All]}
• Continents = {Europe, North America}
• Countries = {Canada, Germany, UK, USA}
• Regions = {Canada_East, Canada_West, England, Germany-North, Germany-South, Ireland, Scotland, USA_North, USA-_South, USA_East, USA_West, Wales}
• Cities = {Belfast, Berlin, Boise, Boston, Calgary, Cardiff, Cookstown, Dover, Edinburgh, Glasgow, Hamburg, Houston, Los Angeles, London, Miami, Munich, New York, Ottawa, Pembroke, Seattle, Shreveport, Stuttgart, Toronto, Vancouver}

Members at the leaf level, presented here as cities, have no children, and members at the root level have no parents. All other members have at least one parent and at least one child. For example, a partial traversal of the hierarchy tree in the Geography dimension yields the following parent-child relationships:

Multiple hierarchies built from the same dimension can be very useful, both in data storage and retrieval. The Quarters dimension in the SalesData Dimensions/Members list contains members that provide many views of the data. The following illustration taken from the SalesData cube shows two ways to roll up to the Year level from the Quarters level:

The preceding example illustrates another characteristic: Some members of the Week level of the Year-Week hierarchy do not appear in any level of the Year-Quarter hierarchy. That is, a hierarchy need not include all members of a dimension. However, if there is just a single hierarchy in a dimension, it is common for all members of the dimension to be included in the hierarchy.

Level Properties

Each piece of data in any given level can have properties assigned to it. In the SalesRep dimension, there is usually a need to store additional data about each salesperson—for example, data such as Address or Phone Number. Therefore, to store this data, each level of a hierarchy tends to have a different set of properties, while all members in a given level have the same set of properties. For example, consider the Geography dimension: Each city can have the properties NUMBER_OF_RETAIL_OUTLETS, WAREHOUSE_NAME, and SALESREP, but these properties might not apply at the Region level. Instead, a region might have properties such as REGIONAL_MANAGER and IMPORT_POINT.

1998-2001 Microsoft Corporation. All rights reserved.