Restricting Cube SpaceSlicing Cube Data

Restricting Cube Space/Slicing Cube Data

A typical cube contains several dimensions and each dimension has several hundred or thousands of members. For example, if you have a sales cube that contains products, the products dimension would have at least hundreds of products if not thousands. During analysis of your data you would typically want to slice the data or drill down into specific sections of the cube to glean insights hidden in the data. Client tools help you to do slice, dice, and drill down in cubes. These client tools dynamically generate the MDX to restrict the cube space. Such tools dynamically generate MDX queries. In this section, "restricting cube space" is used loosely in the sense that we refer to restricting the cube space in the context of MDX scripts or restricting the data being returned to the client where users typically slice or dice the data. Several ways exist to restrict the cube space and analyze data and this section discusses some of the ways of restricting cube space using MDX to appropriately retrieve the section of the data.

Using the SCOPE STATEMENT

You already learned some techniques to restrict searchable cube space; use of which would depend on the context of your problem and what you are trying to accomplish by restricting the cube space. To refresh your memory, the SCOPE statement within MDX Scripts is used to restrict the cube space to form a subcube, which is a part of the cube projected along the dimensions mentioned within the SCOPE statement. The SCOPE statement is often used for cell calculations where the assignment statement typically is used with the "This" function. An example to recap what you learned in the previous section is:

     SCOPE ([Date].[Fiscal Year].&[2005],          [Date].[Fiscal].[Month].Members,          [Measures].[Sales Amount Quota]);    This = (ParallelPeriod ( [Date].[Fiscal].[Fiscal Year],    1,[Date].[Fiscal].CurrentMember),[Measures].[Sales Amount])*2;    END SCOPE; 

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 example, 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 querying the cube you can restrict the cube space with the CREATE SUBCUBE statement, which then restricts the cube space for subsequent queries. Assume 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] 

In the above CREATE SUBCUBE statement, the * in the SELECT statement indicates selection of the default members in the remaining hierarchies of the dimensions. If the All member of a hierarchy is included, all the members of that specific hierarchy are included in the subcube. Subsequent queries to the Adventure Works cube will evaluate to the restricted cube space corresponding to the year 2004. The query selecting Fiscal Quarters provides the results shown in the following table. If you did not create the subcube, you will see quarters for all the Fiscal Years in the cube.

	Internet Sales Amount
Q1 FY 2004	$2,744,340.48
Q2 FY 2004	$4,009,218.46
Q3 FY 2004	$4,283,629.96
Q4 FY 2004	$5,436,429.15

Once you have completed your analysis on the restricted cube space you can revert back to the original cube space by dropping the subcube using the DROP SUBCUBE statement followed by the name of the subcube you just created. The CREATE SUBCUBE statement is typically used within the scope of a query session where you want to perform analysis on restricted cube space. At query time you can reduce the cube space using the CREATE SUBCUBE statement and use the CREATE CELL CALCULATION statement for applying cell calculation. You can later query within the context of the subcube created.

Using EXISTS

As mentioned earlier, the cube space in Analysis Services 2005 is quite large and typically sparse given the nature of the attribute model. Remember AUTOEXISTS? That's where querying the cross-join of attributes within the same dimension results in reducing the cross-join set so that only members that exist with one another are returned. Well, EXISTS is a function that explicitly allows you to do the same operation of returning a set of members that exists with one or more tuples of one or more sets. The EXISTS function can take two or three arguments. The syntax of the EXISTS function is:

     EXISTS ( Set, <FilterSet>, [MeasureGroupName]) 

The first two arguments are Sets that get evaluated to identify the members that exist with each other. The third optional parameter is the Measure group name so EXISTS can be applied across the measure group. EXISTS identifies all the members in the first set that exist with the members in the FilterSet and returns those members as results. The following is an example of EXISTS where you analyze the sales of all customers who have four cars. In this example we use restricting cube space loosely to restrict the members in the Customer hierarchy. You can achieve similar results using a FILTER or NONEMPTY-CROSSJOIN function, which you explore later in this chapter.

     WITH SET [HomeOwnerCustomer] AS     EXISTS ([Customer].[Customer].[Customer].members,[Customer].[Number of Cars     Owned].&[4] )     SELECT [Measures].[Internet Sales Amount] on 0,     HomeOwnerCustomer on 1     from [Adventure Works] 

Out of 18,000 customers in the customer dimension, the query returns 1,262 customers.

An example of using EXISTS with the measure group name follows:

     WITH SET [HomeOwnerCustomer] AS     EXISTS ([Customer].[Customer].[Customer].members, [Product].[Product Model     Categories].[Category].&[1],     "Internet Sales")     SELECT [Measures].[Internet Sales Amount] on 0,     HomeOwnerCustomer on 1     from [Adventure Works] 

The above example identifies the customers who have bought products of category 1, which is Bikes. The measure group name [Internet Sales] is specified so that EXISTS uses the measure group to determine the set of customers who have bought bikes.

Using EXISTING

By now, you are quite familiar with the WHERE clause in the MDX SELECT statement. The WHERE clause only changes the default members of the dimensions for the current subcube and does not restrict the cube space. It does not change the default for the outer query and gets a lower precedence as compared to the calculations specified within the query scope. For example, look at the following MDX query:

     WITH MEMBER measures.x AS COUNT ([Customer].[Customer Geography].[State-     Province].MEMBERS)     SELECT measures.x ON 0     FROM [Adventure Works]     WHERE ([Customer].[Customer Geography].[Country].&[United States]) 

The query returns a value of 71. You know that there are 50 states within the United States and the count of customers should be <= 50. You get the value 71 because calculations are done at a scope larger than the one defined by the WHERE clause. In order to restrict the cube space so that calculations are done within the scope of the conditions specified in the WHERE clause, you can use several methods. One way to accomplish this is using the keyword EXISTING, by which you force the calculations to be done on a subcube under consideration by the query rather than the entire cube. Following is an MDX query using EXISTING:

     WITH MEMBER measures.x AS COUNT (     EXISTING [Customer].[Customer Geography].[State-Province].MEMBERS)     SELECT measures.x ON 0     FROM [Adventure Works]     WHERE ([Customer].[Customer Geography].[Country].&[United States]) 

The EXISTING keyword forces sets to be evaluated in the current context. One can argue that the current context is defined due to the WHERE clause, which does not actually restrict the cube space. As mentioned earlier we are using the term "restricting cube space" loosely just to show examples of how you can restrict the data in a cube to retrieve the results you are looking for.

Using SUB-SELECT

Analysis Services 2005 introduces a new clause called SUB-SELECT, by which you can query a subcube instead of the entire cube. The syntax of the SUB-SELECT clause along with SELECT is:

     [WITH <formula_expression> [, <formula_expression> ...]]     SELECT [<axis_expression>, <axis_expression>...]]     FROM [<cube_expression> | (<sub_select_statement>)]     [WHERE <expression>]     [[CELL] PROPERTIES <cellprop> [, <cellprop> ...]]     <sub_select_statement> =     SELECT [<axis_expression> [, <axis_expression> ...]]     FROM [<cube_expression> | (< sub_select_statement >)]     [WHERE <expression>] 

The cube_expression in the MDX SELECT statement can now be replaced by another SELECT statement called the sub_select_statement, which queries a part of the cube. You can have nested SUB-SELECT statements up to any level. The SELECT clause in the SUB-SELECT statement restricts the cube space on the specified dimension members in the SUBSELECT clause. Outer queries will therefore be able to see only the dimension members that are specified in the inner SELECT clauses. Look at the following MDX query that uses SUBSELECT syntax:

     SELECT NON EMPTY { [Measures].[Internet Sales Amount] } ON COLUMNS,     NON EMPTY { ([Customer].[Customer Geography].[Country].ALLMEMBERS ) }     DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS     FROM (         SELECT ( { [Date].[Fiscal].[Fiscal Year].&[2004],                    [Date].[Fiscal].[Fiscal Year].&[2005]                  }                )         ON COLUMNS         FROM (             SELECT ( { [Product].[Product Categories].[Subcategory].&[26],                        [Product].[Product Categories].[Subcategory].&[27]                      }                    )             ON COLUMNS             FROM [Adventure Works]             )         )     WHERE ( [Product].[Product Categories].CurrentMember,             [Date].[Fiscal].CurrentMember           )     CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE 

The query contains SUB-SELECT clause twice. The innermost query returns a subcube that only contains Products of SubCategory ids 26 and 27. Assume that this subcube is named subcube A. The second SUB-SELECT uses subcube A and returns another subcube with the restriction of Fiscal Years 2004 and 2006 . Finally, the outermost SELECT statement retrieves the Internet Sales for Customers in various countries. Here SUB-SELECT queries restrict the cube space to certain members on Product and Date dimensions, thereby the outermost SELECT statement queries data from a subcube rather than the entire cube space. If you execute the preceding query in SSMS, you will see the results shown in the following table. You can rewrite most queries using SUB-SELECT with the WHERE clause in Analysis Services 2005, which accepts Sets as valid MDX expressions. There are instances where subsets and where clause can return different results. More information is provided in the book MDX Solutions 2nd edition by George Spofford, et, al. (Wiley Publishing, Inc., 2006). Analysis Services 2005 only uses SUBSELECT syntax for queries built through the designer that creates reports. You learn more about creating reports on Analysis Services UDM in Chapter 17.

	Internet Sales Amount
Australia	$16,335.00
Canada	$12,168.00
France	$6,021.00
Germany	$6,060.00
United Kingdom	$7,932.00
United States	$30,435.00