How to create subcube in analysis service

It restricts cubes space to selected member in the select expression for create subcube statement


CREATE SUBCUBE


select statement or non visual (select statement)

• In select expression you can not use non empty, having clauses, dimension or cell properties.

• subcube name should be cube name or perspective name.

When we define subcube without non visual member then total displays only aggregation of selected members


CREATE SUBCUBE [Adventure Works]
AS
SELECT [Date].[Calendar].[Calendar Year].&[2003] ON 0
FROM [Adventure Works]



SELECT [Measures].[Internet Sales Amount] ON 0
FROM [Adventure Works]



DROP SUBCUBE [Adventure Works]

Now when we define subcube with non visual clause then total will be aggregation of all members


CREATE SUBCUBE [Adventure Works]
AS
NON VISUAL (SELECT [Date].[Calendar].[Calendar Year].&[2003] ON 0
FROM [Adventure Works])


SELECT [Measures].[Internet Sales Amount] ON 0
FROM [Adventure Works]



drop subcube [Adventure Works]