Overview of Multidimensional Schemas and Data

Understanding Multidimensional Schemas

The central metadata object in ADO MD is the cube, consisting of a structured set of related dimensions, hierarchies, levels, and members. Members can be consolidated or aggregated along one or more hierarchies per dimension, meaning that members can be totaled or "rolled up" in multiple ways.

Dimensions

Consider a Sales Data cube with six dimensions: Salesperson, Geography, Quarters, Years, Measures, and Products. The Geography dimension has the following set of members (only two continents are used for the sake of simplicity):

{All, North America, Europe, Canada, USA, UK, Germany, Canada-West, Canada-East, USA-NW, USA-SW, USA-NE, USA-SE, England, Scotland, Wales, Ireland, Germany-North, Germany-South, Ottawa, Toronto, Vancouver, Calgary, Seattle, Boise, Los Angeles, Houston, Shreveport, Miami, Boston, New York, London, Dover, Glasgow, Edinburgh, Cardiff, Pembroke, Belfast, Londonderry, Berlin, Hamburg, Munich, Stuttgart}

Hierarchies

A natural hierarchy exists in this dimension:

Levels

Each box is a level in the hierarchy. Each level has a set of members, as follows:

• The World = {All}
  
  
• Continents = {North America, Europe}
  
  
• Countries = {Canada, USA, UK, Germany}
  
  
• Regions = {Canada-East, Canada-West, USA-NE, USA-NW, USA-SE, USA-SW, England, Ireland, Scotland, Wales, Germany-North, Germany-South}
  
  
• Cities = {Ottawa, Toronto, Vancouver, Calgary, Seattle, Boise, Los Angeles, Houston, Shreveport, Miami, Boston, New York, London, Dover, Glasgow, Edinburgh, Cardiff, Pembroke, Belfast, Londonderry, Berlin, Hamburg, Munich, Stuttgart}

Members

Members at the leaf level have no children, and member 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:

• {All} (parent of) {Europe, North America}
  
  
• {North America} (parent of) {Canada, USA}
  
  
• {USA} (parent of) {USA-NE, USA-NW, USA-SE, USA-SW}
  
  
• {USA-NW} (parent of) {Boise, Seattle}

Multiple simultaneous hierarchies (multiple aggregation paths) are often useful. Consider the Time dimension of the Sales Data cube. There are two ways to roll up to the Year level from the Days level:

This example also 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. Thus, a hierarchy need not include all members of a dimension.