MDX Query

Chapter 2 introduced you to the MDX SELECT statement. The syntax for an MDX query is as follows

     [WITH <formula_expression> [, <formula_expression> ...]]     SELECT [<axis_expression>, [<axis_expression>...]]     FROM [<cube_expression>]     [WHERE [slicer_expression]] 

You might be wondering whether the SELECT, FROM, and WHERE format is similar to the Structured Query Language (SQL), serve a purpose similar to SQL. Even though the SELECT-FROM-WHERE looks identical to SQL in MDX, you can do more complex operations in MDX. You learn some of these operations in this chapter and throughout the book.

The keywords WITH, SELECT, FROM, and WHERE along with the expressions following them are together referred to as a clause. In the preceding MDX query template anything specified within the square brackets means it is optional; that is, that section of the query is not mandatory in every MDX query.

You can see that the WITH and WHERE clauses are optional because they are enclosed within [ and ] characters. Therefore, you might be thinking that the simplest possible MDX query should be the following

     SELECT     FROM [Adventure Works] 

Super! You are absolutely correct. This MDX query returns a single value; the tuple corresponding to this value uses the default members of the dimension to retrieve the value. You might recall that the fact data is stored in a special dimension called Measures. When you send the preceding query to the Analysis Services instance you get the default member from the Measures dimension, which is one of the measures in the cube. The result of this query is the aggregated result of all the cells in the cube of this measure for the default values of each dimension in the cube.

The WITH clause is typically used for custom calculations and operations, and you learn about this in the later part of this chapter. First, take a look at the SELECT, FROM, and WHERE clauses.

SELECT Statement and Axis Specification

The MDX SELECT statement is used to retrieve a subset of the multidimensional data from an OLAP Server. In SQL the SELECT statement is used to retrieve columnar data, that is, data is retrieved as columns. Columnar data is one-dimensional and therefore a single axis is sufficient for projecting this data. In SQL you retrieve data as ROWS and COLUMNS, which is viewed as two-dimensional data. If you consider the two-dimensional coordinate system, you have the X and Y axes. The X axis is used for the COLUMNS and the Y axis is used for ROWS. Multidimensional data, however, are specified in a way such that data can be retrieved on multiple axes. Indeed, MDX provides you with the capability of retrieving data on multiple axes.

The syntax of the SELECT statement is

     SELECT [<axis_expression>, [<axis_expression>...]] 

The axis_expression specified after the SELECT refers to the dimension data you are interested in retrieving. These dimensions are referred to as axis dimensions because the data from these dimensions are projected onto the corresponding axes. The syntax for axis_expression is

     <axis_expression> := <set> ON Axis (axis number) 

Axis dimensions are used to retrieve multidimensional result sets. A set, the ordered collection of tuples, is defined to form an axis dimension. MDX provides you with the capability of specifying up to 128 axes in the SELECT statement. The first five axes have aliases. They are COLUMNS, ROWS, PAGES, SECTIONS, and CHAPTERS. Subsequent axes are specified as Axis (axis number). Take the following example:

     SELECT    Measures.[Internet Sales Amount] ON COLUMNS,             [Customers].[Country].MEMBERS on ROWS,             [Product].[Product Line].MEMBERS on PAGES 

Three axes are specified in the SELECT statement. Data from dimensions Measures, Customers, and Product are mapped on to the three axes to form the axis dimensions.

Axis Dimension

The axis dimension is what you build when you define the SELECT statement. When you build a SELECT statement, it functions by assigning a set to both COLUMNS and ROWS or additional axes — if you have more than two axes in your query. Unlike the slicer dimension (described later in this chapter), the axis dimension retrieves and retains data for multiple members, not by single members.

Important

No Shortcuts! In MDX you cannot create a workable query that omits lower axes. If you want to specify a PAGES axis, you must also specify COLUMNS and ROWS.

FROM Clause and Cube Specification

The FROM clause in the MDX query determines the cube from which you retrieve and analyze data. It's similar to the FROM clause in a SQL query where you specify a table name. The FROM clause is a necessity for any MDX query. The syntax of the FROM clause is

     FROM <cube_expression> 

The cube_expression denotes the name of a cube or a sub section of the cube from which you want to retrieve data. In SQL's FROM clause you can specify more than one table, but in an MDX FROM clause you can define just one cube name. The cube specified in the FROM clause is called the cube context and the query is executed within this cube context. That is, every part of the axis_expression to be retrieved from the cube context is specified in the FROM clause:

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

This query is a valid MDX query that retrieves the [Internet Sales Amount] measure on the X-axis. The measure is retrieved from the cube context [Adventure Works]. Even though the FROM clause restricts you to working with only one cube or section of a cube, you can retrieve data from other cubes using the MDX function LookupCube. When there are two ore more cubes having common dimension members then the LookupCube function helps you to retrieve measures outside the CURRENTCUBE's context with the help of the common dimension members.

WHERE Clause and Slicer Specification

In pretty much any relational database work that you do, you will want to issue queries that return only specific portions of the overall data available in a given table or set of joined tables, and/or joined databases. This is accomplished using SQL statements to map out what data you do and do not want returned as a result of running your query. Here is an example of an unrestricted SQL query on a table named Product that contains sales information for products:

     SELECT *     FROM Product 

This query results in five columns being retrieved with several rows:

Product ID	Product Line	Color	Weight	Sales
1	Accessories	Silver	5.00	200.00
2	Mountain	Grey	40.35	1000.00
3	Road	Silver	50.23	2500.00
4	Touring	Red	45.11	2000.00

The * represents "all," meaning that query will dump the entire contents of the table. If you want to know the Color by each Product Line, you have to restrict the query so that it returns only the information you want. The following simple example demonstrates a query constructed to return just two columns from the table:

     SELECT ProductLine, Color     FROM Product 

This query returns the following:

Product Line	Color
Accessories	Silver
Mountain	Grey
Road	Black
Touring	Red

The concept of crafting queries to return only what you need maps directly to MDX from SQL. In fact, they share a conditional statement that adds a whole new level of power to restricting queries to return only desired data. It is called the WHERE clause. After taking a look at the SQL use of WHERE you will see how the concept applies to MDX. Here is a SQL query that uses WHERE to return two columns for those products with a silver color from the Product table:

     SELECT ProductLine, Sales     FROM Product     WHERE Color = 'Silver' 

This query returns the following:

Product Line	Sales
Accessories	200.00
Road	2500.00

The same concept applies to MDX. Indeed, MDX uses both SELECT and WHERE statements. The SELECT statement is used to identify the dimensions and members a query will return and the WHERE statement limits the result set by some criteria; the preceding example is restricted by Color = ‘Silver’. Note that members are the elements that make up a dimension's hierarchy. The Product table, when modeled as a cube, will contain two measures, Sales and Weight, and a Product dimension with the hierarchies ProductID, ProductLine, and Color. In this example the Product table is used as a fact as well as dimension table. An MDX query against the cube that produces the same results as that of the SQL query is

     SELECT Measures.[Sales] ON COLUMS,     [Product].[Product Line].MEMBERS on ROWS     FROM ProductsCube     WHERE ([Product].[Color].[Silver]) 

The two columns selected in SQL are now on the axes COLUMNS and ROWS. The condition in the SQL WHERE clause, which is a string comparison, is transformed to an MDX WHERE clause, which refers to a slice on the cube that contains products that have silver color. Even though the SQL and MDX queries look similar, the operations in SQL Server and Analysis Services are quite different.

Slicer Dimension

The slicer dimension is what you build when you define the WHERE statement; it is a filter that removes unwanted dimensions and members. What makes things even more interesting is that the slicer dimension then includes any axis in the cube not overtly included in any of the queried axes. The default members of the hierarchies not included in the query axes are used in the slicer axis. Regardless of how it gets its data, the slicer dimension will only accept MDX expressions (described later in this chapter) that evaluate to a single tuple. When there are multiple tuples specified on the slicer axis then MDX will evaluate these tuples as a set and the results of the tuples are aggregated based on the measures included in the query and the aggregation function of that specific measure.

WITH Clause and Calculated Member

Often business needs involve calculations that must be formulated within the scope of a specific query. The WITH clause in the MDX query provides you with the ability to create such calculations within the context of the query. In addition, you can also retrieve results outside the context of the current cube identified using the LookupCube MDX function.

Typical calculations that are created using the WITH clause are named sets and calculated members. In addition to these, the WITH clause also provides you with functionality to define cell calculations, load a cube into an Analysis Server cache for improving query performance, alter the contents of cells by calling functions in external libraries, and additional advanced concepts such as solve order and pass order. You learn about the named sets, calculated members, and calculated measures in this chapter, and Chapter 7 covers the rest.

The syntax of a WITH clause is

     [WITH <formula_expression> [, <formula_expression> ...]] 

The WITH clause provides with you the capability of specifying several calculations within one statement. The formula_expression will vary depending upon the type of calculation, and each such calculation is separated by a comma.

Named Sets

As you learned earlier, a set is a collection of tuples. The set expression, even though simple, can often be quite lengthy and this might make the query appear to be complex and unreadable. MDX provides you with the capability of dynamically defining sets with a specific name so that the name can be used within the query. Think of it as an alias for the collection of tuples in the set you are interested in retrieving data from. This is called a named set. A named set is nothing but an alias for a regular MDX set expression that can be used anywhere within the query as an alternative to the actual set expression.

Consider the case where you have customers in various countries. Suppose you want to retrieve the Sales information for customers in Europe, and then you need to use the customer members in the various countries in Europe. Your MDX query would look like this:

     SELECT Measures.[Internet Sales Amount] on COLUMNS,     {[Customer].[Country].[Country].&[France],     [Customer].[Country].[Country].&[Germany],     [Customer].[Country].[Country].&[United Kingdom]} ON ROWS     FROM [Adventure Works] 

This query is not too lengthy, but you can imagine a query that would contain a lot of members and functions being applied to this specific set several times within the query. Instead of using the completed set every time in the query, you can create a named set and then use the alias in the query as follows:

     WITH SET [EUROPE] AS '{[Customer].[Country].[Country].&[France],     [Customer].[Country].[Country].&[Germany],[Customer].[Country].[Country].&[United Kingdom]}'     SELECT Measures.[Internet Sales Amount] on COLUMNS,     [EUROPE] ON ROWS     FROM [Adventure Works] 

The formula_expression of the WITH clause for named sets is

     Formula_expression := SET <set_alias_name> AS [']<set>['] 

The alias name can be any alias name and is typically enclosed within angle brackets. Note the keywords SET and AS that are used in this expression to specify a named set. The actual set of tuples do not have to be enclosed within single quotes (”). The single quotes are still available for backwards comparability reason since Analysis Services 2000 expected the set to be included within single quotes.

Calculated Members

Calculated members are calculations specified by MDX expressions. These MDX expressions are resolved as a result of MDX expression evaluation rather than just the retrieval of the original fact data. A typical example of a calculated member is the calculation of year-to-date sales of products. Let's say the fact data only contains sales information of products for each month and you need to calculate the year-to-date sales. This can be specified by MDX expressions using the WITH clause so that you can retrieve the sales information not only for each month, but also the year-to-date sales up to that month.

The formula_expression of the WITH clause for calculated members is

     Formula_expression := MEMBER <MemberName> AS [‘]<MDX_Expression>[‘],                     [ , SOVLE_ORDER = <integer>]                     [ , <CellProperty> = <PropertyExpression>] 

MDX uses the keywords MEMBER and AS in the WITH clause for creating calculated members. The MemberName should be a fully qualified member name that includes the dimension, hierarchy, and level under which the specific calculated member needs to be created. The MDX_Expression should return a value that corresponds to the member. The SOLVE_ORDER, which is an optional parameter, should be a positive integer value if specified. The SOLVE_ORDER determines the order in which the members need to be evaluated when multiple calculated members are defined. The CellProperty is also an optional parameter and it is used to specify cell properties for the calculated member such as text formatting of the cell contents including background color.

All the measures in a cube are stored in a special dimension called Measures. Calculated members can also be created on the measures dimension. In fact, most of the calculated members that are used for business are usually on the measures dimension. Calculated members on the measures dimension are referred to as calculated measures. Following are examples of calculated member statements.

Example 1

     WITH MEMBER [MEASURES].[Profit] AS '([Measures].[Internet Sales Amount] -                 [Measures].[Total Product Cost])'     SELECT [MEASURES].[Profit] ON COLUMNS,     [Customer].[Country].MEMBERS ON ROWS     FROM [Adventure Works] 

In example 1 a calculated member Profit has been defined as the difference of the measures [Internet Sales Amount] and [Total Product Cost]. When the query is executed for every country, this calculated member will be evaluated based on the MDX expression.

Example 2

     WITH     SET [ProductOrder] AS 'Order ([Product].[Product Line].members, [Internet Sales     Amount], BDESC)' MEMBER [Measures].[ProductRank] AS 'Rank ([Product].[Product Line].CURRENTMEMBER,     [ProductOrder])'     SELECT {[ProductRank],[Sales Amount]} on COLUMNS,     [PRoductOrder] on ROWS     FROM [Adventure Works] 

Example 2 includes creation of a named set and a calculated member within the scope of the query. The query orders the Products based on the Internet Sales Amount and returns the sales amount of each product along with the rank. The named set [Product Order] is created so that the members within this set are ordered based on the Sales. This is done by using an MDX function called Order (you learn more about the Order in Appendix A). To retrieve the rank of each product, a calculated member [ProductRank] is created using the MDX function Rank.

The results of the preceding query on the sample Adventure Works Analysis Services database is

	Product Rank	Sales Amount
All Products	1	$109,809,274.20
Road	2	$48,262,055.15
Mountain	3	$42,456,731.56
Touring	4	$16,010,837.10
Accessory	5	$2,539,401.59
Components	6	$540,248.80

Example 3

     WITH MEMBER Measures.[Cumulative Sales] AS 'SUM (YTD (),[Internet Sales Amount])'     SELECT {Measures.[Internet Sales Amount], Measures.[Cumulative Sales]} ON 0,     [Date].[Calendar].[Calendar Semester].MEMBERS on 1     FROM [Adventure Works] 

In example 3 a calculated member is created so that you can analyze the sales amount of each month along with the cumulative sales. For this two MDX functions are used: the SUM and the YTD. The YTD MDX function is called without any parameter so that the default Time member at that level is used in the calculation. The SUM function is used to aggregate the sales amount for that specific level. The result of the preceding query on the sample Analysis Services database is shown in the table below. You can see that the Cumulative Sales corresponding for the members H2 CY 2002, H2 CY 2003, and H2 CY 2004 show the sum of Internet Sales Amount for that member and the previous half year for the corresponding year.

	Internet Sales Amount	Cumulative Sales
H2 CY 2001	$3,266,373.66	$3,266,373.66
H1 CY 2002	$3,805,710.59	$3,805,710.59
H2 CY 2002	$2,724,632.94	$6,530,343.53
H1 CY 2003	$3,037,501.36	$3,037,501.36
H2 CY 2003	$6,753,558.94	$9,791,060.30
H1 CY 2004	$9,720,059.11	$9,720,059.11
H2 CY 2004	$50,840.63	$9,770,899.74

Example 4

     WITH MEMBER [Date].[Calendar].[%Change] AS     100* (([Date].[Calendar].[Calendar Quarter].[Q2 CY 2002] -       [Date].[Calendar].[Calendar Quarter].[Q1 CY 2002])    /       [Date].[Calendar].[Calendar Quarter].[Q2 CY 2002])     SELECT {[Date].[Calendar].[Calendar Quarter].[Q1 CY 2002],     [Date].[Calendar].[Calendar Quarter].[Q2 CY 2002],     [Date].[Calendar].[%Change]} ON COLUMNS,     Measures.[Internet Sales Amount] ON ROWS     FROM [Adventure Works] 

The above query shows an example of a calculated member defined in the Date dimension to return a quarter-over-quarter comparison of the sales amount. In this example, quarter 1 and quarter 2 of the year 2002 are being used. The result of this query is

	Q1 CY 2002	Q2 CY 2002	%Change
Sales Amount	$1,791,698.45	$2,014,012.13	11.038