MDAC 2.5 SDK - OLE DB Programmer's Reference
Chapter 21: OLE DB for OLAP Concepts

Multidimensional Schemas

OLE DB for OLAP provides interfaces and methods for providers to expose schemas. It does not mandate that MDPs expose the schema objects defined in this specification; rather, it simply provides a way for schema objects 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. These objects contain the means by which an application can access the data. Most providers expose a subset of all OLE DB for OLAP metadata objects.

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

Note   OLE DB for OLAP handles the Measures dimension in the same way it handles all other dimensions, so there is complete symmetry between dimensions and measures.

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 cube 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.