MDX Concepts - Microsoft SQL Server 2008 Analysis Services - page 152

Or, we can put this expression into a SELECT statement and execute it in the SSMS. Figure
10.15 shows the results.

FIGURE 10.15 The Filter function returns the set of stores that have been less profitable
in 1998 than in 1997.

SELECT
Filter([Store].[Stores].[Store].members,
([Unit Sales],[1998]) < ([Unit Sales],[1997])) ON COLUMNS,
{[1997],[1998]} ON ROWS FROM [Warehouse and Sales] WHERE [Unit Sales]

In MDX, it is not only the cell values that are calculated in the query context; all MDX
expressions are calculated in the query context, too. To execute a Filter function, we
have to evaluate a filter expression: ([Unit Sales],[1998]) < ([Unit Sales],[1997]) .
This expression contains only attributes from the Measure and Time dimensions. All the
other attributes are obtained by the steps described in the section “Query Execution
Context.” Analysis Services first applies the default members of all the attributes to the
current coordinate, and then it overwrites the attributes referenced in the WHERE clause.
Then Analysis Services overwrites the attributes from the expression and, finally, it over-
writes the attributes in the filtering set. Let's use another example to show which attrib-
utes are used during different stages of Filter function execution. Let's assume that we
need to analyze the sales by store in the year 1997, and filter out the stores that sold more
than 1,000 items, as in Listing 10.6.

LISTING 10.6

A Filter Expression Affects the Current Coordinate

SELECT Filter( [Store].[Store].[Store Country].members,
➥

[Measures].[Unit Sales].Value >1000) ON COLUMNS

FROM [Warehouse and Sales] WHERE ([Time].[Time].[Year].[1997])

➥

In this query, we filter the stores by the measure Unit Sales . Our FoodMart stores were
first created in the United States; so, in 1997, all sales occurred only in the United States.
But in 1998, products were sold in all three countries. If the Filter expression were calcu-
lated in the context of the expression rather than the context of the entire query, we
would get all three countries: the United States, Mexico, and Canada. (The default
member of the Time dimension is the member ALL .) In this query, however, we get only
one county—USA—because the current context for the execution of the expression
includes members specified in the WHERE clause (see Figure 10.16).

To look a little deeper into this case, we can ask, “What would happen if the attributes
used in the filter expression were the same as the attributes used in the WHERE clause?”
Let's say that the query has a Measure dimension in both the WHERE clause and the filter