Advanced Topics in MDX - Professional SQL Server Analysis Services 2005 with MDX - page 296

Using CREATE and DROP SUBCUBE
By default you retrieve all cells from the entire cube space. You might want to
restrict your analysis to specific slices or sections of the cube though. For ex-
ample, if you are analyzing the sales information for the year 2005, you might
want to reduce your search space to just the year 2005. There are several
ways to restrict your cube space in Analysis Services 2005. If you are query-
ing the cube you can restrict the cube space with the CREATE SUBCUBE
statement, which then restricts the cube space for subsequent queries. As-
sume you are analyzing the Internet Sales information in the Adventure
Works DW database for various quarters. You use the following MDX query:
select [Measures].[Internet Sales Amount] on 0,
[Date].[Fiscal].[Fiscal Quarter].members on 1
from [Adventure Works]
If you want to restrict your cube space and only analyze the Internet sales
data for the year 2004, one way to do so is to use the CREATE SUBCUBE
statement. The syntax of the statement is
CREATE SUBCUBE <SubCubeName> AS <SELECT Statement>
where the SELECT clause is an MDX SELECT clause that returns the results
for the restricted cube space based on specific criteria.
The following MDX statements help you restrict the cube space to year 2006
by using the CREATE SUBCUBE statement and then querying the Internet
sales for all the quarters in that year.
CREATE SUBCUBE [Adventure Works] as SELECT
([DATE}{FISCAL]. [FISCAL YEAR] & [2004],
OK)
on 0 from [Adventure Works]
select [Measures].[Internet Sales Amount] on 0,
[Date].[Fiscal].[Fiscal Quarter].members on 1
from [Adventure Works]
DROP SUBCUBE [Adventure Works]