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

Execution of a query that includes a set in the WHERE clause is naturally more expensive
than execution of a query that contains a single tuple. But, executing a query that
contains an arbitrary set triggers a quite advanced algorithm. So, it can bring some perfor-
mance penalties. In most cases, we advise that you rewrite the query to put a normalized
set in the WHERE clause.
SubSelect and Subcubes
A basic MDX query retrieves data from the multidimensional space of a whole cube.
However, sometimes it's very useful first to limit the multidimensional space according to
certain rules, create a subspace—a subcube—and then execute a query to retrieve data
from the subcube. An example of an application that takes advantage of the subcube
feature is a cube browser in the Business Intelligence Development Studio. With this
browser, a user can filter out part of a cube to create a subcube. She would choose a subset
of various dimension members, and then browse the data within the subset. What makes
this possible is that, behind the scenes, the browser creates a subcube from the dimension
members. The browser can then use Office web components to generate the same sort of
queries to browse the subcube in the same way it would browse the whole cube.
We illustrate the concept of a subcube with the simple cube shown in Figure 11.21. It has
three dimensions— Store , Product , and Time —and a measure dimension with three
measures: Store Sales , Store Cost , and Unit Sales . We can create a subcube that has
members corresponding to the year 1998 and contains only stores located in the United
States. We have shaded the space of the subcube so that you can see that the subcube has
the same structure as the original cube, except that it's been carved out of the original cube.
When we analyze the products in the Food and Drink product families that were sold over
period of time in all the stores, we will get the results shown in Figure 11.22. The results
contain only data for the members that exist in our subcube.
To make it possible to query subcubes, Analysis Services supports two new MDX
constructs: SubSelect and CREATE SUBCUBE . In reality, both are parts of the same function-
ality—creating a subcube and executing a query in the context of this subcube. SubSelect
enables you to write a single query that contains definitions of both axes and a subcube.
You use a standalone MDX statement ( CREATE SUBCUBE ) to create a subcube. This state-
ment creates a subcube definition in context of which all subsequent queries will be
executed until a DROP SUBCUBE statement is issued. So, the difference between SubSelect
and a subcube is just the lifetime of the subcube definition.
In the following example, we write a CREATE SUBCUBE statement that creates the subcube
illustrated in Figure 11.21:
CREATE SUBCUBE [Warehouse and Sales] as SELECT
{[Store].[Stores].[Store Country].[USA] } ON COLUMNS,
{ [Time].[Time].[1998] } ON ROWS FROM
[Warehouse and Sales]