Key Concepts in MDX (MDX)

  • Article

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 2005 Analysis Services (SSAS) 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.

For more information, see the Additional Resources section on the SQL Server 2005 – Analysis Services page on the Microsoft TechNet Web site. For more information about performance issues related to MDX queries and calculations, see the section “Writing Efficient MDX” in the SQL Server 2005 Analysis Services Performance Guide.

Dimensional Modeling Terms and Concepts

An Microsoft SQL Server 2005 Analysis Services (SSAS) 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.

  • 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.
  • 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.
  • 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.
  • 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.
  • Measure Group
    A measure group is a collection of related measures in a cube in SQL Server 2005 Analysis Services (generally measures from the same fact table). In SQL Server 2005 Analysis Services, a cube can contain multiple measure groups.
  • (All) member
    The (All) member is the calculated value of all members in an attribute hierarchy or a user-defined hierarchy.
  • 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 in the dimension in which they are defined.
  • 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.
  • Parent member
    A parent member is a member of a parent-child hierarchy that contains the aggregated value of its children.
  • Leaf member
    A leaf member is a member of a hierarchy that has no children.
  • Child member
    A child member is a member in a hierarchy below the top level.
  • 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.
  • 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.
  • Cube dimension
    A cube dimension is an instance of a database dimension in a cube.

  • 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).

  • Balanced hierarchy
    A balanced hierarchy is a hierarchy in which the same number of levels exist between the top level and any leaf member.
  • Ragged hierarchy
    See unbalanced hierarchy.
  • Unbalanced hierarchy
    An unbalanced hierarchy is a hierarchy in which different numbers of levels exist between the top level and the leaf level. An example of a ragged hierarchy is a parent-child hierarchy. An unbalanced hierarchy is also called a ragged hierarchy.

  • 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. An 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.
  • 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.
  • 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.
  • 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.

  • 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.
  • Cube space
    Cube space is the product of the members of a cube's attribute hierarchies with the cube's measures.
  • 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 subselect clause in an MDX query.

  • 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 a hierarchy's (All) member is the same as including every leaf member of the hierarchy.
    • 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.
    • Aggregate values within a subcube are visually totaled.

Tuples

A tuple uniquely identifies a cell, based on a combination of attribute members that consist of an attribute from every attribute hierarchy in the cube. When defining a tuple in an MDX query or expression, you do not need to explicitly include the attribute member from every attribute hierarchy. If a member from an attribute hierarchy is not explicitly included in a query or an expression, the default member for that attribute hierarchy is the attribute member implicitly included in the tuple. Unless otherwise explicitly defined in a cube, the default member for every attribute hierarchy is the (All) member, if an (All) member exists. If an (All) member does not exist within an attribute hierarchy, the default member is a member of the attribute hierarchy's top level. The default measure is the first measure specified in the cube, unless a default measure is explicitly defined. For more information, see Defining a Default Member and DefaultMember (MDX).

For example, the following tuple identifies a single cell in the Adventure Works database by explicitly defining only a single member of the Measures dimension.

(Measures.[Reseller Sales Amount])

The previous example uniquely identifies the cell consisting of the Reseller Sales Amount member from the Measures dimension and the default member from every attribute hierarchy in the cube. The default member is the (All) member for every attribute hierarchy other than the Destination Currency attribute hierarchy. The default member for the Destination Currency hierarchy is the US Dollar member (this default member is defined in the MDX script for the Adventure Works cube).

Important

The member of an attribute hierarchy in a tuple is also affected by relationships that are defined between attributes within a dimension. For more information, see Attribute Relationships and Cube Space below.

The following query returns the value for the cell referenced by the tuple specified in the previous example, ($80,450.596.98).

SELECT 
Measures.[Reseller Sales Amount] ON COLUMNS 
FROM [Adventure Works]

Note

When you specify an axis for a set (in this case composed of a single tuple) in a query, you must begin by specifying a set for the column axis before specifying a set for the row axis. The column axis can also be referred to as axis(0) or simply 0. For more information about MDX queries, see The Basic MDX Query (MDX).

You can use a tuple in a query to return the value in the cell that is referenced by the tuple, as in the previous example. Or you can use a tuple in an expression to explicitly refer to the members specified in the tuple. The query or the expression can utilize functions that either return or consume tuples. A tuple can be used to either refer to the value of the cell that the tuple specifies, or to specify a combination of members when utilized in a function.

The dimensionality of a tuple refers to the sequence or order of the members in the tuple. Since the implicit members always occur in the same order, dimensionality is most often thought of in terms of the explicitly defined members of the tuple. The ordering of the members of the tuple is important when you define a set of tuples. The following example includes two members in a tuple on the column axis.

SELECT 
([Measures].[Reseller Sales Amount],[Date].[Calendar Year].[CY 2004]) ON COLUMNS 
FROM [Adventure Works]

Note

When you explicitly specify a member in a tuple from more than one dimension, you must include the entire tuple in parentheses. When only specifying a single member in a tuple, parentheses are optional.

The tuple in the query in the previous example specifies the return of the cube cell at the intersection of the Reseller Sales Amount Measure of the Measures dimension and the CY 2004 member of the Calendar Year attribute hierarchy in the Date dimension.

Note

An attribute member can be referred by either its member name or its member key. In the previous example, you could replace the reference to [CY 2004] with &[2004].

Sets

A set is an ordered set of tuples with the same dimensionality. The following is an example of a set.

SELECT 
   {
      ([Measures].[Reseller Sales Amount],
         [Date].[Calendar Year].[CY 2003]),
      ([Measures].[Reseller Sales Amount],
         [Date].[Calendar Year].[CY 2004])
   } ON COLUMNS 
FROM [Adventure Works]

Note

Use curly braces {} to designate a set of tuples.

In the previous example, each tuple in the set has the same dimensionality because the first member of each tuple is a member from the Measures dimension and the second member of each tuple is a member from the Calendar Year attribute hierarchy. If the second member of either tuple were from a different attribute hierarchy in the Date dimension (such as Calendar Month), you would receive an error referring to the difference in dimensionality.

Tip

You can create a set with an alias, referred to as a named set. Using a named set makes your MDX query easier to understand or to reuse when working with complex MDX expressions. To use a named set, use the word "AS" followed by the desired alias name after the end of the set identifier.

Cube Space and Auto-Exists

Earlier in this topic, we defined cube space as the product of the members of its attribute hierarchies. The concept of auto-exists limits this cube space to those cells that actually exist. Members of an attribute hierarchy in a dimension may not exist with members of another attribute hierarchy in the same dimension.

For example, if you have a cube that has a City attribute hierarchy, a Country attribute hierarchy, and an Internet Sales Amount measure, the space of this cube only includes those members that exist with each other. For example, if the City attribute hierarchy includes the cities New York, London, Paris, Tokyo, and Melbourne; and the Country attribute hierarchy includes the countries United States, United Kingdom, France, Japan, and Australia; then the space of the cube does not include the space (cell) at the intersection of Paris and United States.

When querying cells that do not exist, non-existing cells return nulls; that is, they cannot contain calculations and you cannot define a calculation that writes to this space. For example, the following statement includes cells that do not exist.

SELECT [Customer].[Gender].[Gender].Members ON COLUMNS,
{[Customer].[Customer].[Aaron A. Allen]
   ,[Customer].[Customer].[Abigail Clark]} ON ROWS 
FROM [Adventure Works]
WHERE Measures.[Internet Sales Amount]

Note

This query uses the Members (Set) (MDX) function to return the set of members of the Gender attribute hierarchy on the column axis, and crosses this set with the specified set of members from the Customer attribute hierarchy on the row axis.

When you execute the previous query, the cell at the intersection of Aaron A. Allen and Female displays a null. Similarly, the cell at the intersection of Abigail Clark and Male displays a null. These cells do not exist and cannot contain a value, but cells that do not exist can appear in the result returned by a query.

When you use the Crossjoin (MDX) function to return the cross-product of attribute hierarchy members from attribute hierarchies in the same dimension, auto-exists limits those tuples being returned to the set of tuples that actually exist, rather than returning a full Cartesian product. For example, run and then examine the results from the execution of the following query.

SELECT CROSSJOIN
   (
      {[Customer].[Country].[United States]},
         [Customer].[State-Province].Members
  ) ON 0 
FROM [Adventure Works]
WHERE Measures.[Internet Sales Amount]

Note

Notice that 0 is used to designate the column axis, which is shorthand for axis(0) - which is the column axis.

The previous query only returns cells for members from each attribute hierarchy in the query that exist with each other. The previous query can also be written using the new * variant of the * (Crossjoin) (MDX) function.

SELECT 
   [Customer].[Country].[United States] * 
      [Customer].[State-Province].Members
ON 0 
FROM [Adventure Works]
WHERE Measures.[Internet Sales Amount]

The previous query could also be written in the following manner:

SELECT [Customer].[State-Province].Members
ON 0 
FROM [Adventure Works]
WHERE (Measures.[Internet Sales Amount],
   [Customer].[Country].[United States])

The cells values returned will be identical, although the metadata in the result set will be different. For example, with the previous query, the Country hierarchy was moved to the slicer axis (in the WHERE clause) and therefore does not appear explicitly in the result set.

Each of these three previous queries demonstrates the effect of the auto-exists behavior in SQL Server 2005 Analysis Services.

User-Defined Hierarchies and Cube Space

The previous examples in this topic define positions in cube space by using attribute hierarchies. However, you can also define a position in cube space by using user-defined hierarchies that have been defined based on attribute hierarchies in a dimension. A user-defined hierarchy is a hierarchy of attribute hierarchies designed to facilitate browsing of cube data by users.

For example, the CROSSJOIN query in the previous section could also have been written as follows:

SELECT CROSSJOIN
   (
      {[Customer].[Country].[United States]},
         [Customer].[Customer Geography].[State-Province].Members
   ) 
ON 0 
FROM [Adventure Works]
WHERE Measures.[Internet Sales Amount]

In the previous query, the Customer Geography user-defined hierarchy within the Customer dimension is used to define the position in cube space that was previously defined by using an attribute hierarchy. The identical position in cube space can be defined by using either attribute hierarchies or user-defined hierarchies.

Attribute Relationships and Cube Space

Defining attribute relationships between related attributes improves query performance (by facilitating the creation of appropriate aggregations) and affects the member of a related attribute hierarchy that appears with an attribute hierarchy member. For example, when you define a tuple that includes a member from the City attribute hierarchy and the tuple does not explicitly define the Country attribute hierarchy member, you might expect that the default Country attribute hierarchy member would be the related member of the Country attribute hierarchy. However, this is only true if an attribute relationship is defined between the City attribute hierarchy and the Country attribute hierarchy.

The following example returns the member of a related attribute hierarchy that is not included explicitly in the query.

WITH MEMBER Measures.x AS 
   Customer.Country.CurrentMember.Name
SELECT Measures.x ON 0,
Customer.City.Members ON 1
FROM [Adventure Works]

Note

Notice that the WITH keyword is used with the CurrentMember (MDX) and Name (MDX) functions to create a calculated member for use in the query. For more information, see The Basic MDX Query (MDX).

In the previous query, the name of the member of the Country attribute hierarchy that is associated with each member of the State attribute hierarchy is returned. The expected Country member appears (because an attribute relationship is defined between the City and Country attributes). However, if no attribute relationship were defined between attribute hierarchies in the same dimension, the (All) member would be returned, as illustrated in the following query.

WITH MEMBER Measures.x AS 
   Customer.Education.Currentmember.Name
SELECT Measures.x  ON 0, 
Customer.City.Members ON 1
FROM [Adventure Works]

In the previous query, the (All) member ("All Customers") is returned, because there is no relationship between Education and City. Therefore, the (All) member of the Education attribute hierarchy would be the default member of the Education attribute hierarchy used in any tuple involving the City attribute hierarchy where an Education member is not explicitly provided.

Calculation Context

Every set, member, tuple, or numeric function executes in the context of the entire MDX expression or statement. When an argument, such as a tuple, is passed to a function, only some coordinates in cube space are explicitly provided. The other coordinates are obtained based on the current calculation context. The calculation context for unspecified cell coordinates and attribute members is determined in the following order:

  1. The FROM clause (if applicable) - this clause can either specify an entire cube or can specify a subcube in the form of a SELECT statement.

  2. The WHERE clause (if applicable) - this clause, which is also known as the slicer axis, on which you specify a set, tuple, or member that restricts the members returned on the column and row axis by a query. Conceptually, the default member of every attribute hierarchy that is not explicitly specified on column or row axis is part of the slicer axis.

    Note

    When cell coordinates for a particular attribute are specified on both the slicer axis and on another axis, the coordinates specified in the function may take precedence in determining the members of the set on the axis. The Filter (MDX) and Order (MDX) functions are examples of such functions - you can filter or order a result by attribute members that are excluded from the calculation context by the WHERE clause, or by a SELECT statement in the FROM clause.

  3. The named sets and calculated members defined in the query or expression.

  4. The tuples and sets specified on the row and column axes, utilizing the default member for attributes that do not appear on the row, column, or slicer axis.

  5. The cube or subcube cells on each axis, eliminating empty tuples on the axis and applying the HAVING clause.

  6. For more information, see Establishing Cube Context in a Query (MDX).

  7. In the following query, the calculation context for the row axis is restricted by the Country attribute member and the Calendar Year attribute member that are specified in the WHERE clause.

SELECT Customer.City.City.Members ON 0
FROM [Adventure Works]
WHERE (Customer.Country.France, [Date].[Calendar].[Calendar Year].[CY 2004],
   Measures.[Internet Sales Amount])
  1. However, if you modify this query by specifying the FILTER function on the row axis, and utilize a Calendar Year attribute hierarchy member in the FILTER function, then the attribute member from the Calendar Year attribute hierarchy that is used to provide the calculation context for the members of the set on the column axis can be modified.
SELECT FILTER
   (
      Customer.City.City.Members, 
         ([Date].[Calendar].[Calendar Year].[CY 2003],
            Measures.[Internet Order Quantity]) > 75 
   ) ON 0
FROM [Adventure Works]
WHERE (Customer.Country.France,
   [Date].[Calendar].[Calendar Year].[CY 2004],
   Measures.[Internet Sales Amount])
  1. In the previous query, the calculation context for the cells in the tuples that appear on the column axis is filtered by the CY 2003 member of the Calendar Year attribute hierarchy, even though the nominal calculation context for the Calendar Year attribute hierarchy is CY 2004. Furthermore, it is filtered by the Internet Order Quantity measure. However, once the members of the set on the column axis is set, the calculation context for the values for the members that appear on the axis is again determined by the WHERE clause.

Important

To increase query performance, you should eliminate members and tuples as early in the resolution process as possible. In this manner, complex query time calculations on the final set of members operate on the fewest cells possible.

See Also

Concepts

MDX Language Reference (MDX)

Other Resources

Multidimensional Expressions (MDX) Reference

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release History

17 July 2006
Changed content:
  • Updated concepts for clarity.
  • Added updated examples.