Don’t know how I’ve not managed to talk about this one, as it’s one of the biggest new additions to MDX. Basically what this functionality means is that instead of putting a cube or a perspective name in the FROM clause you can put a SELECT statement which defines a subcube instead, similar to a SQL subselect. So what is a subcube? It’s a subset of the cube you’re eventually going to query, sliced by members on one or more dimensions. Think of the effect as being the same as if you had dimension security on your cube, only allowing you to see certain members on some dimensions.
For example with the Adventure Works cube, say you were interested in analysing the combined sales of the Accessories and Clothing product categories, you could run the following query:
select [Measures].[Internet Sales Amount] on 0,
[Date].[Day Name].members
on 1
from (
select ({[Product].[Category].[Category].&[4], [Product].[Category].[Category].&[3]})
on 0 from [Adventure Works])
In this example, what we’re saying in this query is: find me the Internet Sales Amount for each day of the week, from a version of the Adventure Works cube which only has the Accessories and Clothing members on the Product Category hierarchy. Note that for this example we could also use a set in the WHERE clause to get the same values, as follows:
select [Measures].[Internet Sales Amount] on 0,
[Date].[Day Name].members
on 1
from [Adventure Works]
where({[Product].[Category].[Category].&[4], [Product].[Category].[Category].&[3]})
However, there is a big difference between the two approaches. When you put the Product Category hierarchy in the WHERE clause it can’t appear on any other axis, but this isn’t true of the subcube approach as the following example shows:
select [Measures].[Internet Sales Amount] on 0,
[Date].[Day Name].members
*
[Product].[Category].[Category].members
on 1
from (
select ({[Product].[Category].[Category].&[4], [Product].[Category].[Category].&[3]})
on 0 from [Adventure Works])
The thing to notice is that [Product].[Category].[Category].members now only returns the Accessories and Clothing members, and not the Bikes or Components members which are present in the real Adventure Works cubes. It’s the structure of the cube itself which has been altered.
You’re not restricted to one dimension in your subcube definition, of course, as the following example shows:
select [Measures].[Internet Sales Amount] on 0,
[Date].[Day Name].members
on 1
from (
select (
{[Product].[Category].[Category].&[4], [Product].[Category].[Category].&[3]}
,{[Date].[Calendar Year].[Calendar Year].&[2004], [Date].[Calendar Year].[Calendar Year].&[2003]}
)
on 0 from [Adventure Works])
and you can even do quite sophisticated things like:
select [Measures].[Internet Sales Amount] on 0,
[Product].[Category].[Category].members
*
[Date].[Calendar Year].[Calendar Year].members
on 1
from (
select (
{([Product].[Category].[Category].&[4],[Date].[Calendar Year].[Calendar Year].&[2004])
, ([Product].[Category].[Category].&[3], [Date].[Calendar Year].[Calendar Year].&[2003])
})
on 0 from [Adventure Works])
…which shows that you’re not restricted to a simple product of sets of members from various hierarchies. You can also do nested subselects, which make it easy to express otherwise complex multi-step filtering operations. For example:
select [Measures].[Internet Sales Amount] on 0,
[Date].[Day Name].members
on 1
from (
select (topcount([Customer].[Country].[Country].members, 2, [Measures].[Internet Sales Amount]))
on 0 from (
select(
{[Product].[Category].[Category].&[4], [Product].[Category].[Category].&[3]})
on 0 from [Adventure Works]))
…gives me the internet sales amount by day of week for the top two countries for the Accessories and Clothing product categories. This example doesn’t do anything you couldn’t do in a single subselect, but it’s easier to understand; I assume that there would be a performance overhead with doing this kind of nesting though.
Finally, one other point of interest is that calculated members have the ability to look ‘outside’ the subcube. For example:
with member measures.test as [Product].[Category].currentmember.prevmember.name
select [Measures].test on 0,
[Product].[Category].[Category].members
on 1
from (
select ({[Product].[Category].[Category].&[4], [Product].[Category].[Category].&[3]})
on 0 from [Adventure Works])
…shows how measures.test, for Clothing, returns the string ‘Bikes’ even though the Bikes member isn’t in the subcube. This is necessary I suppose for calcs like previous period growth, which would be pretty useless if you put non-contiguous time periods in your subcube.
So, finally, what is all this useful for? Lots of things I’m sure, but the most obvious use to me is as a better way of handling VisualTotals type queries – if you think about how Excel pivot tables work and the MDX they generate, this seems to be a much better way of achieving the same result. I definitely need to research this in much more detail…
Well… I definitely have some big problems making my calculated members look outside a subcube. See http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=527962&SiteID=1 for an example with code from the AW cube database.
Hi Chris,
would my below MDX calculated member look ‘outside’ of the subcube? it appears to be and it has been causing problems for me:
CREATE subcube [MyCube] AS
SELECT
{[Station Date].[Date].&[20130301] : [Station Date].[Date].&[20130307] }
ON COLUMNS
,{[Local Date].[Date].&[20130228] : [Local Date].[Date].&[20130307]} –gives the weird result 711.27, which is the double of the whole date sets avg
ON ROWS
FROM [MyCube]
GO
WITH
MEMBER [Measures].[AVG weight – test]
as
IIF(IsEmpty([Measures].[Weight]),NULL,
AVG(
–[Station Date].[Date].[Date].Members * [Local Date].[Date].[Date].Members — it average across the whole data set, i.e. result is 355.64
–[Station Date].[Date].[Date].Members * [Local Date].[Date].currentmember — it gives the 711.27 weird result, which is the double of the whole date sets avg
–[Station Date].[Date].[Date].Members * [Local Date].[Date].[All] — it gives the 711.27 weird result, which is the double of the whole date sets avg
[Station Date].[Date].[Date].Members– it gives the 664.01 weird result if subcube contains 8 days local dates: [Local Date].[Date].&[20130228] : [Local Date].[Date].&[20130307]; and values changes as you change these dates
,[Measures].[Weight]
) –AVG
)–IFF
,format_string=”#,##0.00″
SELECT {
[Measures].[Weight] –just the weight with SUM being the Aggregate function
,[Measures].[AVG weight – test]
} ON 0
,{[Station Date].[Date].[Date].Members}
–,non empty {[Station Date].[Date].[Date].Members} *{ [Local Date].[Date].[Date].Members} — reveal some reasons for weird values
–,{[Station Date].[Date].[Date].Members} *{[Local Date].[Date].currentmember} –reveal what is happening with local dates
on 1
FROM [MyCube]
WHERE (
[Household].[Household ID].&[9831] –need to see 373.50 for one household
)
GO
DROP SUBCUBE [MyCube]
GO
All I am trying to do is to get an Average of a measure [Weight] across different dates pair, and the dates are supposed to be restricted by the subcube.
My extracted underlying table which supports the measure is: and I am expecting to see the calculated member “[Measures].[AVG weight – test]” to return the value 373.60 on each row.
HouseholdID StationDateID LocalDateID Weight
9831 20130301 20130228 388.5732
9831 20130301 20130301 388.5732
9831 20130302 20130301 385.6567
9831 20130302 20130302 385.6567
9831 20130303 20130302 380.3481
9831 20130303 20130303 380.3481
9831 20130304 20130303 375.8885
9831 20130304 20130304 375.8885
9831 20130305 20130304 371.5434
9831 20130305 20130305 371.5434
9831 20130306 20130305 357.8331
9831 20130306 20130306 357.8331
9831 20130307 20130306 355.368
9831 20130307 20130307 355.368
Am I doing something wrong here?
This measure will be used in another calculated measure [measure2], and I do not want it to change with [Station date] which appear on the rows, so another way I was trying to do is to make the measure2 to use [Station Date] [All] member, and of course the [All] member is the [All] for the whole cube instead of the subCube.
Your comments will be greatly appreciated.
Calculated members always look outside the subcube (and the same thing happens for subselects too) – that’s just the way they work, and there’s nothing you can do about it. You will have to rewrite your query to filter your data in a different way.
Hi Chris,
Thanks for this.
Yes, in the end, I actually accidently, successfully managed to make the “[Station Date].[Date].[Date].Members” to be dynamic set like Mosha suggested http://sqlblog.com/blogs/mosha/archive/2007/08/25/mdx-in-katmai-dynamic-named-sets.aspx
This has been driving *me* crazy, and this post is at least helpful in confirming the behavior, if not how to work around it. In my case I want to define a calculated member which acts as an indicator of whether any attribute in a dimension is not at the All level – i.e. is being used to slice in some way – because in such case certain calculations are invalid. I am able easily to achieve this writing my own query – not involving a sub-cube – but the same calc measure thus developed malfunctions when the FROM clause is a sub-cube, as it is from Excel or Management Studio. It malfunctions because of this “looking outside the sub-cube” behavior. As I don’t have control over the query, only the calc measure, I can’t figure out how to solve this – or indeed whether it is solvable. If anyone has any ideas I’d be grateful. I can provide more details but didn’t think it necessary to understand the basic issue.
It sounds like you are trying to solve the problem in the wrong way – maybe scoped assignments below the All level are a better way to go?
Quite right. I should have thought of that. I have it working great now.
If we need to pre-filter the hierarchy members – but based on a dynamic condition – say which is driven by user input – how would we use the sub-select ? I tried using a Where clause inside the sub-select query – but the outer query (which contains the main and more complex computation) still runs across the entire set of members of the hierarchy – which was pre-filtered in the sub-select ? Is there a different and more elegant solution here ?