Lesson 2: Writing an MDX Query

As with traditional Transact-SQL, when you create an MDX query, you should try to visualize the structure of the returned dataset before you write the query and then build a statement to return the desired data. The previous lesson helped you visualize datasets. In this lesson, you will learn how to use the parts of an MDX query to specify the dataset.

After this lesson, you will be able to:

• Describe the parts of an MDX statement
• Write an MDX statement to query a cube

Estimated lesson time: 120 minutes

Building an MDX Statement

A multidimensional dataset is the result of an MDX SELECT. Similar to an n-dimensional array, in a multidimensional dataset, each cell contains the value found at the intersection of the positions of the axes of the dataset.

Parts of an MDX Statement

To specify a dataset, the MDX statement contains information about the following subjects:

• The name of the cube that sets the scope of the query
• The number of axes
• The dimensions projected on each axis and nesting level of each of these dimensions
• The members that you want to include from each projected dimension (axis dimension)
• A member from each non-projected dimension (slicer dimension); these members filter the data that is returned for the projected dimensions

TIP

---

You may be wondering why measures are not mentioned in this list. The measures are part of a dimension named Measures. You use the Measures dimension in the same way as other dimensions, either by projecting members from the Measures dimension on one of the axes or by specifying a member of the Measures dimension with the non-projected dimension members.

The MDX SELECT Statement

In the MDX query syntax, there is one axis specification for each axis, and each axis specification lists the dimensions that will be projected on the axis. The cube specification identifies the source cube. The slicer specification works as a filter to choose single members of the dimensions that are not projected on the axes.

Syntax

 SELECT <axis_specification> [, <axis_specification>...]  FROM <cube_specification>  WHERE <slicer_specification> 

NOTE

---

The OLAP Services documentation includes a brief discussion about writing MDX queries. MDX is documented in more detail in the OLE DB for OLAP Programmer s Reference, which is part of the OLE DB documentation. There is a copy of this documentation on this book s companion CD-ROM in the \References folder. Open the file named oledb.chm to view this document.

Exercise 1: Querying Cube Data with the MDX Sample Application

In this exercise, you will connect to your OLAP Server by using the MDX Sample Application. You will execute a basic query that contains the Members function.

To connect to the OLAP Server

1. On the Start menu, point to Programs, point to Microsoft SQL Server 7.0, and then from the OLAP Services Group, click MDX Sample Application.
2. Use the following information to connect to your OLAP Server.

Parameter	Value
Server	(Your local server name)
Provider	MSOLAP

To connect to the Northwind_DSS sales cube

In this procedure, you will connect to the Sales cube in the Northwind_DSS database.

1. In the DB drop-down list, select Northwind_DSS.
2. In the Cube drop-down list, select Sales.
3. View the metadata for the Sales cube that appears in the Cube treeview pane and members pane. Expand the node for the Time and Customer dimensions. Expand the Measures node and examine the measures for the cube. Notice that you can expand the dimensions and levels in the cube. If you click on a level name in the treeview, the members of that level are displayed in the members pane.

TIP

---

The MDX Sample Application lets you save queries to a file. All the queries that are used in the exercises in this chapter are saved for you in the C:\SQLDW\Exercise\Ch12\Exercises.MDX file. You must select the Northwind_DSS database when you use the Exercises.MDX queries.

To execute an MDX query

In this procedure, you will execute a query to show all measures on the COLUMNS axis and all members of the [Shipper Name] dimension on the ROWS axis.

1. On the Query menu, click New.
2. In the query pane, type the following MDX query.

 SELECT  Measures.Members ON COLUMNS,  [Shipper].Members ON ROWS  FROM Sales 

3. Execute the query and review the results. You can press F5 or click the green arrow on the toolbar to execute the query.
4. On the View menu, click Pivot Results.

This step reverses the columns and rows in the returned data.

Specifying the Axes

Although MDX queries can use up to 64 axes, it is rare that more than 4 are used, and 2 or 3 axes are most common. When you specify a flattened data set that is displayed in a two-dimensional grid or report, you use only two axes.

Each axis is associated with a number: 0 for the first axis, 1 for the second axis, and so on. The axes are referred to as AXIS(0), AXIS(1), , AXIS(n). To simplify reading MDX queries, the first five axes have names that you can use instead of specifying the axis numbers. The following table describes the keywords that you can use to identify each axis in an MDX statement axis.

Keywords	Axis	Description
COLUMNS	AXIS(0)	This axis represents the x-axis in a two-dimensional grid that represents the cube.
ROWS	AXIS(1)	This axis represents the y-axis in a two-dimensional grid that represents the cube.
PAGES	AXIS(2)	This axis represents the z-axis in a three-dimensional representation of the cube.
SECTIONS	AXIS(3)
CHAPTER	AXIS(4)
	AXIS (index)	Each axis in the cellset has a generic name that you can use to explicitly specify any axis.

When specifying the axes in an MDX query, consider the following facts and guidelines:

• You cannot skip axes.

You must include all axes up to the last axis in the SELECT clause. For example, it is an error to have ROWS and PAGES but not COLUMNS, or to have COLUMNS and PAGES but not ROWS.

• You can specify axes out of order.

For example, you can specify ROWS, COLUMNS, and PAGES instead of COLUMNS, ROWS, and PAGES.

• You can specify a SELECT clause with no axes.

It is not an error to have an empty SELECT clause. In this case, all dimensions listed in the slicer specification are used to filter the results, and the MDX statement returns a single cell.

Defining the Contents of an Axis

You specify one axis specification for each axis after the SELECT keyword and immediately before the FROM clause in the MDX syntax.

An axis specification in an MDX query defines which dimension members appear in the results. These members are specified as a set. You can define the members of the set in a number of ways, either explicitly or by using functions.

When specifying axis dimensions in an MDX query, consider the following facts and guidelines:

• An axis specification is composed of a set of dimension members and the name of the axis onto which they are projected.
• Enclose sets in curly braces { }, even if there is only one member in the set.
• If member names are composed of multiple words, are numbers, or are reserved words, you must delimit the names of dimension members by using square brackets [ ] in the set.
• You can swap the contents of axes simply by rewriting the SELECT statement and swapping the axis specifications.

Syntax

<axis_specification> ::= <set> ON <axis_name>

Example

This example shows an MDX SELECT statement. Two members of the Promotions dimension are specified on the COLUMNS axis, and two measures are specified on the ROWS axis. The statement retrieves data from the Sales cube.

 SELECT  {[Promotions].[Best Savings], [Promotions].[Super Savers]} ON COLUMNS,  {[Measures].[Store Cost], [Measures].[Store Sales]} ON ROWS  FROM Sales 

The result of this query is displayed in the following table:

	Best Savings	Super Savers
Store Cost	1,689.79	2,127.24
Store Sales	4,241.53	5,337.16

TIP

---

The example queries in this chapter all use the Sales cube in the OLAP Services sample FoodMart database. If you want to execute the example queries in this chapter as well as perform the practical exercises, ensure that you have installed the FoodMart sample database from the SQL Server 7.0 CD-ROM and then select FoodMart in the DB drop-down list before you execute the queries in the MDX Sample Application.

Defining Sets

You use a set to define the contents of an axis in an MDX statement. Because sets can contain members from more than one dimension, MDX supports several ways to specify members of a set explicitly or by using expressions.

Listing Members of a Set

When you define sets, you can explicitly list each dimension member that you want to include in the set. In addition, you can use range notation to include many members.

Explicitly Naming Members

The simplest form for the axis specification set is to explicitly specify the list of members. When explicitly naming a member of a set, consider the following facts:

• All sets in MDX are enclosed in curly braces { }.
• If the member name is unique among all dimensions in the cube, you can list only the member name. Otherwise, you must fully qualify the member name. For example, if New York, Boston, and San Francisco are unique names among all dimensions in the cube, then

 {[Geography].[USA].[New York], [Geography].[USA].[Boston],  [Geography].[USA].[San Francisco]} is equivalent to {[New York], [Boston], [San Francisco]} 

Using Ranges with Ordered Sets

If the dimension members have an inherent order in their naming, you can select a range of members by naming the first member followed by a colon (:) followed by the second member. The expression is an inclusive range. That is, the expression includes the members on both sides of the colon (:). For example, {Q1:Q4} is equivalent to {Q1, Q2, Q3, Q4}.

Generating Set Members with Functions

You can also use functions that return sets. These sets represent a particular group of members. Set functions generate sets so that you do not have to list set members explicitly. The most commonly used set functions are Members, Children, and Descendants.

Members Function

Use the Members function to generate a set containing all members of a dimension, hierarchy, or level. When you use the Members function with a dimension name, the set that is returned includes the members from all levels, including the [All] member if an All level exists for the dimension. The members are returned in hierarchical order. For example, for the Year level that has the members 1997 and 1998, [Year].Members returns the set {[1997], [1998]}. In another example, for the Gender dimension that has an All level named All Gender, [Gender].Members returns the set {[All Gender], [F], [M]}.

NOTE

---

The Members function is not valid for a member. For example, if 1997 is a member of the Year level, [1997].Members is invalid and generates an error.

Children Function

Use the Children function to generate a set containing the child members of a member or a dimension. This set contains only the members that are one level below the parent member. For example, [1997].Children returns the set {[1997].[Q1], [1997].[Q2], [1997].[Q3], [1997].[Q4]}, and [Gender].Children returns the set {[F], [M]}.

NOTE

---

The Children function is not valid for a level. For example, if Year is a level, [Year].Children is invalid and generates an error.

Descendants Function

The Descendants function returns the set of descendants of a member or a dimension. The set is returned either for a specified lower level only or for the specified level and for other levels as specified by an optional flag. By default, only members at the specified level will be included. For example, Descendants([Customers].[Country].[USA], [City]) returns all of the cities in the United States. For another example, Descendants([1997], [Quarter]) returns the set {[1997].[Q1], [1997].[Q2], [1997].[Q3], [1997].[Q4]}, whereas Descendants([1997], [Quarter], SELF_AND_BEFORE) returns the set {[1997], [1997].[Q1], [1997].[Q2], [1997].[Q3], [1997].[Q4]}.

NOTE

---

The Descendants function is not valid for a level. For example, if Year is a level, Descendants([Year], [Day]) is invalid and generates an error.

Generating Sets of Tuples

A tuple is a combination of members from different dimensions or a single member from one dimension. In the preceding discussion, the axes specifications have all listed members from only one dimension. If an axis projects more than one dimension, the tuples in the axis specification set contain more than one member. A tuple contains one member from each dimension that is projected on the axis. A tuple represents a single coordinate or position on the axis. You can explicitly list the members in each tuple, or you can generate tuples by using functions. [Boston].[1995] is an example of a tuple that is formed by a member of the Geography dimension and a member of the Time dimension. The ordering of the members in a tuple is important because it indicates the nesting of the dimensions on the axis and determines how you will be able to drill down into the members of the axis.

Crossjoin Function

You can use the Crossjoin function to create tuples that represent all combinations of listed members from two different sets.

These examples use the Crossjoin function to combine members from two sets and generate tuples that contain all combinations of the set members.

 Crossjoin({[1997], [1998]}, {[USA], [Canada]}) returns the set {([1997], [USA]) , ([1997], [Canada]) , ([1998], [USA]), ([1998],  [Canada])} Crossjoin([Gender].Children, [Marital Status].Children) returns the set {([Gender].[Female], [Marital Status].[Single]), ([Gender].[Female],  [Marital Status].[Married]), ([Gender].[Male], [Marital  Status].[Single]), ([Gender].[Male], [Marital Status].[Married])} Crossjoin([USA].Children, [Q1]:[Q3]) returns the set {(CA, Q1), (CA, Q2), (CA, Q3), (CA, Q4), (OR, Q1), (OR, Q2), (OR, Q3),  (OR, Q4), (WA, Q1), (WA, Q2), (WA, Q3), (WA, Q4)} 

NOTE

---

Nest is supported as an optional synonym for Crossjoin, so Nest({[1997], [1998]}, {[USA], [Canada]}) and Crossjoin({[1997], [1998]}, {[USA], [Canada]}) return the same set.

Additional Set Functions

The following table describes some additional functions that you can use to generate tuples. As with the Crossjoin function, you can name the input sets explicitly for these functions, or you can specify them by using set functions such as Members and Children.

Function	Description
Intersect	Returns the members common to two sets, optionally retaining duplicates.
Union	Returns all members of two sets, optionally retaining duplicates.
Except	Returns the members not in common between two sets.

Empty Tuples

When all of the cells for a tuple are empty, you may want to exclude that tuple from the dataset. You can use the NON EMPTY keyword for an axis to filter out empty tuples from the axis.

Example

This query returns a dataset with two empty rows.

 SELECT Descendants([1997], [Quarter]) ON COLUMNS,  [Country].Members ON ROWS  FROM Sales 

The results of this query are displayed in the following table:

	Q1	Q2	Q3	Q4
Canada
Mexico
USA	66,291.00	62,610.00	65,848.00	72,024.00

The NON EMPTY keyword removes the empty rows.

 SELECT Descendants([1997], [Quarter]) ON COLUMNS,  NON EMPTY [Country].Members ON ROWS  FROM Sales 

The results of this query are displayed in the following table:

	Q1	Q2	Q3	Q4
USA	66,291.00	62,610.00	65,848.00	72,024.00

Specifying the WHERE Clause

You specify slicer dimensions in the WHERE clause of an MDX query.

Specifying Slicer Dimensions

You use slices to narrow the focus of your analysis. Slicer dimensions are the dimensions from the cube that are not projected on the axes. Data is returned by the query for only one member of a slicer dimension. If no member is specified for a dimension, the first member of the dimension is used by default. Slices are denoted as a set consisting of one member from each of the slicer dimensions of the cube, excluding defaults.

If the slicer specification consists of only one member, you can list just the member s name in the WHERE clause. When you slice across multiple dimensions, however, you must enclose the member names in parentheses.

The default member is particularly important for the Measures dimension because this member determines what measure is returned in the cell values of the cube when you do not specify the Measures dimension on an axis. For this reason, whenever you want to return a single measure other than the default measure, you must specify the measure in the slicer specification.

Examples

This query returns a cube that shows the unit sales (default measure) of each top-level product category in each store. The results include sales for female shoppers only, because [Gender].[F] is specified in the slicer specification.

 SELECT [Product].Children ON COLUMNS,  NON EMPTY [Store Name].Members ON ROWS  FROM Sales  WHERE [Gender].[F] 

The results of this query are displayed in the following table:

	Drink	Food	Non-Consumable
Store 6	959	7919	1893
Store 7	1109	8650	2330
Store 24	1367	9175	2293
Store 14	81	793	190
Store 11	1141	8821	2526
Store 13	1825	14776	3947
Store 2	104	781	211
Store 3	1060	8414	2166
Store 15	1266	9786	2461
Store 16	1148	8771	2149
Store 17	1546	12631	3243
Store 22	100	734	185
Store 23	496	3563	948

This query returns the same cube as the previous example but adds a second limitation by adding a second slicer dimension member to the slicer specification. The slicer specification now includes female shoppers whose yearly income is in the $70,000 to $90,000 range.

 SELECT [Product].Children ON COLUMNS,  NON EMPTY [Store Name].Members ON ROWS  FROM Sales  WHERE ([Gender].[F], [$70K - $90K]) 

The results of this query are displayed in the following table:

	Drink	Food	Non-Consumable
Store 6	119	1095	303
Store 7	164	1148	313
Store 24	170	1246	370
Store 14	19	130	34
Store 11	133	1120	332
Store 13	249	2158	512
Store 2	16	82	30
Store 3	150	1630	397
Store 15	163	1513	320
Store 16	136	1075	244
Store 17	236	1956	505
Store 22	20	142	31
Store 23	78	704	167

Specifying Measures

An MDX query returns values for the default measure for a dimension. If you want to retrieve a different measure in your results, you can project one or more measures on an axis, or you can specify one measure in the WHERE clause of the MDX query.

Examples

This example replaces the default measure for the Sales cube (which is [Unit Sales]) with the [Store Sales] measure. It displays the [Store Sales] for each education level, by marital status.

 SELECT  {[Education Level].Members} ON COLUMNS,  {[Marital Status].Children} ON ROWS  FROM Sales  WHERE (Measures.[Store Sales]) 

The results of this query are displayed in the following table:

	All Education	Bachelors Degree	Graduate Degree	High School Degree	Partial College	Partial High School
M	285852.81	73416.97	37356.93	64981.80	75581.01	34516.10
S	279385.32	71215.33	37336.69	64637.17	70092.60	36103.53

You also can combine slicer dimensions and additional measure specifications in the WHERE clause of an MDX query.

This example returns the [Store Sales] information for each education level, by marital status, and restricts the results to males reached through a radio promotion.

 SELECT {[Education Level].Members} ON COLUMNS,  {[Marital Status].Children} ON ROWS  FROM Sales  WHERE (Gender.[M], [Promotion Media].[Radio], Measures.[Store Sales]) 

The results of this query are displayed in the following table:

	All Education	Bachelors Degree	Graduate Degree	High School Degree	Partial College	Partial High School
M	1123.61	185.14	76.68	398.29	351.30	112.20
S	1019.80	141.24	118.45	402.09	169.01	189.01

Exercise 2: Specifying Axis and Slicer Dimensions

In this exercise, you will write and execute MDX queries that specify the contents of each axis by listing dimension members and measures and by using functions. You will limit the results by specifying slicer dimensions in the WHERE clause. This exercise uses the Sales cube in the Northwind_DSS multidimensional database.

To use the Children function

In this procedure, you will write a query to show the first-level members of the Product dimension on the COLUMNS axis and all the measures on the ROWS axis.

1. In the MDX Sample Application, select Northwind_DSS in the DB drop-down list.
2. Write a query that uses the Children function to list the children of the product dimension on the COLUMNS axis and all measures on the ROWS axis.

 SELECT  Product.Children ON COLUMNS,  Measures.Members ON ROWS  FROM Sales 

3. Execute the query and review the results.

You see a column for each product category and a row for each measure. Note that the Product dimension enumerates all product categories.

4. Modify the query to show the children of the Beverages member of the product dimension.

 SELECT  Product.[Beverages].Children ON COLUMNS,  Measures.Members ON ROWS  FROM Sales 

5. Execute the modified query and review the results.

Notice that by adding the [Beverages] category to the previous query, you enumerate only the products that are classified as Beverages.

To enumerate axes

In this procedure, you will write an MDX query that explicitly lists members in the axis specification set.

1. Write a query that returns the default measure for [Beverages], [Produce], and [All Product] from the Products dimension for 1997. Place the Time dimension on the COLUMNS axis and the Products dimension on the ROWS axis.

 SELECT  {[Time].[1997].Children} ON COLUMNS,  {Product.[All Product].[Beverages],  Product.[All Product].[Produce],  Product.[All Product]} ON ROWS  FROM Sales 

Because the low-level member names are not ambiguous, an equivalent query is

 SELECT  {[Time].[1997].Children} ON COLUMNS,  {[Beverages], [Produce],  [All Product]} ON ROWS  FROM Sales 

2. Execute the query and review the results.

To specify a slicer dimension by using a WHERE clause

In this procedure, you will specify a slicer dimension by using the WHERE clause.

1. Write a query that returns the default measure for the children of [Product].[Beverages] on the COLUMNS axis and [Shipper Name] members on the ROWS axis. The results should only include results for Customers in [USA].

 SELECT  [Product].[Beverages].Children ON COLUMNS,  [Shipper Name].Members ON ROWS  FROM Sales  WHERE [Customer].[Country].[USA] 

2. Execute the query and review the results.

To specify a measure by using a WHERE clause

In this procedure, you will specify a measure by using a WHERE clause.

1. Modify the WHERE clause in query from the previous procedure so that the query returns the measure [Line Item Quantity].

 SELECT  [Product].[Beverages].Children ON COLUMNS,  [Shipper Name].Members ON ROWS  FROM Sales  WHERE ([Customer].[Country].[USA], [Measures].[Line Item Quantity]) 

2. Execute the query and review the results.

To combine dimensions and measures in the WHERE clause

In this procedure, you will combine dimensions and measures in the WHERE clause.

1. Write a query to return the [Discounted Line Total] measure for January, February, March, and April 1997 on the COLUMNS axis and [Beverages], [Produce], and [All Product] from the Products dimension on the ROWS axis. Filter the results to list only Sales to Canadian customers that are shipped via [Federal Shipping].

 SELECT  {[Time].[1997].[Quarter 1].[January]:[Time].[1997].[Quarter  2].[April] } ON COLUMNS,  {[Beverages], [Produce],[All Product]} ON ROWS  FROM Sales  WHERE ([Customer].[Canada],      [Shipper].[Federal Shipping],      [Measures].[Discounted Line Total]) 

2. Execute the query and review the results.

Exercise 3: Specifying Sets with Tuples and the Crossjoin Function

In this exercise, you will write and execute MDX queries that specify tuples on axes and generate tuples by using the CROSSJOIN function.

To specify tuples

In this procedure, you will compare the [Line Item Total] measure for Condiments in Austria with Dairy Products in Brazil and view the results by quarters for the year 1997.

1. Write a query with these tuples that returns [Line Item Total] with the quarters of the year 1997 on columns, Condiments sold in Austria on one row, and Dairy Products sold in Brazil on another row. The tuples for the rows are ([Product].[Condiments], [Customer].[Austria]) and ([Product].[Dairy Products], [Customer].[Brazil]).

 SELECT  {[Time].[1997].CHILDREN} ON COLUMNS,  {([Product].[Condiments],  [Customer].[Austria]),  ([Product].[Dairy Products],   [Customer].[Brazil])} ON ROWS  FROM Sales  WHERE (Measures.[Line Item Total]) 

2. Execute the query.

Notice that the query displays the [Line Item Total] for each quarter of the year 1997.

To display combinations of members on an axis

In this procedure, you will display combinations of members on an axis.

1. Write a query to report on the measure [Line Item Total] by placing the Product category on the COLUMNS axis and all combinations of the Year and Shipper Name on the ROWS axis. List each combination of Year and Shipper Name explicitly by using tuples.

 SELECT {[Customer].Children} ON COLUMNS,  {([Time].[1996], [Shipper].[Federal Shipping]),  ([Time].[1996], [Shipper].[Speedy Express]),  ([Time].[1996], [Shipper].[United Package]),  ([Time].[1997], [Shipper].[Federal Shipping]),  ([Time].[1997], [Shipper].[Speedy Express]),  ([Time].[1997], [Shipper].[United Package]),  ([Time].[1998], [Shipper].[Federal Shipping]),  ([Time].[1998], [Shipper].[Speedy Express]),  ([Time].[1998], [Shipper].[United Package])} ON ROWS  FROM Sales  WHERE ([Line Item Total]) 

2. Execute the query and review the results.
3. Rewrite the previous query by using the Crossjoin function.

 SELECT  {[Customer].Children} ON COLUMNS,  NON EMPTY {Crossjoin({[Time].Children}, {[Shipper].Children})} ON  ROWS  FROM [Sales]  WHERE ([Measures].[Line Item Total])

4. Execute the query and notice that the results are the same.

Defining a Calculated Member

When you define a dataset with an MDX query, you can create custom measures or dimension-level members, called calculated members. You can use expressions containing operators and functions to create calculated members. OLAP Services includes a library of over 100 functions that you can use in your expressions.

Calculated member values are derived from other member values in the cube. A calculated member is not part of the source cube. Calculated member definitions are cached, but their values are not added to the source cube. For example, you can create a calculated member called Profit by subtracting the value of the member Costs from the value of the member Sales.

Calculated member definitions can be defined with three different scopes:

• Global scope. The definitions of these calculated members are stored in the cube so that the calculated members are available whenever you use the cube. Create calculated members with global scope by using the Calculated Member Builder in the Cube Editor in OLAP Manager.
• Session scope. The definitions of these calculated members are cached for the current session so that the calculated members are available until you close the connection. Create calculated members with session scope by using the CREATE MEMBER statement. The CREATE MEMBER statement is beyond the scope of this book. You can learn more about the CREATE MEMBER statement in the OLE DB for OLAP Programmer s Reference.
• Query scope. The calculated member is created for and used in a single query. Create calculated members with query scope by using the WITH clause in an MDX query.

Using the WITH Clause

Use the WITH clause as part of an MDX query to create a calculated member.

Syntax

 WITH <single_formula_specification> [<single_formula_specification>...]  SELECT <axis_specification> [, <axis_specification>...]  FROM <cube_specification>  WHERE <slicer_specification> 

When defining calculated members, the syntax of <single_formula_specification> is

MEMBER <parent_of_member>.<member_name> AS '<value_expression>'

Operators and Numeric Functions

You can use the standard mathematical operators (+, -, *, /) to perform calculations on members and measures of the cube when you specify a calculated member.

In addition, you can specify a calculated member by using numeric functions with members or measures in the cube. Using numeric functions, you can apply mathematical operations to the set of members as a whole. The following table describes frequently used numeric functions.

Numeric Function	Description
Sum	Returns the sum of the values of all the members in the set.
Aggregate	Chooses the proper aggregate function to apply based on the aggregation type of the member on which it operates. For example, if you use the Aggregate function on a measure that is a sum, Aggregate returns a sum.
Count	Returns the number of tuples in a set.
Avg	Returns the average value of the numeric expressions in a set.

Examples

This example adds a calculated measure named [CostPercentOfSales] to an MDX query. The expression calculates [Sales Cost] as a percentage of [Store Sales]. The query then specifies the calculated measure as a slicer dimension in the WHERE clause.

 WITH MEMBER [Measures].[CostPercentOfSales] AS  '([Measures].[Store Cost] / [Measures].[Store Sales] * 100)'  SELECT  {[Product Family].Members} ON COLUMNS,  {[Store Type].Members} ON ROWS  FROM Sales  WHERE ([Measures].[CostPercentOfSales]) 

The results of this query are displayed in the following table:

	Drink	Food	Non-Consumable
All	39.88	39.92	39.94
Deluxe supermarket	39.80	39.98	40.02
Gourmet supermarket	39.28	40.08	39.60
Mid-size grocery	39.99	39.98	39.70
Small grocery	39.97	39.96	40.24
Supermarket	39.99	39.85	39.94

This example reports on the average monthly sales of stores for 1997. It uses a calculated member with the Avg function to calculate the average monthly sales. The average is calculated only for months in which sales were made.

 WITH MEMBER [Time].[1997 Monthly Avg] AS   'Avg({Descendants([Time].[1997], [Month])})'  SELECT   {[Time].[1997], [Time].[1997 Monthly Avg]} ON COLUMNS,  {Descendants([USA], [Store Name])} ON ROWS  FROM Sales  WHERE (Gender.[F], [Measures].[Store Sales],   [Product].[Beer and Wine].[Beer]) 

The results of this query are displayed in the following table:

	1997	Monthly Avg
Store 6	121.44	10.12
Store 7	92.56	13.22
Store 24	177.91	14.83
Store 14	5.95	2.98
Store 11	143.99	14.40
Store 13	284.43	25.86
Store 2	15.61	3.90
Store 3	177.56	16.14
Store 15	197.56	17.96
Store 16	109.85	12.21
Store 17	228.99	19.08
Store 22	10.80	5.40
Store 23	121.86	13.54

This example shows two calculated members in the Time dimension called Spring and Summer. It uses the Sum function in the WITH clause to define the calculated members based on a range of months. The calculated members are projected on the COLUMNS axis.

 WITH MEMBER [Time].[Spring] AS 'Sum({[Month].[3]:[Month].[5]})'  MEMBER [Time].[Summer] AS 'Sum({[Month].[6]:[Month].[8]})'  SELECT   {[Time].[Spring], [Time].[Summer]} ON COLUMNS,  NON EMPTY {Descendants([Store], [Store Name])} ON ROWS  FROM Sales  WHERE ([Profit]) 

The results of this query are displayed in the following table:

	Spring	Summer
Store 6	6894.56	5947.63
Store 7	6785.05	8608.87
Store 24	8025.35	8568.31
Store 14	617.28	697.49
Store 11	8610.40	7803.35
Store 13	13800.01	13763.78
Store 2	668.09	571.57
Store 3	7483.53	8467.33
Store 15	8175.58	7532.39
Store 16	7020.66	7661.51
Store 17	9857.31	11888.77
Store 22	673.00	698.17
Store 23	3885.27	3056.74

Guidelines for Working with Formulas and Sets

Be careful to avoid recursive formulas. In general, formulas and sets can be defined in terms of other formulas and sets. Proper planning should prevent formulas and sets from referring to themselves, thus preventing recursive function calls. The following guidelines are important when creating your formulas:

• Use fully qualified explicit member names to eliminate ambiguity in MDX queries.
• Use [square brackets] to delimit member and measure names that contain spaces or are reserved words.
• Add nondefault measures to the results by including them on one of the axes or by listing them in the WHERE clause.
• Use member set functions such as Children and Members rather than explicitly listing all members on an axis.
• Use the Crossjoin function to generate combinations of several members rather than explicitly listing these combinations.

Exercise 4: Creating a Calculated Member

In this exercise, you will write and execute an MDX query that defines and uses a calculated member.

To use calculated members

In this procedure, you will modify the following MDX query to include a calculated member.

 SELECT  {[Product].Children} ON COLUMNS,  NON EMPTY {Crossjoin({[Time].Children}, {[Shipper].Children})} ON ROWS  FROM [Sales]  WHERE ([Measures].[Line Item Total]) 

1. Modify the query to calculate the [Avg Unit Retail] of the line item totals by performing the following steps:

• Add the WITH MEMBER clause.
• Define the calculated member [Avg Unit Retail] as [Line Item Total] / [Line Item Quantity].
• Display the calculated member in the results by including it in the WHERE clause.

The completed MDX query should be similar to the following:

 WITH MEMBER [Measures].[Avg Unit Retail] AS     '[Measures].[Line Item Total] / [Measures].[Line Item  Quantity]',      Format = '$#.00'  SELECT  {[Product].Children} ON COLUMNS,  NON EMPTY {Crossjoin({[Time].Children}, {[Shipper].Children})} ON  ROWS  FROM [Sales]   WHERE ([Measures].[Avg Unit Retail]) 

2. Execute the query.

Lesson Summary

When you create an MDX query, you use the parts of the SELECT statement to specify the axes, the data source, and the slicer dimensions. MDX provides many useful functions that help you to easily query the hierarchies in your multidimensional cubes. The WITH clause allows you to create calculated members that you can include in your query results.