Query Issue

Jul 15, 2013 at 2:37 PM
Dear,

I've tried the version 2.5 of the Pivot Table last week.

I found a difference between the query on the pivot table and the one on SSMS browser.

In SSMS the query is:

SELECT
            NON EMPTY HIERARCHIZE(VISUALTOTALS(SUBSET([Time].[Time].Levels(0).Members, 0, 10000), '* - Total**')) 

            DIMENSION PROPERTIES PARENT_UNIQUE_NAME,  HIERARCHY_UNIQUE_NAME, CUSTOM_ROLLUP, UNARY_OPERATOR, KEY0 ON 0, 

            NON EMPTY HIERARCHIZE(
                            SUBSET(
                                            CrossJoin([Segment].[Segment].Levels(0).ALL, 
                                            [Channel].[Channel].Levels(0).Members), 0, 10000
                                            )
                            ) 
                            DIMENSION PROPERTIES PARENT_UNIQUE_NAME, HIERARCHY_UNIQUE_NAME, CUSTOM_ROLLUP, UNARY_OPERATOR, KEY0 ON 1
            FROM 
[XXX]
WHERE ([Measures].[Survey Qty])
CELL PROPERTIES BACK_COLOR, CELL_ORDINAL, FORE_COLOR, FONT_NAME, FONT_SIZE, FONT_FLAGS, FORMAT_STRING, VALUE, FORMATTED_VALUE, UPDATEABLE, ACTION_TYPE

In rannet the query is:

SELECT
NON EMPTY ( [Time].[Time].Levels ( 0 ).Members )
DIMENSION PROPERTIES PARENT_UNIQUE_NAME , HIERARCHY_UNIQUE_NAME , CUSTOM_ROLLUP , UNARY_OPERATOR , KEY0 ON 0
, NON EMPTY
( CrossJoin
( { { [Segment].[Segment].[Segment].&[Franquicia] } }
, { { { [Channel].[Channel].[Subchannel].&[39] }
    , { [Channel].[Channel].[Subchannel].&[40] }
    , { [Channel].[Channel].[Subchannel].&[42] }
    , { [Channel].[Channel].[Subchannel].&[19] }
    , { [Channel].[Channel].[Subchannel].&[44] }
    , { [Channel].[Channel].[Subchannel].&[41] }
    , { [Channel].[Channel].[Subchannel].&[43] }
    }
  }
)
)
DIMENSION PROPERTIES PARENT_UNIQUE_NAME , HIERARCHY_UNIQUE_NAME , CUSTOM_ROLLUP , UNARY_OPERATOR , KEY0 ON 1
FROM [XXX]
WHERE [Measures].[Survey Qty]
CELL PROPERTIES BACK_COLOR , CELL_ORDINAL , FORE_COLOR , FONT_NAME , FONT_SIZE , FONT_FLAGS , FORMAT_STRING , VALUE , FORMATTED_VALUE , UPDATEABLE

Due this query difference, there are some cases where the data retrieved by Rannet has differences w/ SSMS.

Could you inform me the reason of this difference?

Thanks,
Leandro
Coordinator
Jul 15, 2013 at 4:50 PM
Edited Jul 16, 2013 at 12:10 PM
You do not need to use the SUBSET().
SUBSET can return value is NULL. NON EMPTY exclude the value is NULL.
In Ranet OLAP don't use the setting [The maximum number of tuptes to be returned] Set its value to 0.

You have different MDX queries. How can you compare their results?