none
MDX to aggregate measure over specific dimensions

    Question

  • Hi,

    I'm trying to write a calculated member in SSAS 2005 that will only aggregate across certain dimensions. For example, say I have five dimensions: D1 - 5. I only want the member to aggregate across three of these dimensions. So in the cube browser, when I drag these three dimensions in, I get the correct aggregated value. But when I then drag dimensions four and five in, I want this value to stay the same. (The measure is currently in a measure group that uses all five dimensions).

    I was thinking that the solution would be to have an MDX expression of the form

    ([Measures].[Measure],
     [D1].CurrentMember,
     [D2].CurrentMember,
     [D3].CurrentMember,
     [D4].[(All)],
     [D5].[(All)])

    but I would prefer not to have to list all dimensions and their hierarchies, and have to remember to add to this list if I add a dimension in the future. In SSAS 2000 I had a lookup cube that only contained the dimensions I wanted to slice by.

    My other solution was to create a named query on the fact table that this measure is currently in, create a measure group on that, and then set up the dimension usage so that only the dimensions I want to slice by are referenced. However, I'm thinking that there must be a better solution, probably using some MDX that I don't know about!

    Thanks in advance.
    James

    Thursday, February 08, 2007 4:00 PM

Answers

  • Take a look at the MDX Root function. Here's an example of something that might work for you using Adventure Works:

    with member measures.rootdemo as (
    root([Date].[Calendar Year].currentmember),
    root([Product].[Category].currentmember),
    root([Customer]),
    [Measures].[Internet Sales Amount]
    )
    select
    [Date].[Calendar Year].[Calendar Year].members
    *
    [Date].[Calendar Semester of Year].[Calendar Semester of Year].members
    *
    [Customer].[Country].[Country].members
    on
    0,
    [Product].[Category].[Category].members
    on
    1
    from [Adventure Works]
    where(measures.rootdemo)

    You still need to list all the dimensions but at least you don't need to list the hierarchies. Watch out for this 'feature' of the function, though, which occurs when more than one member from a hierarchy is in scope:

    with member measures.rootdemo as (
    root([Date].[Calendar Year].currentmember),
    root([Product].[Category].currentmember),
    root([Customer]),
    [Measures].[Internet Sales Amount]
    )
    select
    [Date].[Calendar Year].[Calendar Year].members
    *
    [Date].[Calendar Semester of Year].[Calendar Semester of Year].members
    on
    0,
    [Product].[Category].[Category].members
    on
    1
    from [Adventure Works]
    where(measures.rootdemo,{[Customer].[Country].&[Australia],[Customer].[Country].&[United Kingdom]})

    HTH,

    Chris

    Thursday, February 08, 2007 4:54 PM
    Moderator

All replies

  • Take a look at the MDX Root function. Here's an example of something that might work for you using Adventure Works:

    with member measures.rootdemo as (
    root([Date].[Calendar Year].currentmember),
    root([Product].[Category].currentmember),
    root([Customer]),
    [Measures].[Internet Sales Amount]
    )
    select
    [Date].[Calendar Year].[Calendar Year].members
    *
    [Date].[Calendar Semester of Year].[Calendar Semester of Year].members
    *
    [Customer].[Country].[Country].members
    on
    0,
    [Product].[Category].[Category].members
    on
    1
    from [Adventure Works]
    where(measures.rootdemo)

    You still need to list all the dimensions but at least you don't need to list the hierarchies. Watch out for this 'feature' of the function, though, which occurs when more than one member from a hierarchy is in scope:

    with member measures.rootdemo as (
    root([Date].[Calendar Year].currentmember),
    root([Product].[Category].currentmember),
    root([Customer]),
    [Measures].[Internet Sales Amount]
    )
    select
    [Date].[Calendar Year].[Calendar Year].members
    *
    [Date].[Calendar Semester of Year].[Calendar Semester of Year].members
    on
    0,
    [Product].[Category].[Category].members
    on
    1
    from [Adventure Works]
    where(measures.rootdemo,{[Customer].[Country].&[Australia],[Customer].[Country].&[United Kingdom]})

    HTH,

    Chris

    Thursday, February 08, 2007 4:54 PM
    Moderator

  • Hi Chris, many thanks for your reply.

    This indeed worked. Going back to my previous example, I created a calculated member as:

    (Root([D4]),
     Root([D5]),
     [Measures].[Measure])

    and the measure is only sliced by dimensions D1, D2 and D3.

    Thanks for your help!

    James
    Thursday, February 08, 2007 6:50 PM
  • Another approach to this problem is to put these measures into dedicated measure group which excludes dimensions D4 and D5 - then you will get the aggregates you need without calculated members. Of course, if you sometimes do need detailed information over them, then the approach with calculated member is the right one.

    One more note - you don't have to use Root() function if all the attributes in your dimensions are aggregatable. You will get better performance if you simply use

    ([D4].[All], [D5].[All], [Measures].[Measure])

    Thursday, February 08, 2007 7:35 PM

  • Hi, yes I thought those were my two options.

    I don't want to create another measure group as I'll have duplicate measures and the table with this measure in is large (it's a requirement in our system to keep processing time to a minimum). I was hoping that there would be a solution where I didn't have to list every dimension I wanted to remove from the slice (and remember to add to the list if I add dimensions in the future), but at least I have a solution!

    Many thanks for your help.
    James


    Thursday, February 08, 2007 8:19 PM
  • Hi ,

    I have a situation ,I have one of the measures which should not be aggregated up the geography Dimension
    ex: [measures].[volume Base]  should not aggregate up Geography Dimension

    right now it is aggregating up along all the dimensions say Time,Currency, Market,Geography

    I have seen the above code in this link; http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=1205362&SiteID=17


    with member measures.rootdemo as (
    root([Date].[Calendar Year].currentmember),
    root([Product].[Category].currentmember),
    root([Customer]),
    [Measures].[Internet Sales Amount]
    )
    select
    [Date].[Calendar Year].[Calendar Year].members
    *
    [Date].[Calendar Semester of Year].[Calendar Semester of Year].members
    *
    [Customer].[Country].[Country].members
    on
    0,
    [Product].[Category].[Category].members
    on
    1
    from [Adventure Works]
    where(measures.rootdemo)


    In the following situation should I create a calculated member based on my measure and set dimensions aggregations

    or is there any way we can set for a measure to NOT AGGREGATE along a dimension , and how do I implement it.

    Please help...

    Friday, September 19, 2008 2:29 AM