ssas - MDX where clause in subquery does not slice cube - how to understand? -
this query gives me sales of 1 store:
select [measures].[sales] on 0 [mycube] [store].[store].[042] however, if move slicer inside of subquery, gives me sales of stores.
select [measures].[sales] on 0 (select [mycube] [store].[store].[042] ) how understand mechanisms behind difference? noted in this article, without explanation.
----edit----:
i tried various things , read around while. i'd add question: there scenario in clause in sub-select filter result?
this query gives me sales of stores in state mi (store [042] belongs mi):
select [measures].[sales] on 0 (select [store].[state].[mi] on 0 [mycube] [store].[store].[042] ) thinking of 'inner query filters if filtered dimension returned on axis', theory proved wrong if this:
select [measures].[sales] on 0 (select [store].[state].members on 0 [mycube] [store].[store].[042] ) the sub-select still returns 1 state mi, outer query returns sales of stores (of states).
----edit 4/13----:
re-phrasing question in adventureworks cube screenshot. query 1: sales of 1 store query 2: returns sales of stores if clause in sub-select. query 3: 2 answers got suggested select dimension in axis - here result - cities.
select [measures].[sales] on 0 (select [mycube] [store].[store].[042] ) the above query reduces scope of stores member [042]. make note sub-select executed before actual select. so, when comes select, engine sees cube has members in dimensions; member [store].[store].[042] in store dimension. it's if cube has been kept intact every else sliced off on store dimension.
if go step ahead , add store on 1 of axes, like
select [measures].[sales] on 0, [store].[store].members on 1 (select [mycube] [store].[store].[042] ) you see although member [all] appears in output, comprised of 1 store.
in essence, [all] special member calculated respect scope of cube. reflects combined effect of members in cube.
in sql terms, similar to:
select sales, store [all] (select sales, store tbl store = '042') tbl even though see sales----all, reflection of sales store [042]