Here are some other good references concerning sub-select and slicer debate:
http://bisherryli.com/2013/02/08/mdx-25-slicer-or-sub-cube/
https://cwebbbi.wordpress.com/2014/04/07/free-video-on-subselects-in-mdx/
Chris Webb's video being located here:
https://projectbotticelli.com/knowledge/what-is-a-subselect-mdx-video-tutorial?pk_campaign=tt2014cwb
This should still leave an All
member:
SELECT
[measures].[sales] ON 0
FROM
(
SELECT
FROM [MyCube]
WHERE
[store].[store].[042]
);
...but the member [All]
of the Store hierarchy will only now be made up of [store].[store].[042]
.
You can see this by adding the Store hierarchy onto ROWS
:
SELECT
[measures].[sales] ON 0,
[store].MEMBERS ON 1
FROM
(
SELECT
FROM [MyCube]
WHERE
[store].[store].[042]
);
This is the AdvWorks
version similar to the reference in your question:
SELECT
{[Measures].[Order Count]} ON 0
,[Subcategory].MEMBERS ON 1
FROM
(
SELECT
{
[Subcategory].[Subcategory].&[22]
} ON 0
FROM [Adventure Works]
);
It returns the member from the sub-select and the All
member adjusted to take account of the subselect:
In the references article why is the [All]
less than the sum of the other two - this is not down to the subselect but is in connection with the measure that he has chosen [Measures].[Order Count]
which is a distinct count. If you take away the subselect you see exactly the same behaviour of the All member being less than the sum of the other subcategory members (I've marked the point at which the total of the parts becomes higher than the All member):
SELECT
{[Measures].[Order Count]} ON 0
,Order
(
[Subcategory].MEMBERS
,[Measures].[Order Count]
,bdesc
) ON 1
FROM [Adventure Works];
Order Count: on 1 order there might be several Product Subcategories - hence this behaviour.
Edit
This query of yours:
select
[measures].[sales] on 0
from (select
[store].[state].members on 0
from TestCube //<< added this!
where [store].[store].[042]
)
This inner script is not valid? Using the same dimension on an axes and the WHERE clause is not valid:
select
[store].[state].members on 0
from TestCube
where [store].[store].[042]
Edit2
An mdx
script returns a cube, which may be sliced or not sliced, but nevertheless it returns a cube. The WHERE
clause is used to slice the cube that is returned. If we were using a third party tool then the dimension added to the WHERE
clause would go into a combobox - with say Cliffside
selected. BUT the user could effectively select Ballard
from that combobox - it is just a slicer. The WHERE
clause is not changing the cube that is returned by the mdx
script, it is just affecting what is displayed in the cellset
.
WHERE
is valid within a subselect. It is part of the definition:
https://msdn.microsoft.com/en-us/library/ff487138.aspx
I've never found a use case for a subselect's WHERE
clause.
Edit3
This link will explain things:
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/ccb66ac3-0f9a-4261-8ccc-b6ecc51b6f07/is-where-clause-pointless-inside-a-subselect?forum=sqlanalysisservices
As Darren gosbell says in the answer to this question:
https://msdn.microsoft.com/en-us/library/ff487138.aspx it says that:
The WHERE clause does not filter the subspace.
(SELECT blah ON 0, foo ON 1, bar ON 2 ..... FROM cube)
with as many axes as is required. I've never played with theWHERE
clause - what doesmsdn
say about this? – whytheq