MDX (Muti Dimensional Expressions) Fundamentals and Important Functions

Dear Student

----------------

Please create “Analysis Services Tutorial “ Cube Database to practice all these queries.

 
SELECT STATEMENT SYNTAX:
 
SELECT {MEASURES/MEMBERS} ON COLUMNS/0/AXIS(0),
{MEASURES/MEMBERS} ON ROWS/1/AXIS(1)
FROM CUBENAME
WHERE <SLICER CONDITION>
 
NOTE:
 
1. ONE MEMBER SHOULD BE IN ONE AXIS    (AXIS(0),AXIS(1),WHERE )
 
2. WHERE CLAUSE COLUMNS ARE NOT DISPLAYABLE, ONLY DOES FILTERING
  SSMS-->SSAS-->CONNECT CUBE DATABASE->NEW QUERY->MDX
 
1. CHOOSE THE CUBE FROM THE CUBE DROP DOWN
 
2. CHOOSE FACT TABLE FROM THE MEASURE GROUP DROP DOWN,SO THAT      
   RESPECTIVE DIMENSIONS AND OTHER OBJECTS YOU WILL FIND.
     (IF YOU WANT TO WORK WITH ALL, CHOOSE ALL)
3.      

SELECT

          FROM
          [Analysis Services Tutorial]
 

--THIS IS FOR DEFAULT MEASURE, TOP MEASURE DISPLAY

4.
          SELECT [Measures].[Internet Sales-Sales Amount] ON AXIS(0),
          [Date].[Calendar Year] ON AXIS(1)
          FROM
          [Analysis Services Tutorial]
 
--THIS IS FOR DEFAULT MEMBER, ALL MEMBERS AGGREGATE DISPLAY
 
5. TEST THESE THREE SCENARIOS AND OBSERVE THE RESULT
 
SC1:
 
SELECT
([Measures].[Internet Sales-Sales Amount],[Date].[Calendar Year].MEMBERS) ON 0
FROM
[Analysis Services Tutorial]
 
SC2:
 
SELECT
([Date].[Calendar Year].MEMBERS,[Measures].[Internet Sales-Sales Amount]) ON 0
FROM
[Analysis Services Tutorial]
 
SC3:
 
SELECT
([Measures].[Internet Sales-Sales Amount]) ON 0,
[Date].[Calendar Year].MEMBERS ON 1
FROM
[Analysis Services Tutorial]
 
 
6. LEFT JOIN RESULT TO INNER JOIN
 
SC3 RESULTS LEFT JOIN OUTPUT SINCE UNMATCHED YEAR ENTRIES ARE NULL VALUES.
    TO HAVE BETTER INTERSECTION AND TO GET INNER JOIN RESULT USE
          "NON EMPTY" FUNCTION
 
          SELECT
          ([Measures].[Internet Sales-Sales Amount]) ON AXIS(0),
          NONEMPTY([Date].[Calendar Year].MEMBERS) ON AXIS(1)
          FROM
          [Analysis Services Tutorial]
 
7. MDX HAS ONLY CROSS JOIN OPERATION (IMPLEMENTED IN THREE WAYS)   
    EXPLICITLY. INTERNALLY INNER AND LEFT JOIN WE CAN IMPLEMENT.
 
8. USAGE OF ISEMPTY
 
WITH
MEMBER ISCHECK AS IIF(ISEMPTY([Measures].[Internet Sales-Sales Amount]),99999,[Measures].[Internet Sales-Sales Amount])
 
SELECT
{[Measures].[Internet Sales-Sales Amount],ISCHECK} ON AXIS(0),
[Date].[Calendar Year].MEMBERS ON AXIS(1)
FROM
[Analysis Services Tutorial]

 

Q1: RETURN ALL COLUMNS FROM MEASUES SECTION (WORKS LIKE *)

ANS: SELECT [Measures].MEMBERS ON 0 FROM  [Analysis Services Tutorial]

Q2: DISPLAY ONLY SALESAMT, TAXMT FOR ALL CHILD VALUES OF PRODUCT

ANS:

SELECT {[Measures].[Internet Sales-Sales Amount],[Measures].[Internet Sales-Tax Amount]} ON 0,

 [Product].[Product Name].CHILDREN ON 1  FROM [Analysis Services Tutorial]

Q3: DISPLAY TOTAL NUMBER OF ROWS IN A FACT TABLE

ANS:

SELECT [Measures].[Internet Sales Count] ON 0 FROM  [Analysis Services Tutorial]

Q4: DISPLAY TOP 5 TAX AMOUNT PRODUCTS

ANS:

SELECT [Measures].[Internet Sales-Tax Amount] ON 0,

TOPCOUNT(ORDER([Product].[Product Name].CHILDREN,[Internet Sales-Tax Amount],DESC),5) ON 1

 FROM [Analysis Services Tutorial]

 

 SELECT [Measures].[Internet Sales-Tax Amount] ON 0,

 HEAD(ORDER([Product].[Product Name].CHILDREN,[Internet Sales-Tax Amount],DESC),5) ON 1

 FROM [Analysis Services Tutorial]

Q5: DISPLAY BOTTOM 5 TAX AMOUNT PRODUCTS

ANS:

 SELECT [Measures].[Internet Sales-Tax Amount] ON 0,

 BOTTOMCOUNT(ORDER([Product].[Product Name].CHILDREN,[Internet Sales-Tax Amount],DESC),5) ON 1

 FROM [Analysis Services Tutorial]

 

 SELECT [Measures].[Internet Sales-Tax Amount] ON 0,

 TAIL(ORDER([Product].[Product Name].CHILDREN,[Internet Sales-Tax Amount],DESC),5) ON 1

 FROM [Analysis Services Tutorial]

 

Q6:  DISPLAY TAX PAYERS OF MORE THAN 85K]

ANS:

SELECT [Measures].[Internet Sales-Tax Amount] ON 0,

 FILTER([Product].[Product Name].CHILDREN,[Internet Sales-Tax Amount]>85000) ON 1

 FROM [Analysis Services Tutorial]

 

 Q7.DISPLAY FIRST CHILD IN THE PRODUCTS LIST

ANS:

 SELECT [Measures].[Internet Sales-Tax Amount] ON 0,

 [Product].[Product Name].FIRSTCHILD ON 1

 FROM [Analysis Services Tutorial]

 

 Q7.DISPLAY LAST CHILD IN THE PRODUCTS LIST

ANS:

 SELECT [Measures].[Internet Sales-Tax Amount] ON 0,

 [Product].[Product Name].LASTCHILD ON 1

 FROM [Analysis Services Tutorial]

 

 Q8: DISPLAY TAXAMOUNT FOR ALL PRODUCTS ORDERED IN THE YEAR 2008,2009

 ANS:

 SELECT [Measures].[Internet Sales-Tax Amount] ON 0,

 ([Product].[Product Name].CHILDREN,

 {[Order Date].[Calendar Year].[2008],[Order Date].[Calendar Year].[2009]}

 ) ON 1

 FROM [Analysis Services Tutorial]

 

 Q9: DISPLAY ALL PRODUCTS IN ALL CALENDER YEARS ORDERED

Ans:

 SELECT [Measures].[Internet Sales-Tax Amount] ON 0,

 ([Product].[Product Name].CHILDREN,[Order Date].[Calendar Year].children) ON 1

 FROM [Analysis Services Tutorial]

 Q10; DISPLAY NONEMPTY PRODUCTS FROM THE LIST

 ANS :

 SELECT [Measures].[Internet Sales-Tax Amount] ON 0,

 NONEMPTY([Product].[Product Name].CHILDREN)  ON 1

 FROM [Analysis Services Tutorial]

 Q11: DISPLAY DISTINCT VALUES IN THE CROSS PRODUCT BETWEEN PRODUCT AND CALENDER YEAR

 ANS:

 SELECT [Measures].[Internet Sales-Tax Amount] ON 0,

 DISTINCT(

 CROSSJOIN([Product].[Product Name].CHILDREN,[Order Date].[Calendar Year].children)

 )ON 1

 FROM [Analysis Services Tutorial]

 

 Q12: WITHOUT DISPLAYING YEAR DISPLAY PRODUCTS ORDERED IN 2008

Ans:

 SELECT [Measures].[Internet Sales-Tax Amount] ON 0,

 [Product].[Product Name].CHILDREN   ON 1

 FROM [Analysis Services Tutorial]

 WHERE [Order Date].[Calendar Year].[2008]

 

 Q13: DISPLAY SALES AMOUNT BETWEEN THE YEARS 2004 TO 2008 YEAR WISE

ANS :

SELECT [Measures].[Internet Sales-Sales Amount] ON 0,

  [Order Date].[Calendar Date].[2004]:[2008] on 1

FROM [Analysis Services Tutorial]

 

Q14: CALCULATED MEMBER FOR CREATING AGGREGATES

Ans:

WITH

MEMBER XCOUNT AS COUNT([Customer].[Phone].CHILDREN)

SELECT {XCOUNT,[Measures].[Internet Sales-Sales Amount]} ON 0,

[Customer].[Phone] ON 1

FROM [Analysis Services Tutorial]

 

Q15: Display models related to Gloves (Using Custom Functions)

Ans:

SELECT [Measures].[Internet Sales-Sales Amount] ON 0,

 

FILTER([Product].[Model Name].MEMBERS,

VBA!INSTR(1,[Product].[Model Name].CURRENTmember.NAME,'Gloves')>0) ON 1

FROM [Analysis Services Tutorial]

Q16: DISPLAY TOTAL NUMBER OF ROWS INA FACT TABLE

ANS:

SELECT [Measures].[Internet Sales Count] ON 0   FROM  CBN

 

MDX IMPORTANT TIME-FUNCTIONS

PARALLELPERIODS:

The ParallelPeriod function returns a member from a prior period in the same relative position as a supplied member. 

Syntax:  PARALLELPERIOD( [«Level»[, «Numeric Expression»[, «Member»] ] ] )

EX1:

DISPLAY 2 MONTHS PROIR INFO FOR THE MONTH 2006-JUNE IN THE SAME LEVEL.

SELECT

PARALLELPERIOD( [Date].[Calendar Date].[Month Name],2,[Date].[Calendar Date].[Month Name].&[2006]&[June])ON COLUMNS

FROM

[Analysis Services Tutorial]

EX2:

DISPLAY 2 QUARTERS PRIOR INFO FROM THE MONTH OF JUNE-2006

 

SELECT

PARALLELPERIOD([Date].[Calendar Date].[Calendar Quarter] ,2,[Date].[Calendar Date].[Month Name].&[2006]&[June])ON COLUMNS

FROM

[Analysis Services Tutorial]

 

EX3:

DISPLAY 2 YEARS PRIOR INFO FROM THE MONTH OF JUNE-2006

SELECT

      PARALLELPERIOD([Date].[Calendar Date].[Calendar Year] ,2,[Date].[Calendar Date].[Month Name].&[2008]&[June])ON COLUMNS

FROM

[Analysis Services Tutorial]

PeriodsToDate

The PeriodsToDate function returns a set of siblings from the same level as the  Supplied member. The returned set starts with the first sibling and ends with the supplied member.

Syntax:  PERIODSTODATE( [«Level»[, «Member»] ] )

The query below returns the first sibling at the Month level up to the supplied member of July 2005. 

  SELECT

  {[Measures].[Internet Sales-Sales Amount]} ON COLUMNS,

  PeriodsToDate ([Date].[Calendar Date].[Calendar Year], [Date].[Calendar Date].[Month  

 Name].&[2005]&[July]) ON ROWS

FROM

 [Analysis Services Tutorial]

-----
SELECT
    --  {[Measures].[Internet Sales-Sales Amount]} ON COLUMNS,
      PeriodsToDate([Date].[Calendar Date].[Month Name],
   [Date].[Calendar Date].[Date].&[20050725])
   ON columns
FROM
[Analysis Services Tutorial]
---
  SELECT
 -- {[Measures].[Internet Sales-Sales Amount]} ON COLUMNS,
  PeriodsToDate ([Date].[Calendar Date].[Calendar Year],
  [Date].[Calendar Date].[Month Name].&[2005]&[June]) ON columns
FROM
 [Analysis Services Tutorial]
----

 The query below returns the first sibling at the Date level up to the supplied member of [Date].[Calendar Date].[Date].&[20050725]. 

  SELECT

      {[Measures].[Internet Sales-Sales Amount]} ON COLUMNS,

      PeriodsToDate([Date].[Calendar Date].[Month Name],[Date].[Calendar Date].[Date].&[20050725]) ON ROWS

FROM

[Analysis Services Tutorial]

 Closing Period

The ClosingPeriod function returns the last sibling from the descendants of a supplied member at a supplied level. 

Syntax:  CLOSINGPERIOD( [«Level»[, «Member»] ] )

The query below returns the closing period at the Date level for the  hierarchy of the Date dimension. 

  SELECT

      {CLOSINGPERIOD([Date].[Calendar Date].[Date] )}ON COLUMNS

  FROM

  [Analysis Services Tutorial]

 

The following query returns the closing period at the  Quarter level for the hierarchy of the Date dimension. 

  SELECT

     {CLOSINGPERIOD([Date].[Calendar Date].[Calendar Quarter])} ON COLUMNS

  FROM

  [Analysis Services Tutorial]

The query below returns the default measure for the closing period at the Calendar Month level for the member CY 2007.. 

SELECT

      CLOSINGPERIOD ([Date].[Calendar Date].[Month Name],[Date].[Calendar Date].[Calendar Year].&[2007] ) ON COLUMNS

FROM

[Analysis Services Tutorial]

 

 

 

 OpeningPeriod

The OpeningPeriod function returns the first sibling from the descendants of a supplied member at a supplied level.

Syntax:  OPENINGPERIOD( [«Level»[, «Member»] ] )

The query below returns the opening period at the Date level for the  hierarchy of the Date dimension. 

   SELECT

    {OPENINGPERIOD([Date].[Calendar Date].[Date])} ON COLUMNS

  FROM

  [Analysis Services Tutorial]

 The following query returns the opening period at the  Quarter level for the  hierarchy of the Date dimension. 

  SELECT

      {OPENINGPERIOD( [Date].[Calendar Date].[Calendar Quarter]   )} ON COLUMNS

  FROM

  [Analysis Services Tutorial]

  

 

The query below returns the default measure for the opening period at the Calendar Month level for the member CY 2008.. 

  SELECT

      OPENINGPERIOD ([Date].[Calendar Date].[Month Name], [Date].[Calendar Date].[Calendar Year].&[2008]) ON COLUMNS

FROM

 [Analysis Services Tutorial]

YTD

The YTD function returns a set of siblings from the same level as the supplied level. The set consists of the first sibling up to and including the supplied member.

SYN: YTD( [«Member»] )

The query below returns a set of months starting with January 2005 and ending with July 2005. 

  SELECT

      {[Measures].[Internet Sales-Sales Amount]} ON COLUMNS,

      YTD([Date].[Calendar Date].[Month Name].&[2005]&[July]) ON ROWS

FROM

[Analysis Services Tutorial]     

 The query below returns a set of dates starting with January 1, 2005 and ending with January 15, 2005. 

  SELECT

      {[Measures].[Internet Sales-Sales Amount]} ON COLUMNS,

      YTD([Date].[Calendar Date].[Date].&[20050115])ON ROWS

FROM

[Analysis Services Tutorial]

MTD

The MTD function returns all siblings of the supplied member starting at the first sibling and ending with the supplied sibling.

 Syntax:  MTD( [«Member»] )

The query below returns all siblings up to and including the member October 6, 2005 from the  hierarchy in the Date dimension. 

  SELECT

      {[Measures].[Internet Sales-Sales Amount]} ON COLUMNS,

      MTD([Date].[Calendar Date].[Date].&[20051006]) ON ROWS

FROM

 [Analysis Services Tutorial]

QTD

The QTD function returns a set of siblings from the same level as the supplied level. The set consists of the first sibling up to and including the supplied member.

 

Syntax:  QTD( [«Member»] )

The query below returns a set of months starting with the first month in the quarter and ending with supplied month. 

SELECT

      {[Measures].[Internet Sales-Sales Amount]} ON COLUMNS,

      QTD([Date].[Calendar Date].[Month Name].&[2005]&[July])ON ROWS

FROM

[Analysis Services Tutorial]

The query below returns a set of dates starting with the first day of the quarter (July 1, 2005) and ending with supplied date of July 15, 2005. 

  SELECT

      {[Measures].[Internet Sales-Sales Amount]} ON COLUMNS,

      QTD([Date].[Calendar Date].[Date].&[20050715]) ON ROWS

FROM

[Analysis Services Tutorial]

 

WTD

The WTD function returns a set of siblings from the same level as the supplied level. The set consists of the first sibling up to and including the supplied member.

 

Syntax:  WTD( [«Member»] )

This set would start at week 1 and ending with week 1 (JULY 15, 2005). 

SELECT

      {[Measures].[Internet Sales-Sales Amount]} ON COLUMNS,

      WTD([Date].[Calendar Date].[Date].&[20050715] ) ON ROWS

FROM

[Analysis Services Tutorial]

 LastPeriods

The LastPeriods function returns the supplied number of members up to and including the supplied member.

Syntax:  LASTPERIODS( «Index»[, «Member»] )

The query below returns a total of five members. The first four members are before the supplied member of July 2005 in the  hierarchy in the Date dimension. 

 

SELECT

      {[Measures].[Internet Sales-Sales Amount]} ON COLUMNS,

      LastPeriods(5,[Date].[Calendar Date].[Month Name].&[2005]&[July]) ON ROWS

FROM     

 [Analysis Services Tutorial]

The following query returns the first 15 days of January 2005 in the Calendar hierarchy in the Date dimension. 

  SELECT

      {[Measures].[Internet Sales-Sales Amount]} ON COLUMNS,

      LastPeriods(15,[Date].[Calendar Date].[Date].&[20050115]) ON ROWS

FROM

[Analysis Services Tutorial]

Ancestor

The Ancestor function returns a member at the specified level for for the supplied member.

Syntax:  ANCESTOR( «Member», «Level» )

           ANCESTOR( «Member», «Distance» )

The query below returns the Clothing member which is an ancestor at the Product Category level for the member Bearing Ball. 

  SELECT

      {[Measures].[Internet Sales-Sales Amount]} ON COLUMNS,

      {ANCESTOR( [Product].[Product Categories].[Product Name].&[2], [Product].[Product Categories].[Category])} ON ROWS

FROM

[Analysis Services Tutorial] 

This query uses the distance (numeric expression) to return the parent (one level up) of Bearing Ball. 

  SELECT

      {[Measures].[Internet Sales-Sales Amount]} ON COLUMNS,

      {ANCESTOR([Product].[Product Categories].[Product Name].&[2] , 1)} ON ROWS

FROM

[Analysis Services Tutorial]

The query below uses the distance (numeric expression) to return the set two levels up from the Product member Bearing Ball.. 

  SELECT

      {[Measures].[Internet Sales-Sales Amount]} ON COLUMNS,

      {ANCESTOR([Product].[Product Categories].[Product Name].&[2],2)} ON ROWS

FROM

[Analysis Services Tutorial] 

This query below returns members at the Category, SubCategory and Product levels for the supplied member. 

  SELECT

      {[Measures].[Internet Sales-Sales Amount]} ON COLUMNS,

      {ANCESTOR([Product].[Product Categories].[Product Name].&[2],2),

            ANCESTOR([Product].[Product Categories].[Product Name].&[2],1),

            ANCESTOR([Product].[Product Categories].[Product Name].&[2],0)} ON ROWS

FROM

[Analysis Services Tutorial]

RANK

The Rank function returns the one-based rank of the supplied tuple in the supplied set. 

Syntax:  RANK( «Tuple», «Set» )

The query below returns the Rank of each Month member within the CY 2009 member without regard to a numeric expression. 

  WITH

      MEMBER [Measures].[DateRank] AS

      RANK([Date].[Calendar Date].CurrentMember,

                   DESCENDANTS([Date].[Calendar Date].[Calendar Year].&[2009], [Date].[Calendar Date].[Month Name]))

SELECT

      {[Measures].[DateRank]} ON COLUMNS,

      DESCENDANTS([Date].[Calendar Date].[Calendar Year].&[2009], [Date].[Calendar Date].[Month Name]) ON ROWS

FROM

[Analysis Services Tutorial]

 

 

The following query returns the Rank of each Month member within the CY 2009 member according to the measure Order Count. 

  WITH

      MEMBER [Measures].[DateRank] AS

      RANK([Date].[Calendar Date].CurrentMember,

            DESCENDANTS([Date].[Calendar Date].[Calendar Year].&[2009], [Date].[Calendar Date].[Month Name]), [Measures].[Internet Sales Count])

SELECT

      {[Measures].[DateRank],[Measures].[Internet Sales Count] } ON COLUMNS,

      DESCENDANTS([Date].[Calendar Date].[Calendar Year].&[2009], [Date].[Calendar Date].[Month Name]) ON ROWS

FROM

[Analysis Services Tutorial]

 

SCOPE STATEMENT

In Microsoft SQL Server Analysis Services, a Multidimensional Expressions (MDX) script can apply to the entire cube, or to specific portions of the cube, at specific points within the execution of the script. The MDX script can take a layered approach to calculations within a cube through the use of calculation passes.

 

 

To control the calculation pass, scope, and context within an MDX script, you specifically use the CACULATE statement, the This function, and the SCOPE statement.

Using the CALCULATE Statement

If the script contains a SCOPE statement that contains a CALCULATE statement, MDX evaluates the CALCULATE statement within the context of the subcube defined by the SCOPE statement, not against the whole cube.

The CALCULATE statement populates each cell in the cube with aggregated data. For example, the default MDX script has a single CALCULATE statement at the beginning of the script.

If the script contains a SCOPE statement that contains a CALCULATE statement, MDX evaluates the CALCULATE statement within the context of the subcube defined by the SCOPE statement, not against the whole cube.

Using the This Function

If the script contains a SCOPE statement that contains a This function, MDX evaluates the This function within the context of the subcube defined by the SCOPE statement, not against the whole cube.

 

The following MDX script command example uses the This function to increase the value of the Amount measure, in the Finance measure group of the Adventure Works DW Multidimensional 2012 sample cube, to 10% higher for the children of the Redmond member in the Customer dimension:

 

/* This SCOPE statement defines the current subcube */

SCOPE([Customer].&[Redmond].MEMBERS,

    [Measures].[Amount], *);

        /* This expression sets the value of the Amount measure */

        THIS = [Measures].[Amount] * 1.1;

END SCOPE

 

The This function lets you retrieve the current subcube within an MDX script. You can use the This function to quickly set the value of cells within the current subcube to an MDX expression. You often use the This function in conjunction with the SCOPE statement to change the contents of a specific subcube during a specific calculation pass

 

SCOPE DEFINITION

The SCOPE statement defines the current subcube that contains, and specifies the scope of, other MDX expressions and statements within an MDX script. MDX evaluates this other MDX expressions and statements, including the This function and the CALCULATE statement, within the context of the subcube.

 

A SCOPE statement is dynamic, but not iterative in nature. The statements contained within a SCOPE statement run once, but the subcube itself can be dynamically determined. For example, you have a cube named SampleCube. Against the SampleCube cube, you apply the following SCOPE statement to define a subcube the defines the context as the ALLMEMBERS within the Measures dimension:

 

SC1:

PLEASE SPECIFY THE BELOW IN THE CALCULATE TAB.

SCOPE([Measures].ALLMEMBERS);

 THIS = [Measures].ALLMEMBERS.COUNT;

END SCOPE;

 

SC2:

SCOPE([Measures].ALLMEMBERS);

 THIS = 300;

END SCOPE;

BUILD->DEPLOY

SSMS--> NEW QUERY-->

 

 

WITH MEMBER [Measures].[NewMeasure] AS '1'

SELECT

 [Measures].ALLMEMBERS ON COLUMNS,

[Customer].[City] .DEFAULTMEMBER ON ROWS

FROM

 [Analysis Services Tutorial]

 

SCOPE STATEMENT ANOTHER EXAMPLE:

The following MDX script example uses a SCOPE statement to sets the value of the Amount measure, in the Finance measure group of the Adventure Works DW Multidimensional 2012 sample cube, to 10% higher for the children of the Redmond member in the Customer dimension. However, another SCOPE statement changes the subcube to include the Amount measure for the children of the 2002 calendar year. Finally, the Amount measure is then aggregated only for that subcube, leaving the aggregated values for the Amount measure in other calendar years unchanged.

 

/* Calculate the entire cube first. */

CALCULATE;

/* This SCOPE statement defines the current subcube */

SCOPE([Customer].&[Redmond].MEMBERS,

    [Measures].[Amount], *);

        /* This expression sets the value of the Amount measure */

        THIS = [Measures].[Amount] * 1.1;

END SCOPE;

CREATE SUBCUBE Statement (MDX)

Redefines the cube space of a specified cube or subcube to a specified subcube. This statement changes the apparent cube space for subsequent operations.

 

SYN:

CREATE SUBCUBE Cube_Name AS Select_Statement

                                                  | NON VISUAL ( Select_Statement )

Session objects created in the context of a subcube are dropped when the subcube is dropped.

Example

The following example creates a subcube that restricts the apparent cube space to members that exist with the country of Canada. It then uses the MEMBERS function to return all members of the Country level of the Geography user-defined hierarchy - returning only the country of Canada.

CREATE SUBCUBE [Analysis Services Tutorial] AS

   SELECT [Customer].[Country-Region].&[Canada] ON 0

   FROM [Analysis Services Tutorial]

 

  SELECT [Measures].[Internet Sales-Sales Amount] ON 0,

  Customer].[Country-Region].[Country-Region].MEMBERS ON 1

  FROM [Analysis Services Tutorial]

====================================================================================