---

 

CONTENTS

1.NTRODUCTION.....................................................................................................................................................3
2.MULTIDIMENSIONAL EXPRESSIONS....................................................................................................................3
         2.1. CUBE CONCEPTS...................................................................................................................................3
3.GETTING STARTED WITH MDX............................................................................................................................11
4. FILTERING AND SORTING..................................................................................................................................13
5. TOP AND BOTTOM PERFORMANCE ANALYSIS.................................................................................................14
6. NUMERIC FUNCTIONS AND CONDITIONAL EXPRESSIONS.................................................................................15
7. SLICER SPECIFICATIONS..................................................................................................................................15
8. CALCULATED MEMBERS AND NAMED SETS....................................................................................................16
9. HIERARCHICAL NAVIGATION.............................................................................................................................17
10. TIME SERIES FUNCTIONS...............................................................................................................................19
11. TUPLES AND CROSSJOIN...............................................................................................................................21

 

 

 

 

 

1. INTRODUCTION

Microsoft SQL Server OLAP Services provides architecture for access to multidimensional data. This data is summarized, organized, and stored in multidimensional structures for rapid response to user queries. Through OLE DB for OLAP, a PivotTable Service provides client access to this multidimensional online analytical processing (OLAP) data. For expressing queries to this data, OLE DB for OLAP employs full-fledged, highly functional expression syntax: multidimensional expressions (MDX).

OLAP Services supports MDX functions as a full language implementation for creating and querying cube data. The querying capabilities of this language are the focus of this article. To demonstrate these capabilities, we will utilize whenever possible simple real-world sample expressions. These are based on the Sales cube in the sample Food Mart database that is installed with OLAP Services. The MDX expressions can thus be run to view the actual output.

OLE DB for OLAP is a set of Component Object Model (COM) interfaces designed to extend OLE DB for efficient access to multidimensional data. ADO has been extended with new objects, collections, and methods that are designed to take advantage of OLE DB for OLAP. These extensions are collectively known as ADO MD (multidimensional) and are designed to provide a simple, high-level object model for accessing tabular and OLAP data.

This description of MDX assumes the reader is familiar with multidimensional data warehousing and OLAP terms.

2. MULTIDIMENSIONAL EXPRESSIONS

Before talking about MDX and how it queries data, it is worthwhile to give a brief description of the structure of a cube. In addition, we will outline the cube structure of the sample FoodMart Database Sales cube, since all the samples in this article are designed to operate against this sample.

2.1. Cube Concepts

Cubes are key elements in online analytic processing. They are subsets of data from the OLAP store, organized and summarized into multidimensional structures. These data summaries provide the mechanism that allows rapid and uniform response times to complex queries.

Illustrated below is an example cube I have constructed, based loosely upon the more elaborate "Budget" cube supplied as a sample with the installation of Analysis Services. The cube is composed of three dimensions: Stores, Accounts and Time. It stores two measures, Actual and Budget amounts. Each dimension is broken down into different, hierarchical levels, each of which is broken down further into members. For example, the Stores dimension is divided into State levels (seen here as Georgia and New York), which are composed of City levels (here Atlanta, Augusta, New York City and Albany). The Cities are subdivided into the individual Storesthemselves, which are the members of the Stores dimension.

 

The fundamental cube concepts to understand are dimensions and measures.
Dimensions provide the categorical descriptions by which the measures are separated for analysis.
Measures identify the numerical values that are summarized for analysis, such as Price, cost, or quantity sold.
Note: The collection of measures forms a dimension, albeit a special one, called "Measures."
Members
A Member is a specific value in a dimensional hierarchy. In the below Geography hierarchy, Asia, Europe, North America, Canada, Mexico, United States, Boston, Chicago, Los Angeles and New York are all members, of the Geography hierarchy.

Hierarchy Structure

Hierarchy with Members

• Asia
• Europe
• North America
  • Canada
  • Mexico
  • United States
  •  
    • Boston
    • Chicago
    • Las Angeles
    • New York
    • ...
• South America
  

Cells

A Cell is the specific intersection of a measures or measures and the members of a hierarchy. The highlighted block below - "600" is an example of a cell. The other values (1, 000, $5000, $3000) are also each separate cells.

Tuples

A Tuple is a specific member from each dimension in the cube. A Tuple uniquely identifies a cell or a section of the OLAP cube. When specifying a tuple in MDX, each member does not have to be explicitly mentioned. If dimension members are not specified, then the default member of that dimension will be used (Most often the 'ALL' member will be used).

Example 1:

([Geography].[Continent].[Europe])

example 2:

([Date].[Calendar].[2008].[Feb 2008])

example 3:

([Geography].[Continent].[Europe],[Date].[Calendar].[2008].[Feb 2008])

 

Sets

A Set is a collection of Tuples.

Important Points:

• Each cube dimension can contain a hierarchy of levels to specify the categorical breakdown available to users. For example, a Store dimension might include the following level hierarchy: Country, State, City, and Store Name. Each level in a dimension is of a finer grain than its parent.
• Similarly, the hierarchy of a time dimension might include levels for year, quarter, and month.
• Multiple hierarchies can exist for a single dimension. For example, take the common breakdown of time. One may want to view a Time dimension by calendar or fiscal periods. In this case the time dimension could contain the time hierarchies fiscal period and calendar year. The fiscal period hierarchy (defined as Time.FiscalYear) could contain the levels Fiscal Year, Fiscal Quarter, and Month. The calendar hierarchy (defined as Time. Calendar) could contain the levels Calendar Year, Calendar Quarter, and Month.
• A dimension can be created for use in an individual cube or in multiple cubes.
• A dimension created for an individual cube is called a private dimension, whereas a dimension that can be used by multiple cubes is called a shared dimension. Shared dimensions enable the standardization of business metrics among cubes within a database.
• A member is nothing more than an item in a dimension or measure. A calculated member is a dimension member whose value is calculated at run time using a specified expression.
  
• Calculated members can also be defined as measures. Only the definitions for calculated members are stored; values are calculated in memory when needed to answer a query.
• Calculated members enable you to add members and measures to a cube without increasing its size.
• Although calculated members must be based on a cube's existing data, you can create complex expressions by combining this data with arithmetic operators, numbers, and a variety of functions.
  
• Although the term "cube" suggests three dimensions, a cube can have up to 64 dimensions, including the Measures dimension.

 

REQUIREMENTS
To run the sample queries given in the following examples you will need:

• Microsoft SQL Server 2000 Analysis Services (or Microsoft SQL Server 7 OLAP Services) properly installed

• Foodmart 2000 (or Foodmart) sample database

You can find the cube by: Start -> Programs -> Microsoft SQL Server -> Analysis Services, then expanding the Analysis Servers folder by clicking the "+" sign to its left.

 

Explore: Simple Calculated Members
Let's create a simple calculated member to get a look at its properties in a general way. We will also use our newly created calculated member to explore other facets of MDX as we progress through the tutorial.

1. Expand the Cubes folder

2. Right click the Warehouse cube, then click Edit from the flyout menu.

 

3. When the Cube Editor Screen appears, click the Data tab at the lower left (on the right half of the screen).The preview pane is activated.

 

4. If "Product Family" does not appear in the lower pane area, drag the Product dimension to the rows area (the left column of the lower pane section).

5. Select Insert Calculated Member (top drop down menu) under Insert. A picture of the associated toolbar button, an alternative means of initializing the Calculated Member Builder, appears below.

The Calculated Member Builder appears.

6. Type MyCalcMem into the Member Name box. In the Value Expression box, input the phrase "Check for Updates" (quotation marks required). The Value Expression box should appear as shown below.

7. Click OK & Review the newly created MyCalcMem column, comparing it to the illustration below.

8. To change the expression for MyCalcMem, go to the Calculated Members folder in the Cube tree in the left pane of the Cube Editor, as partially shown below.

 

9. Click MyCalcMem within the Calculated Members folder.

10. Right click and select Edit.

11. Type 11+2.

12.Click OK

The results that are returned show the MyCalcMem column to be populated with the Unique Name of the current member, which includes its hierarchy (in brackets, and delimited by single decimals). This represents a sort of "qualified name" whereby no member of a cube is without unique identification. To repeat in similar fashion, we will perform the same process with the Name-Dimension function.

1. Select the Value property (again, bottom left corner of the Cube Editor).
2. Click the ellipses button.
3. Clear the Value Expression box when the Calculated Member Builder appears.
4. Expand the String folder in the Functions tree.
5. Double-click the Name-Member function (<<Member>>.Name should appear).
6. Click the <<Member>> token in the Value Expression box to highlight it.
7. Expand the Member folder in the Functions tree.
8. Double-click the CurrentMember function. Our Value Expression box should display the following expression: <<Dimension>>.CurrentMember.Name
9. Click the <<Dimension>> token to highlight it, and then double-click Product in the Data tree.The expression becomes as shown below:

 

10. Click OK, and observe that the MyCalcMem column now lists the  Member Name, as partially shown below (exploded view):

Illustration 15: The new MyCalcMem Values, using the Member Name Function

11. Now, let's drag the Store dimension from the top pane down to replace the Product dimension in the row axis (you can drop the icon that appears over the "Product Family" headings currently in place - a small, double-headed arrow appears at the "drop" point), "swapping" Product (to the top) with Store (to below, in its old place).

Notice that the context of MyCalcMem has become "for All Products;" if we look above at the (newly positioned) Product dimension, which is now serving as a part of the filter list, we can see it is set to All Products.

12. Select Alcoholic Beverages in the Product filter list, by scrollin down the hierarchy that appears, as shown below.

Illustration 16: The Product Hierarchy as an Exploded Selection

The Current Member of the Product dimension is now Alcoholic Beverages, so the MyCalcMem value becomes Alcoholic Beverages. ("Current Member" is the value that appears on the column or row axis, assuming that the dimension itself appears on the column or row axis, respectively - if not, and the dimension appears in the filter section, the Filter box displays the "Current Member.")

13. Return the Product dimension to its original position in the row axis, simply "swapping" it again with the Store dimension, which, in turn, returns to the top.

14. Double-click the Product Family hierarchy. This exposes the Product Department level to the right of the Product Family level, as partially shown in Illustration 17 below (scroll over to the far right; the row axis remains fixed).

 

3. GETTING STARTED WITH MDX

Getting Started: How to Write MDX Query
Go to Start -> Programs -> Microsoft SQL Server -> Analysis Services, Then browse the cube.

3. Click on New Query on the top left corner as shown below where  we can write the MDX query.

Let's start by outlining one of the simplest forms of an MDX expression:

Syntax:-

SELECT axis specification ON COLUMNS,
Axis specification ON ROWS
FROM cube_name
WHERE slicer_specification

The axis specification can also be thought of as the member selection for the axis. If a single dimension is required, using this notation, COLUMNS must be returned. For more dimensions, the named axes would be PAGES, CHAPTERS and, finally, SECTIONS.

The slicer specification on the WHERE clause is actually optional. If not specified, the returned measure will be the default for the cube. Unless you actually query the Measures dimension (as will the first few expressions), you should always use a slicer specification.

Following are examples of some queries with the use of the functions used in mdx.

Query# 1.1

SELECT Measures.MEMBERS ON COLUMNS,
[Store].MEMBERS ON ROWS
FROM [Sales]

This query displays the recorded measures for each store along with a summary at every defined summary level.

Query# 1.2

SELECT Measures.MEMBERS ON COLUMNS,
{[Store].[Store State].[CA], [Store].[Store State].[WA]} ON ROWS
FROM [Sales]

This expression queries the measures for the stores summarized for the states of California and Washington. To actually query the measures for the members making up both these states, one would query the CHILDREN of the required members:

Query# 1.3

SELECT Measures.MEMBERS ON COLUMNS,
{[Store].[Store State].[CA].CHILDREN,
[Store].[Store State].[WA].CHILDREN} ON ROWS
FROM [Sales]

The MEMBERS function returns the members for the specified dimension or dimension level, and the CHILDREN function returns the child members for a particular member within the dimension.
Both functions are used often in formulating expressions, but do not provide the ability to drill down to a lower level within the hierarchy. For this task, a function called DESCENDANTS is required. This function allows one to go to any level in depth.
Syntax for the DESCENDANTS function is:
DESCENDANTS (member, level [, flags])

Example:

Query# 1.4

SELECT Measures.MEMBERS ON COLUMNS,
{[Store].[Store State].[CA],
DESCENDANTS ([Store].[Store State].[CA], [Store City])} ON ROWS
FROM [Sales]
Calculated members are not enumerated if one requests the dimensions members. Calculated members must be explicitly requested by using the ADDCALCULATEDMEMBERS function:

Query# 1.5

SELECT ADDCALCULATEDMEMBERS (Measures.MEMBERS) ON COLUMNS,
{[Store].[Store State].[CA],
DESCENDANTS ([Store].[Store State].[CA], [Store City])} ON ROWS
FROM [Sales]

4. FILTERING AND SORTING

As mentioned earlier, the concept of slicing and filtering are very distinct. Expectably, filtering actually reduces the number of members on an axis. However, all slicing does is affect the values that go into the axis members, and does not actually reduce their number.

For more specific filtering, MDX offers the FILTER function. This function returns the set that results from filtering according to the specified search condition. The format of the FILTER function is:

FILTER(set, search_condition)

Consider the following simple expression comparing sales profit in 1997 for each city based against the store type:

SELECT {[Store Type].[Store Type].MEMBERS} ON COLUMNS,
{[Store].[Store City].MEMBERS} ON ROWS FROM [Sales]
WHERE (Measures.[Profit], [Time].[Year].[1997])

If one were only interested in viewing top-performing cities, defined by those whose Unit sales exceed 25,000; a filter would be defined as:

SELECT NON EMPTY {[Store Type].[Store Type].MEMBERS} ON COLUMNS,

FILTER({[Store].[Store City].MEMBERS},

(Measures.[Unit Sales], [Time].[1997])>25000) ON ROWS
FROM [Sales]
WHERE (Measures.[Profit], [Time].[Year].[1997])

During cube queries, all the members in a dimension have a natural order. This order can be seen when one utilizes the inclusion operator, a colon. Consider the simple expression displaying all measures for the store cities:

SELECT Measures.MEMBERS ON COLUMNS,
[Store].[Store City].MEMBERS ON ROWS
FROM [Sales]

MDX provides this functionality through the ORDER function. The full syntax for this function is:

ORDER (set, expression [, ASC | DESC | BASC | BDESC])

The expression can be numeric or a string expression. The default sort order is ASC. The "B" prefix indicates the hierarchical order can be broken. Hierarchized ordering first arranges members according to their position in the hierarchy, and then it orders each level. The nonhierarchized ordering arranges members in the set without
regard to the hierarchy.

SELECT Measures.MEMBERS ON COLUMNS,
ORDER({[Store].[Store City].[Beverly Hills]:[Spokane]},
[Store].CURRENTMEMBER.Name, BASC) ON ROWS FROM [Sales]

Here the property Name is used. This returns the name of a level, dimension, Member, or hierarchy. A similar property, UniqueName, exists to return the Corresponding unique name.

5. TOP AND BOTTOM PERFORMANCE ANALYSIS

When displaying information such as the best-selling cities based on unit sales, it may be beneficial to limit the query to, say, the top dozen. MDX can support this operation using a function called HEAD. This function is very simple and returns the first members in the set based on the number that one requests. A similar function called TAIL exists that returns a subset from the end of the set. Taking the previous expression of best-selling stores as an example, the top dozen store cities can be queried by the expression:

SELECT Measures.MEMBERS ON COLUMNS,
HEAD(ORDER({[Store].[Store City].MEMBERS},
Measures.[Sales Count], BDESC), 12) ON ROWS
FROM [Sales]

Expectably, because this is such a common request, MDX supports a function called TOPCOUNT to perform such a task. The syntax for the TOPCOUNT function is:

TOPCOUNT(set, count, numeric_expression)

The previous expression can easily be rewritten:

SELECT Measures.MEMBERS ON COLUMNS,
TOPCOUNT({[Store].[Store City].MEMBERS}, 12,
Measures.[Sales Count]) ON ROWS
FROM [Sales]

Other functions exist for the top filter processing. They are TOPPERCENT, returning the top elements whose cumulative total is at least a specified percentage, and TOPSUM, returning the top elements whose cumulative total is at least a specified value. There is also a series of BOTTOM functions, returning the bottom items in the list.

The preceding expression can easily be modified to display the list of cities whose sales count accounts for 50 percent of all the sales:

SELECT Measures.MEMBERS ON COLUMNS,
TOPPERCENT({[Store].[Store City].MEMBERS}, 50,
Measures.[Sales Count]) ON ROWS
FROM [Sales]

6. NUMERIC FUNCTIONS AND CONDITIONAL EXPRESSIONS
MDX supports many numeric functions. The SUM function is an example that we have seen already in this article. COUNT is another important function, which simply counts the number of tuples in a set.

The COUNT function has two options: including and excluding empty cells. COUNT is useful for such operations as deriving the number of customers that purchased a particular product category. Looking at unit sales, products within the number of customers who purchased products can be derived by counting the number of tuples of the unit sales and customer names. Excluding empty cells is necessary to restrict the count to those customers for which there are unit sales within the product category:

WITH MEMBER Measures.[Customer Count] AS
'COUNT(CROSSJOIN({Measures.[Unit Sales]},
[Customers].[Name].MEMBERS), EXCLUDEEMPTY)'
SELECT {Measures.[Unit Sales], Measures.[Customer Count]} ON COLUMNS,
[Product].[Product Category].MEMBERS ON ROWS
FROM [Sales]

Other numeric functions exist for such operations as calculating the average, median, maximum, minimum, variance, and standard deviation of tuples in a set based on a numeric value. These functions are AVG, MEDIAN, MAX, MIN, VAR, and STDDEV, respectively. The format for all these functions is the same:

FUNCTION(set, numeric_value_expression)

7. SLICER SPECIFICATIONS
You define the slicer specification with the WHERE clause, outlining the slice of the cube to be viewed.

Query# 1.6

SELECT {[Store Type].[Store Type].MEMBERS} ON COLUMNS,
{[Store].[Store State].MEMBERS} ON ROWS

FROM [Sales]
WHERE (Measures.[Sales Average])

If one were only interested in the sales averages for the year 1997, the WHERE clause would be written as:

WHERE (Measures.[Sales Average], [Time].[Year].[1997])

It is important to note that slicing is not the same as filtering. Slicing does not affect selection of the axis members, but rather the values that go into them. This is different from filtering, because filtering reduces the number of axis members.

8. CALCULATED MEMBERS AND NAMED SETS

Calculated members allow one to define formulas and treat the formula as a new member of a specified parent.

Syntax for a calculated member:
WITH MEMBER parent.name AS 'expression' Here, parent refers to the parent of the new calculated member name. Similarly, for named sets the syntax is:

WITH SET set_name AS 'expression'

The simplest use of calculated members is in defining a new measure that relates already defined measures. This is a common practice for such questions as percentage profit for sales, by defining the calculated measure Profit Percent.

WITH MEMBER Measures.ProfitPercent AS
'(Measures.[Store Sales] - Measures.[Store Cost]) /
(Measures.[Store Cost])', FORMAT_STRING = '#.00%'

For defining calculated members there are two properties that you need to know:

FORMAT_STRING and SOLVE_ORDER.

FORMAT_STRING informs the MDX expression of the display format to use for the new calculated member. The format expression takes the form of the Microsoft Visual Basic? format function. The use of the percent symbol (%) specifies that the calculation returns a percentage and should be treated as such, including multiplication by a factor of 100.
SOLVE_ORDER property is used in defining multiple calculated members or named sets. With calculated members one can easily define a new Time member to represent the first and second halves of the year:

WITH MEMBER [Time].[First Half 97] AS
'[Time].[1997].[Q1] + [Time].[1997].[Q2]'
MEMBER [Time].[Second Half 97] AS
'[Time].[1997].[Q3] + [Time].[1997].[Q4]'

Using all this, if one were required to display the individual store's sales percentage profit for each quarter and half year, the MDX expression would read:

Query# 2.1

WITH MEMBER Measures.ProfitPercent AS
'(Measures.[Store Sales] - Measures.[Store Cost]) /
(Measures.[Store Cost])', FORMAT_STRING = '#.00%', SOLVE_ORDER = 1
MEMBER [Time].[First Half 97] AS
'[Time].[1997].[Q1] + [Time].[1997].[Q2]'
MEMBER [Time].[Second Half 97] AS
'[Time].[1997].[Q3] + [Time].[1997].[Q4]'
SELECT {[Time].[First Half 97], [Time].[Second Half 97],
[Time].[1997].CHILDREN} ON COLUMNS,
{[Store].[Store Name].MEMBERS} ON ROWS

FROM [Sales]
WHERE (Measures.ProfitPercent)

In all these expressions, the new calculated member has been directly related to a dimension. This doesn't have to be the case; calculated members can also be related to a member within the hierarchy, as the next sample shows:

Query# 2.2

WITH MEMBER [Time].[1997].[H1] AS
'[Time].[1997].[Q1] + [Time].[1997].[Q2]'
MEMBER [Time].[1997].[H2] AS
'[Time].[1997].[Q3] + [Time].[1997].[Q4]'
SELECT {[Time].[1997].[H1], [Time].[1997].[H2]} ON COLUMNS,
[Store].[Store Name].MEMBERS ON ROWS
FROM [Sales]
WHERE (Measures. Profit)

The definition of named sets follows the exact same syntax as that for calculated members. A named set could be defined that contains the first quarter for each year, within the time dimension. Using this, you can display store profit for the first quarter of each year:

Query# 2.3

WITH SET [Quarter1] AS
'GENERATE([Time].[Year].MEMBERS, {[Time].CURRENTMEMBER.FIRSTCHILD})'
SELECT [Quarter1] ON COLUMNS,
[Store].[Store Name].MEMBERS ON ROWS
FROM [Sales]
WHERE (Measures.[Profit])

The FIRSTCHILD takes the first child of the specified member, in this case the first quarter of each year. A similar function called LASTCHILD also exists, which will take the last child of a specified member.

In the following sections, more will be said about calculated members and named sets, including the use of the CURRENTMEMBER function. In using calculated members and named sets, the ability to perform hierarchical navigation is what really extends their usage. The next section covers this capability.

9. HIERARCHICAL NAVIGATION
In constructing MDX expressions, it is often necessary to relate a current member value to others in the hierarchy. MDX has many methods that can be applied to a member to traverse this hierarchy. Of these, the most commonly used methods are PREVMEMBER, NEXTMEMBER, CURRENTMEMBER, and PARENT. Others also exist, including FIRSTCHILD and LASTCHILD.

Consider the common business need for calculating the sales of a product brand as a percentage of the sales of that product within its product subcategory. To satisfy this requirement, you must calculate the percentage of the sales of the current product, or member, compared to that of its parent. The expression for this calculated member can be derived using the CURRENTMEMBER and PARENT functions.

Query# 2.4

WITH MEMBER MEASURES.PercentageSales AS
'([Product].CURRENTMEMBER, Measures.[Unit Sales]) /
([Product].CURRENTMEMBER.PARENT, Measures.[Unit Sales])',
FORMAT_STRING = '#.00%'

SELECT {MEASURES.[Unit Sales], MEASURES.PercentageSales} ON COLUMNS,
[Product].[Brand Name].MEMBERS ON ROWS
FROM [Sales]

• The CURRENTMEMBER function returns the current member along a dimension during iteration.
• The PARENT function returns the parent of a member.

For calculating the appropriate ancestor of the CURRENTMEMBER, the appropriate function is ANCESTOR, which returns the ancestor of a member at the specified level:
WITH MEMBER MEASURES.PercentageSales AS
'([Product].CURRENTMEMBER, Measures.[Unit Sales]) /
(ANCESTOR([Product].CURRENTMEMBER, [Product Category]),
MEASURES.[Unit Sales])'
The use of named sets and the function EXCEPT will easily allow an expression to be formulated that shows the percentage of sales for each promotion compared only to other promotions:

Query# 2.5
WITH SET [PromotionSales] AS
'EXCEPT({[Promotions].[All Promotions].CHILDREN},
{[Promotions].[No Promotion]})'
MEMBER Measures.PercentageSales AS
'([Promotions].CURRENTMEMBER, Measures.[Unit Sales]) /
SUM([PromotionSales], MEASURES.[Unit Sales])',
FORMAT_STRING = '#.00%'
SELECT {Measures.[Unit Sales], Measures.PercentageSales} ON COLUMNS,
[PromotionSales] ON ROWS
FROM [Sales]

• The EXCEPT function finds the difference between two sets, optionally retaining duplicates. The syntax for this function is:
  EXCEPT(set1, set2 [, ALL])

Duplicates are eliminated from both sets prior to finding the difference. The optional ALL flag retains duplicates.
The concept of taking the current member within a set is also useful when using the GENERATE function. The GENERATE function iterates through all the members of a set, using a second set as a template for the resultant set.

Query# 2.6
SELECT {GENERATE([Time].[Year].MEMBERS,
{[Time].CURRENTMEMBER, [Time].CURRENTMEMBER.CHILDREN})} ON COLUMNS,
[Promotions].[All Promotions].CHILDREN ON ROWS
FROM [Sales]
WHERE (Measures.[Unit Sales])

Similarly, if you wanted to display the unit sales for all promotions and stores within the states of California and Washington, you would need to enumerate all the stores for each state. Not only would this be a lengthy expression to derive, but modifying the states in the expression would require extensive rework. The GENERATE function can be used to allow new states to be easily added to or removed from the expression:

Query# 2.7
SELECT {GENERATE({[Store].[CA], [Store].[WA]},
DESCENDANTS([Store].CURRENTMEMBER, [Store Name]))} ON COLUMNS,
[Promotions].[All Promotions].CHILDREN ON ROWS
FROM [Sales]

WHERE (Measures.[Unit Sales])

Another common business problem involves the need to show growth over a time period, and here the PREVMEMBER function can be used. If one needed to display sales profit and the incremental change from the previous time member for all months in 1997, the MDX expression would read:

Query# 2.8
WITH MEMBER Measures.[Profit Growth] AS
'(Measures.[Profit]) - (Measures.[Profit], [Time].PREVMEMBER)',
FORMAT_STRING = '###,###.00'
SELECT {Measures.[Profit], Measures.[Profit Growth]} ON COLUMNS,
{DESCENDANTS([Time].[1997], [Month])} ON ROWS
FROM [Sales]

Note: - Using NEXTMEMBER in this expression would show sales for each month compared with those of the following month.
You can also use the LEAD function, which returns the member located a specified number of positions following a specific member along the member's dimension. The syntax for the LEAD function is as follows: member.LEAD(number) If the number given is negative a prior member is returned; if it is zero the current member is returned. This capability allows for replacing the PREV, NEXT, and CURRENT navigation with the more generic LEAD(-1), LEAD(1), and LEAD(0). A similar function called LAG exists, such that LAG(n) is equivalent to LEAD(-n).

10. TIME SERIES FUNCTIONS
MDX provides a powerful set of time series functions for time period analysis. While they are called time series functions and their most common use is with the Time dimension, most of them work equally well with any other dimension, and scenarios exist where these functions can be useful on other dimensions.

The xTD (YTD, MTD, QTD, WTD) functions are exceptions to this flexibility; they are only applicable to the Time dimension. These functions refer to Year-, Quarter-, Month-, and Week-to-date periods and will be discussed later in this section. Including the xTD functions, the important time series functions that we demonstrate here are PARALLELPERIOD, CLOSINGPERIOD, OPENINGPERIOD, and PERIODSTODATE.

PARALLELPERIOD allows to compare member values of a specified member with those of a member in the same relative position in a prior period. (The prior period is the prior member value at a higher specified level in the hierarchy.)
For example, one would compare values from one month with those of the same relative month in the previous year. The expression using the PREVMEMBER function compared growth with that of the previous month; PARALLELPERIOD allows for an easy comparison of growth with that of the same period in the previous quarter:

Query# 2.9
WITH MEMBER Measures.[Profit Growth] AS '(Measures.[Profit]) –
(Measures.[Profit], PARALLELPERIOD([Time].[Quarter]))',
FORMAT_STRING = '###,###.00'
SELECT {Measures.[Profit], Measures.[Profit Growth]} ON COLUMNS,
{DESCENDANTS([Time].[1997], [Month])} ON ROWS
FROM [Sales]
If you were to run this expression, for the first quarter the profit growth would be equivalent to the profit. Because the sales cube only holds sales for 1997 and 1998, the quarter growth for the first quarter cannot really be measured. In these situations, an appropriate value of zero is used for parallel periods beyond the cube's range.

The exact syntax for PARALLELPERIOD is:
PARALLELPERIOD(level, numeric_expression, member) All parameters are optional. The numeric expression allows one to specify how many periods one wishes to go back. One could just as easily have written the previous expression to traverse back to the same month in the previous half year:
PARALLELPERIOD([Time].[Quarter], 2)

The functions OPENINGPERIOD and CLOSINGPERIOD have similar syntax:
OPENINGPERIOD(level, member), CLOSINGINGPERIOD(level, member) Their purpose is to return the first or last sibling among the descendants of a member at a specified level. All function parameters are optional. If no member is specified, the default is [Time].CURRENTMEMBER. If no level is specified, it is the level below that of member that will be assumed.

Query# 2.10
WITH MEMBER Measures.[Sales Difference] AS
'(Measures.[Unit Sales]) – (Measures.[Unit Sales],
OPENINGPERIOD([Time].[Month], [Time].CURRENTMEMBER.PARENT))',
FORMAT_STRING = '###,###.00'
SELECT {Measures.[Unit Sales], Measures.[Sales Difference]} ON COLUMNS,
{DESCENDANTS([Time].[1997], [Month])} ON ROWS
FROM [Sales]
In deriving the calculated member "Sales Difference," the opening period at the month level is taken for the quarter in which the month resides. Replacing OPENINGPERIOD with CLOSINGPERIOD will show sales based on the final month of the specified season.

The final set of time series functions we'll discuss are the xTD functions. Before describing these functions, however, we need to consider the PERIODSTODATE function, as the xTD functions are merely special cases of PERIODSTODATE. PERIODSTODATE returns a set of periods (members) from a specified level starting with the first period and ending with a specified member. This function becomes very useful when combined with such functions as SUM, as will be shown shortly.
The syntax for the PERIODSTODATE function is:
PERIODSTODATE(level, member)
If member is not specified, the member [Time].CURRENTMEMBER is assumed. As a simple example, to define a set of all the months up to and including the month of June for the year 1997, the following definition could be used:

PERIODSTODATE([Time].[Year], [Time].[1997].[Q2].[6])

Before we continue with this discussion, the SUM function warrants a brief description. This function returns the sum of a numeric expression evaluated over a set. For example, one can easily display the sum of unit sales for the states of California and Washington with the following simple expression:

SUM({[Store].[Store State].[CA], [Store].[Store State].[WA]},
Measures.[Unit Sales])

More will be said about SUM and other numeric functions shortly. Using the functions SUM and PERIODSTODATE, it becomes easy to define a calculated member that displays year-to-date information. Take for example the requirement to query monthly year-to-date sales for each product category in 1997. The measure to be displayed is the sum of the current time member over the year level:
PERIODSTODATE([Time].[Year], [Time].CURRENTMEMBER)
This is easily abbreviated by the expression YTD():

Query# 2.11

WITH MEMBER Measures.YTDSales AS

'SUM(YTD(), Measures.[Store Sales])', FORMAT_STRING = '#.00'
SELECT {DESCENDANTS([Time].[1997], [Month])} ON COLUMNS,
{[Product].[Product Category].MEMBERS} ON ROWS
FROM [Sales]
WHERE (Measures.YTDSales)

Calculating quarter-to-date information is easily achieved by using the QTD() instead of YTD() function. It can also be achieved by using the PERIODSTODATE function with the level defined as [Time].[Quarter]. Similar rules apply for using the MTD() and WTD() functions. Using PERIODSTODATE may be somewhat longwinded compared to using the xTD functions, but does offer greater flexibility. In addition, it can also be used with non-time dimensions.

11. TUPLES AND CROSSJOINIn many cases a combination of members from different dimensions are enclosed in brackets. This combination is known as a tuple and is used to display multiple dimensions onto a single axis. In the case of a single member tuple, the brackets can be omitted.

The main advantage of tuples becomes apparent when more than two axes are required. Say that one needs to query unit sales for the product categories to each city for each quarter. This query is easily expressed by the following MDX expression, but unless one is mapping the data into a 3D graph, is impossible to display.

Query# 2.11

/* non-displayable on MDX Sample App */
SELECT [Product].[Product Family].MEMBERS ON COLUMNS,
[Customers].[City].MEMBERS ON ROWS,
[Time].[Quarter].MEMBERS ON PAGES
FROM [Sales]
WHERE (Measures.[Unit Sales])

To allow this query to be viewed in a matrix format one would need to combine the customer and time dimensions onto a single axis. This is a tuple—a combination of dimension members coming from different dimensions. The syntax for a tuple is as follows:

(member_of_dim_1, member_of_dim_2, ..., member_of_dim_n)
The only problem here is to enumerate all the possible combinations of customer cities and yearly quarters. Fortunately, MDX supports this operation through the use of the CROSSJOIN function. This function produces all combinations of two sets.
The previous expression can now be rewritten to display the results on two axes as follows:

Query# 2.12

SELECT [Product].[Product Family].MEMBERS ON COLUMNS,
{CROSSJOIN([Customers].[City].MEMBERS, [Time].[Quarter].MEMBERS)}
ON ROWS
FROM [Sales]
WHERE (Measures.[Unit Sales])