.png)
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.
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.
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]. |
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. |
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 Analysis Services.
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.
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.
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:
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.
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.
NoteWhen 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.
The named sets and calculated members defined in the query or expression.
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.
The cube or subcube cells on each axis, eliminating empty tuples on the axis and applying the HAVING clause.
For more information, see Establishing Cube Context in a Query (MDX).
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])
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])
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 improve 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. |
| Important |
|---|
The pass and solve order on which expressions are evaluated are relevant to the final value of an expression. see Understanding Pass Order and Solve Order (MDX) for more information on how these values might affect your calculations. |
