Cube

A cube takes the data from a data warehouse or mart and pre-aggregates (processes) the data for all the dimensions so that MD Analysis can occur quickly.  The core of a cube is its underlying fact table.

• BasicDefinitions
• Miscellany

Basic Definitions

• A cube can be stored in three kinds of databases.
  • MOLAP (MD OLAP). The underlying data and aggregated data are stored in a structure specifically for MD work.
  • ROLAP (Relational OLAP). The underlying data and aggregated data are stored in a relational database that accommodate MD work. ROLAP should be used for dimensions with more than 10 million members
  • HOLAP (Hybrid OLAP). The underlying data is stored in a relational database, but the aggregated data is stored in a structure specifically for MD work.
• A cube can have 1 to 128 dimensions, i.e. different perspectives on the data. These dimensions can be private to the cube or shared between cubes. Dimensions are essentially look up tables to the fact table of a cube. The levels are equivalent to columns in the dimension tables, the values of levels are equivalent to the fields in the dimension table.
  • Time
  • Store
  • Product
  • Measures . The measures themselves are considered to be members of a private dimension, thus the maximum number of dimensions may said to be 129. The measures "dimension" is the fact table.
• A dimension can have 1 or more hierarchies of aggregates. Each step (level of depth) within the hierarchy is called a level. If the dimension has a hierarchies, then when a level is specified, it must be qualified with the hierarchy.
  • The Time dimension of cube A has a single hierarchy.
    • Time. Most dimensions have a single hierarchy.
  • The Time dimension of cube B has two hierarchies.
    • Time.Calendar.Year. Level 0 of the Calendar hierarchy of the Time dimension.
    • Time.Fiscal.Year.Quarter. Level 1 of the Fiscal hierarchy of the Time dimension.
• A span of data within a level is a member.  When specifying a member, the qualification consists of four parts.
  1. Optionally, the dimension (and hierarchy if needed).
     • January.
     • Time.January. Dimension with no  hierarchy.
     • Time.Calendar.Year.Quarter.[Q1].January. Dimension with hierarchy.
  2. Optionally the path of levels.
     • January. No level path specified.
     • Month.January. Level path specified.
     • Time.Calendar.Year.Quarter.[Q1].January. Level path specified.
  3. Optionally the path of members leading to the final member.
     • January. No member path specified.
     • Time.January . No member path specified.
     • Time.Calendar.Year.Quarter.[Q1].January . Member path specified.
  4. The member specified.
     • January .
     • Time.January .
     • Time.Calendar.Year.Quarter.[Q1].January .
     • In the above examples the members are identified by name but a member can also be identified by other means.
       • Time.[1999].January. This member is identified by Name.
       • Time.[1999].&[1]. This member is identified by Key.
       • Time.[1999].FirstChild. This identifies a member by a function that pulls the first member of its parent.
• A tuple specifies one or more coordinates (one member for each dimension) of an MD cube.
  • The name "tuple" is roughly derived as follows: a single coordinate specifies 1D, a double coordinate or pair specifies 2D, a triple specifies 3D, a quadruple specifies 4D, a quintuple specifies 5D, a sextuple specifies 6D,....
  • If a tuple is composed of members from more than one dimension, then it is said to have dimensionality and must be enclosed in parentheses (()).
  • The order of the dimensions listed is irrelevant.
  • If a tuple does not cover all the dimensions of a cube, then it is said to be a partial tuple.
  • EG:
    • Store.IL.Chicago.
    • (Time.[1999], Store.IL).
    • Measures.Revenue.
• A set is an ordered collection of zero or more tuples. Sets are demarcated with curly brackets ({}).
  1. {Time} . This set has one dimension.
  2. {Time.[1999], Time.[2000], Time.[2001]} . This set has three tuples from one dimension.
  3. {Time.[1999], Time.[2001], Time.[2000]} . This is not equivalent to the 2nd example because the order tuples in the set does not match.
  4. {Time.[1999]:Time.[2001]}. This set is equivalent to the 2nd example. If a colon (: ) is used then the 2 specified members must be of the same level.
  5. {Time.Children} . This set includes a set by a function that pulls all the members of its parent.
  6. { (Time.[1999], Store.IL), (Time.[2000], Time.[2001]) } . This set has tuples, each with different dimensionality.
  7. { {Time.[1999]:Time.[2001]}, {Customers.Name.A:Customer.Name:D} } . This set is a set of sets.
• A cell is the intersection of the specified tuples.
  • If a cell is referred to by a partial tuple, then the dimensions not specified are assumed to refer to the current member. The current member of a member is defined by:
    • The axes.
    • The slicer.
    • The default member of the dimension
  • In this example, assume that the cube has 3 dimensions (Time, Store, and Product).
    • (Time.[1999].May.[15], Store.IL.Chicago.[Will E. Coyote Goods], Product.Acme.Rockets.[A1 Turbo]). This is a complete tuple since a member is provided for all 3 dimensions.
    • Store.IL. This is a partial tuple since no member is provided for the other 2 dimensions.
• Each cell can yield values for various measures, i.e. the fact columns of the fact table. EG:
  • Units Sold: 3
  • Revenue: $33,000

Miscellany

• The difference between a cube and its corresponding data warehouse/mart is that the cubes contains pre-aggregated (processed) data for all the dimensions so that OLAP and data mining can occur quickly.
• OLAP objects names are often enclosed with square brackets ([]). An OLAP object name must be bracketed if any of the following 3 conditions are met:
  • The name has a space. EG: [Net Profit].
  • The name starts with a number. EG: [2000].
  • The name is an MDX reserved word. EG: [Select].
• The uniqueness of name and keys of members can be toggled on the dimension or level level
• A virtual cube can be created by joining other cubes.
• Member properties is an attribute of a member. The values are related to columns in the fact table.
• By default a cube has one partition with one data source. However a cube can be made up of multiple partitions, and each partition can have its own data source but the data structure of each must be the same.

Page Modified: (Hand noted: 2007-10-12 01:32:32Z) (Auto noted: 2007-11-17 06:45:57Z)