• Article

Key Concepts in MDX (MDX)

You can use Multidimensional Expressions (MDX) to query multidimensional data or to create MDX expressions for use within a cube, but first you need to understand Microsoft SQL Server Analysis Services dimension concepts and terminology. The following section provides a quick description of the basic dimensional modeling concepts and terminology that you need. The sections that follow illustrate how to apply these concepts.

Dimensional Modeling Terms and Concepts

An Microsoft SQL Server Analysis Services cube is organized based on measures, dimensions, and dimension attributes. The following table describes the dimensional modeling terms and concepts that you need to understand to work with the MDX expression language.

  • (All) member
    The (All) member is the calculated value of all members in an attribute hierarchy or a user-defined hierarchy.

  • Attribute hierarchy
    An attribute hierarchy is a hierarchy of attribute members that contains the following levels:

    • A leaf level that contains each distinct attribute member, with each member of the leaf level also known as a leaf member.

    • Intermediate levels if the attribute hierarchy is a parent-child hierarchy.

    • An optional (All) level (IsAggregatable=True) containing the aggregated value of the attribute hierarchy's leaf members, with the member of the (All) level also known as the (All) member.

    By default, an attribute hierarchy is defined for each dimension attribute (AttributeHierarchyEnabled=True). Attribute hierarchies are visible by default (AttributeHierarchyVisible=True).

  • Attribute relationship
    An attribute relationship is a one-to-many relationship between attributes, for example a relationship between a state and a city dimension attribute.

  • Balanced hierarchy
    A balanced hierarchy is a hierarchy in which the same number of levels exists between the top level and any leaf member.

  • Calculated member
    A calculated member is a dimension member that is defined and calculated at query time. A calculated member can be defined in a user query or in the MDX calculation script and stored on the server. A calculated member corresponds to rows in the dimension table of the dimension in which it is defined.

  • Cell
    A cell in a cube is the space that exists at the intersection of a member of the measures dimension member and a member from each attribute hierarchy in a cube.

    • A member from the measures dimension can be a leaf member (an individual fact) or an aggregated member (for example, sales aggregated for a particular year).

    • A member from a dimension can be the leaf member, a data member, a parent member, or an (All) member.

  • Child member
    A child member is a member in a hierarchy below the top level.

  • Cube dimension
    A cube dimension is an instance of a database dimension in a cube.

  • Cube space
    Cube space is the product of the members of a cube's attribute hierarchies with the cube's measures.

  • Data member
    A data member is a child member associated with a parent member in a parent-child hierarchy. A data member contains the data value for its parent member, rather than the aggregated value for the parent's children.

  • Database dimension
    A database dimension is a collection of dimension attributes that are related to a key attribute, which in turn relates to facts in the measures dimension.

  • Dimension attribute
    A dimension attribute is bound to one or more columns in a dimension table and contain members. A dimension attribute might contain customer names, month names, product names.

  • Granularity attribute
    The attribute of a cube dimension that links a dimension to the facts in a measure group in the measures dimension. If the granularity attribute and the key attribute are different attributes, then non-key attributes must be linked, directly or indirectly, to the granularity attribute. Within a cube, the granularity attribute defines a dimension's granularity.

  • Key attribute
    The key attribute of a database dimension is the attribute to which all non-key attributes in the dimension are linked (directly or indirectly). The key attribute is also often the granularity attribute.

  • Leaf member
    A leaf member is a member of a hierarchy that has no children.

  • Measure
    A measure is a value from a fact table, and is also called a fact. A value in the measures dimension is also generically sometimes referred to as a member. Measures are generally numeric values, but can also be string values.

  • Measure Group
    A measure group is a collection of related measures in a cube in SQL Server Analysis Services (generally measures from the same fact table). In SQL Server Analysis Services, a cube can contain multiple measure groups.

  • Measures dimension
    A measures dimension is the dimension that contains all of the measures in a cube. A measures dimension is a special type of dimension in which the members are typically aggregated (generally by sum or by count) based on the current member of each dimension attribute with which a specified measure exists.

  • Member
    A member is a value of a dimension attribute, including the measures dimension. A member in a hierarchy can be a leaf member, a parent member, a data member, or an (All) member.

  • Member property
    A member property is a property of an attribute member, for example the gender of a customer or the color of a product.

  • Parent member
    A parent member is a member of a parent-child hierarchy that contains the aggregated value of its children.

  • Parent-child hierarchy
    A parent-child hierarchy is a special type of attribute hierarchy in which an attribute in the dimension is set to type parent. A parent-child hierarchy is an unbalanced hierarchy of child and parent members. A parent-child hierarchy contains the following levels:

    • Child levels that contain the children of parent members. The children of a parent include the attribute members that aggregate to the parent member, including data members.

    • Intermediate levels that contain parent members.

    • An optional (All) level (IsAggregatable=True) that contains the aggregated value of the parent-child hierarchies leaf members, with the member of the (All) level also known as the (All) member.

    • Only one parent-child hierarchy can exist per dimension and must be related to the key attribute.

  • Ragged hierarchy
    See unbalanced hierarchy.

  • Subcube
    A subcube is a subset of a cube that represents a filtered view of the cube. Subcubes can be defined with a Scope statement in the MDX calculation script or in a subselect clause in an MDX query or as a session cube.

  • Subcube with Subselect
    A subcube defined with a subselect clause in an MDX query includes all members existing with the subcube definition, with the following consequences:

    • Including any member includes its ascendants and descendants.

    • Including every member from a level in a user-defined hierarchy includes all members from the user-defined hierarchy, but can exclude members from other hierarchies that do not exist with members from the level (such as a city that does not contain customers).

    • Every (All) member in the cube always exists in subcubes created from the cube.

    • All values, when aggregated in the subcube, will reflect only the space of the new cube and not the space of the exterior cube from which this cube is being derived.

  • Unbalanced hierarchy
    An unbalanced hierarchy is a hierarchy in which different numbers of levels exist between the top level and the leaf members. An example of an unbalanced hierarchy is a parent-child hierarchy. An unbalanced hierarchy is also called a ragged hierarchy.

  • User-defined hierarchy
    A user-defined hierarchy is a balanced hierarchy of attribute hierarchies that is used to facilitate browsing of cube data by users. User-defined hierarchies do not add to cube space. Levels in a user-defined hierarchy can be hidden under certain circumstances and appear unbalanced.