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

Auto-exist means that not only members that are actually present in the subcube defini-
tion will be returned, but also any members that exist together in the dimensional table.
So, if we modify the preceding query to drill down into the months rather than the years,
we will get data for the months of 1998. That modified query follows:
SELECT [Store].[Stores].[Store Country].members ON COLUMNS,
[Time].[Time].[Month].members ON ROWS FROM
( SELECT {[Store].[Stores].[Store Country].[USA] } ON COLUMNS,
{ [Time].[Time].[1998] } ON ROWS FROM [Warehouse and Sales])
Figure 11.24 shows the results of this query.
Although the results of a query executed in the context of a subcube never contain tuples
that don't exist in the subcube, tuples that don't exist in the subcube can be referenced in
the query. The following query demonstrates this concept:
CREATE SUBCUBE [Warehouse and Sales] as SELECT
{[Store].[Stores].[Store Country].[USA] } ON COLUMNS,
{ [Time].[Time].[1998] } ON ROWS FROM [Warehouse and Sales]
We have created a subcube that restricts our multidimensional space to the data in the
1998 year period, but we need to filter out certain stores to restrict the results to the
stores in which sales in the current year (1998) have grown compared to the preceding
year (1997):
SELECT Filter([Store].[Stores].[Store].members,
([Measures].[Store Sales],[Time].[Time].[Year].[1998]) >
([Measures].[Store Sales],[Time].[Time].[Year].[1997])) ON COLUMNS,
[Time].[Time].[Year].members ON ROWS
FROM [Warehouse and Sales] WHERE [Measures].[Store Sales]
Because the year 1997 doesn't exist in the subcube, we need to go outside the boundaries
of the subcube to calculate the MDX expression used in the Filter expression. When we
execute the preceding query, it produces the results shown in Figure 11.25.
FIGURE 11.25 Tuples outside of the boundaries of the subcube can be used in MDX expres-
sions when the query executed in the context of the subcube.
Neither CREATE SUBCUBE nor SubSelect changes a current coordinate. (We introduced the
concept of the current coordinate in Chapter 10.) For example, if we create a subcube that
contains only the member 1998 and we issue the following query containing the
[Time].[Time].currentmember expression, the query will return the member ALL as the