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…
