Advanced MDX - Microsoft SQL Server 2008 Analysis Services - page 175

subcube, see the “Subcubes” section in Chapter 2, “Multidimensional Space.”) Let's move
the Time attribute to the WHERE clause. Now our query reads like this:

SELECT { [Warehouse].[Warehouses].[Country].&[Canada],
[Warehouse].[Warehouses].[Country].&[Mexico],
[Warehouse].[Warehouses].[Country].&[USA] } ON COLUMNS
FROM [Warehouse and Sales]
WHERE {{[Product].[Products].[Product Family].[Drink],
[Product].[Products].[Product Family].[Food] } *
{[Time].[Time].[Year]. [1997], [Time].[Time].[Year].[1998]} }

Figure 11.19 shows the results of this query.

Canada Mexico USA

29341 114642 366393

FIGURE 11.19

This query with a crossjoin of two sets in WHERE clause.

NOTE
One limitation applies to the types of sets that can be placed in a WHERE clause: You
cannot use more than one measure in a WHERE clause. That's understandable because
the aggregation of values that result from a query sliced by two wouldn't make sense.

Analysis Services doesn't limit you to the use of “pure” crossjoins in a WHERE clause; you
can also use an arbitrary set. Internally, it would be resolved to an arbitrary subcube. (For a
definition of an arbitrary subcube, see the “Subcubes” section in Chapter 2.) The following
query is an example of a WHERE clause with an arbitrary set:

SELECT { [Warehouse].[Warehouses].[Country].&[Canada],
[Warehouse].[Warehouses].[Country].&[Mexico],
[Warehouse].[Warehouses].[Country].&[USA] } ON COLUMNS
FROM [Warehouse and Sales]
WHERE {([Product].[Products].[Product Family].[Drink], [Time].[Time].[Year].[1997]),
([Product].[Products].[Product Family].[Food] , [Time].[Time].[Year].[1998]) }

Figure 11.20 shows the results of this query.

Canada Mexico USA

26249 99747 182957

FIGURE 11.20

These are the results of a query execution with an arbitrary set in the WHERE

clause.