Chris Webb's BI Blog

Analysis Services, MDX, PowerPivot, DAX and anything BI-related

Subcubes in the FROM clause

with 4 comments

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…

Written by Chris Webb

May 6, 2005 at 2:00 pm

Posted in MDX

4 Responses

Subscribe to comments with RSS.

  1. 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.

    Michael

    July 10, 2006 at 7:27 pm

  2. 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.

    Min

    November 20, 2013 at 1:20 am

    • 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.

      Chris Webb

      November 20, 2013 at 8:07 am


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 3,072 other followers

%d bloggers like this: