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

As you can see, we are defining a subcube using the SELECT statement. The designers of
Analysis Services chose to use SELECT statements to define subcubes because any SELECT
statement results in a multidimensional space. That multidimensional space is a subspace
of the original cube—a subcube.

However, the definition of the axes used in such a SELECT statement is a little different. In
a regular SELECT statement, it's usually the axes that define the shape of the resulting
multidimensional space. However, shape of the subcube is exactly the same as the shape
(but smaller) of the original cube. We would get the same result if we rewrote the CREATE
SUBCUBE statement and projected the Time dimension against the COLUMNS axis and the
Store dimension against the ROWS axis (or even if we projected both of these dimensions
against the same axis).

NOTE
Even though a subcube definition has almost the same syntax as a SELECT statement,
there are some syntactical limitations. For example, a subcube definition can't have
WITH and NON EMPTY clauses.

After a subcube is created, the result of any query issued against it will contain only
members that exist in the subcube. So, if we write an MDX query to analyze the products
in different product families that were sold over some period of time in different stores,
we'll get results, as shown in Figure 11.23, and they will contain only information about
sales that happened in 1998 in U.S. stores:

SELECT [Store].[Stores].[Store Country].members ON COLUMNS,
[Time].[Time].[Year].members ON ROWS FROM [Warehouse and Sales]

USA
222276

1998

FIGURE 11.23 When our query is executed in the context of the subcube, only products sold
in the United States in 1998 (tuples that exist in the subcube) are returned to the user.

The SubSelect construct is very similar to the CREATE SUBCUBE statement. The only differ-
ence is the lifetime of the subcube. Analogous to the CREATE SUBCUBE statement, the
SubSelect construct also uses a SELECT statement to create a subcube.

A FROM clause contains information about the source of data to be queried (a cube or a
subcube). If the source of data is a subcube, the FROM clause needs some sort of language
construct to define that subcube, and that would be a nested SELECT statement—a SubSelect
clause. The following is a Backus Naur Form (BNF) of a query with a SubSelect clause:

WITH <with clause> SELECT <select clause> FROM [NON VISUAL](<subcube>)
WHERE <where clause>

If we need to define a subcube only for the lifetime of a single query, we can use SubSelect
to write an MDX statement. This query produces exactly the same results as a SELECT