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.

adventureworks cube

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]


Popular posts from this blog