SubCube adhoc behaviour on MSSQL 2005 AS

[kayyum_khan]

When I execute the MDX Query it gives me proper Result.

Later with Same MDX Query I create the Sub Cube and then again execute the same MDX Query on the SubCube it gives me some other result which doesnot contain certain members which are there in SubCube



Output of Query on wholeCube(image1.gif)
Output of Same Query used for creating the SubCube (image2.gif)
Output of Same Query Query on SubCube(image_3.gif)
Query without "WITH"
1)

CREATE SUBCUBE KAYYUM_PVA AS SELECT {[Measures].[SPEND_INVOICE_LINE_AMT]} ON 0,{ GENERATE ( { GENERATE ( { CROSSJOIN ( { EXCEPT ( { DRILLDOWNMEMBER ( { HIERARCHIZE ( { [UN_DIM].[All UN_DIM].[Distribution and Conditioning Systems and Equipment and Components], [UN_DIM].[All UN_DIM].[Manufacturing Components and Supplies]} )}, { [UN_DIM].[All UN_DIM].[Distribution and Conditioning Systems and Equipment and Components]} )}, { [UN_DIM].[All UN_DIM].[Distribution and Conditioning Systems and Equipment and Components]} )}, { GENERATE ( { [TIME_DIM].MEMBERS}, { Descendants([TIME_DIM].CurrentMember,[TIME_DIM].Levels(1)), Ancestor([TIME_DIM].CurrentMember,[TIME_DIM].Levels(1))} )} )}, { StrToSet(IIf([UN_DIM].CurrentMember IS [UN_DIM].[All UN_DIM].[Distribution and Conditioning Systems and Equipment and Components].[Fluid and gas distribution] AND [TIME_DIM].CurrentMember IS [TIME_DIM].[All TIME_DIM].[2004] , SetToStr({ CROSSJOIN ( { ( [UN_DIM].CurrentMember)}, { Descendants([TIME_DIM].[All TIME_DIM].[2004],[TIME_DIM].Levels(2),SELF)} )}) , SetToStr({ ( [UN_DIM].CurrentMember, [TIME_DIM].CurrentMember)})))} )}, { StrToSet(IIf([UN_DIM].CurrentMember IS [UN_DIM].[All UN_DIM].[Distribution and Conditioning Systems and Equipment and Components].[Fluid and gas distribution] AND [TIME_DIM].CurrentMember IS [TIME_DIM].[All TIME_DIM].[2004].[Q2 04] , SetToStr({ CROSSJOIN ( { ( [UN_DIM].CurrentMember)}, { Descendants([TIME_DIM].[All TIME_DIM].[2004].[Q2 04],[TIME_DIM].Levels(3),SELF)} )}) , SetToStr({ ( [UN_DIM].CurrentMember, [TIME_DIM].CurrentMember)})))} )}ON 1 FROM KAYYUM_PVA


Query with "WITH"

WITH

SET [DerivedSet0] AS '{ [MEASURES].[SPEND_INVOICE_LINE_AMT]}'

SET [DerivedSet1] AS '{ HIERARCHIZE ( { [UN_DIM].[All UN_DIM].[Distribution and Conditioning Systems and Equipment and Components], [UN_DIM].[All UN_DIM].[Manufacturing Components and Supplies]} )}'

SET [DerivedSet2] AS '{EXCEPT( {DRILLDOWNMEMBER ( { [DerivedSet1]}, { [UN_DIM].[All UN_DIM].[Distribution and Conditioning Systems and Equipment and Components]} )},{ [UN_DIM].[All UN_DIM].[Distribution and Conditioning Systems and Equipment and Components]})}'

SET [DerivedSet5] AS '{ HIERARCHIZE ( { [DerivedSet2]} )}'

SET [DerivedSet6] AS '{ [TIME_DIM].MEMBERS}'

SET [DerivedSet7] AS '{ GENERATE ( { [DerivedSet6]}, { Descendants([TIME_DIM].CurrentMember,[TIME_DIM].Levels(1)), Ancestor([TIME_DIM].CurrentMember,[TIME_DIM].Levels(1))} )}'

SET [DerivedSet11] AS '{ HIERARCHIZE ( { [DerivedSet7]} )}'

SET [DerivedSet12] AS '{ CROSSJOIN ( { [DerivedSet5]}, { [DerivedSet11]} )}'

SET [DerivedSet13] AS '{ GENERATE ( { [DerivedSet12]}, { StrToSet(IIf([UN_DIM].CurrentMember IS [UN_DIM].[All UN_DIM].[Distribution and Conditioning Systems and Equipment and Components].[Fluid and gas distribution] AND [TIME_DIM].CurrentMember IS [TIME_DIM].[All TIME_DIM].[2004] , SetToStr({ CROSSJOIN ( { ( [UN_DIM].CurrentMember)}, { Descendants([TIME_DIM].[All TIME_DIM].[2004],[TIME_DIM].Levels(2),SELF)} )}) , SetToStr({ ( [UN_DIM].CurrentMember, [TIME_DIM].CurrentMember)})))} )}'

SET [DerivedSet14] AS '{ GENERATE ( { [DerivedSet13]}, { StrToSet(IIf([UN_DIM].CurrentMember IS [UN_DIM].[All UN_DIM].[Distribution and Conditioning Systems and Equipment and Components].[Fluid and gas distribution] AND [TIME_DIM].CurrentMember IS [TIME_DIM].[All TIME_DIM].[2004].[Q2 04] , SetToStr({ CROSSJOIN ( { ( [UN_DIM].CurrentMember)}, { Descendants([TIME_DIM].[All TIME_DIM].[2004].[Q2 04],[TIME_DIM].Levels(3),SELF)} )}) , SetToStr({ ( [UN_DIM].CurrentMember, [TIME_DIM].CurrentMember)})))} )}'

SELECT NON EMPTY { [DerivedSet0]} ON COLUMNS , NON EMPTY { [DerivedSet14]} ON ROWS FROM KAYYUM_PVA

Thanx in Advance