none
How to create a dimension based on calculated measure?

    Question

  • Greetings,

    I have a calculated measure - call it [Weighted Bucket] - bucketing a metric as following 1, 2, 3 etc up to 10. I need to create a dimension out of it to aggregate other measures by the buckets, like:

    Weight Bucket      Sales
    1                          500
    2                          100
    3                          400

    Since it's calculated measure, everything has to be done using MDX. Any assistance will be greatly appreciated. Thanks
    • Edited by polaro7 Thursday, July 23, 2009 1:18 PM
    Thursday, July 23, 2009 1:17 PM

Answers

  • Hi polaro7,

    take a look at my recent post on blog: http://tomislavpiasevoli.spaces.live.com/blog/cns!5BB64CF526505D83!325.entry .

    I's not exactly the same situation, but I think you'll get the idea how to solve your problem (write different T-SQL, the one that will return you 10 records, your buckets, the rest should be easy).

    Regards,


    Tomislav Piasevoli
    Business Intelligence Specialist
    www.softpro.hr
    • Marked as answer by polaro7 Monday, July 27, 2009 3:56 PM
    Friday, July 24, 2009 6:20 AM
    Answerer
  • Ok,

    I think I made one more oversight. But a simple tweak should correct it - use tuple again, for Measures.CurrentMember, like this:


    Scope
        ( [Weigthed Source TQ Buckets].[Weigthed Source TQ Buckets].children );
          This = Sum( Existing ( [Sites].[Site].children * [Sources].[Sources].children ),
                      iif( [Weigthed Source TQ Buckets].[Weigthed Source TQ Buckets].CurrentMember.MemberValue =
                           ( [Measures].[Weighted Source TQ Buckets], [Weigthed Source TQ Buckets].[Weigthed Source TQ Buckets].[All] ),
                           ( [Measures].CurrentMember, [Weigthed Source TQ Buckets].[Weigthed Source TQ Buckets].[All] ), null )
                    );
    End Scope;
    

    Hope that settles it.

    Regards,


    Tomislav Piasevoli
    Business Intelligence Specialist
    www.softpro.hr
    • Marked as answer by polaro7 Tuesday, August 04, 2009 9:10 PM
    Tuesday, August 04, 2009 8:35 PM
    Answerer

All replies

  • I have done this in adventureworks cube. Here I am trying to add up internet sales for all products and display it with Name Grandtotal
    WITH 
    
    MEMBER [Date].[Month of Year].[GrandTotal] AS SUM([Date].[Month of Year].MEMBERS,[Measures].[Internet Sales Amount])
    ,FORMAT_STRING = "Currency"
    
    select [Measures].[Internet Sales Amount] on 0
    ,{[Date].[Month of Year].CHILDREN,[Date].[Month of Year].[GrandTotal]} on 1
    from [Adventure Works]
      Internet Sales Amount
    January $2,375,856.68
    February $2,502,386.86
    March $2,610,615.17
    April $2,778,842.08
    May $3,114,646.27
    June $3,180,923.99
    July $1,911,262.79
    August $1,899,606.67
    September $1,834,668.15
    October $2,009,169.29
    November $2,076,069.60
    December $3,064,629.66
    GrandTotal $58,717,354.44

    You can apply the same for you weighted Bucket and SUM() and display as a row. Hope this helps

    Ashwani Roy

    My Blog

    Please click the Mark as Answer button if a post solves your problem!

    Thursday, July 23, 2009 1:32 PM
    Answerer
  • Thanks a lot for your quick reply, Ashwani. I have tried your suggestion and the result returns empty. I see you use dimension members [month of year] in your example. When I plug in the calc measure as below, I get empties:

    WITH

    MEMBER [Date].[Date].[GrandTotal] AS SUM([Measures].[Weighted Buckets], [Measures].[clicks])
    ,FORMAT_STRING = "Currency"

    select [Measures].[clicks] on 0
    ,{[Date].[Date].[GrandTotal]} on 1
    from <cube>

    What do I do wrong? Thanks

    What I see needed here is first we have to extract all the available "members" from [weighted buckets] measure and then create the new dimension populated with the members 1..2..3..10. Essentially, I need a "dynamic" dimension out of a calc measure using MDX
    Thursday, July 23, 2009 1:48 PM
  • OK I think I confused you... Can you post what you data set looks like , what are measures and dimensions involved and what is the output that you want.

    Ashwani Roy

    My Blog

    Please click the Mark as Answer button if a post solves your problem!

    Thursday, July 23, 2009 1:54 PM
    Answerer
  • Dear Ashwani,

    I have a fact table [Daily History] which stores a quality score field. I use that field to build a [weighted average quality score] calc measure. Then I use case to get to the [Weighted Buckets] calc:

    create member measure.[weighted buckets] as
    case
    when [weighted average quality score]>=0 and [weighted average quality score]<1 then 0
    when [weighted average quality score]>=1 and [weighted average quality score]<2 then 1
    ...
    when [weighted average quality score]>9 and [weighted average quality score]<10 then 9
    else 10 end

    So the measure is now populated with 1..10. I need to create the same dimension with the members 1..10 so my clients can aggregate in Excel ANY other measure (not only a few) as following:


    Weighted Bucket      Measure 1      ..       Measure x
    1                             500                ..            20
    2                             100                ..            50
    3                             400                 ..           100

    It would be easy to implement if [weighted buckets] was not a calc measure - build a [weighted buckets] dimension directly out of the fact table.

    Appreciate your assistance
    • Edited by polaro7 Thursday, July 23, 2009 2:18 PM
    Thursday, July 23, 2009 2:02 PM
  • Ok Now I understand the question better and here is my answer.

    I would suggest that you create a real dimension by putting this same case logic in a named query. Add this dimension to Datasource View.

    Once you have done that you will have to add column to the fact table(s) which contains the measures on which you are planning to aggregate.This column will be foreign key referencing this dimensions primary key.(You dont need real table in the DB , you can just have a named query for dimension and create this relation ship in the datasource view itself)

    Once this is done , then you analysis service will be able to dentify which measures related to which key in the dimension and you will be able to aggregate on it. I dont see how , without having relationship between dimension and fact analysis will do the grouping.

    Think of it like SQL SUM() and GROUP BY . if you dont group by attribute how will you SUM() a column . Hope this helps you. I know this was not the reposne you expected but I dont see a way that you can create such a dimension on the fly and not have relationship with measure and still be able to group it.


    Ashwani Roy

    My Blog

    Please click the Mark as Answer button if a post solves your problem!

    Thursday, July 23, 2009 2:22 PM
    Answerer
  • Thanks Ashwani,

    The complication here is that there is no "materialized" measure [weighted bucket] to which to link the new dimension and creating the physical equivalent will slow down processing considerably. The measure only exists in calculated form. Anyways, perhaps creating a new fact table aggregating keys by [weighted buckets] is the only solution. I was entertaining an idea of extracting the list of values from [weighted buckets] calc and then somehow materializing it. Is there a way to extract all values from a calc measure using MDX? Thanks
    Thursday, July 23, 2009 8:48 PM
  • Hi polaro7,

    take a look at my recent post on blog: http://tomislavpiasevoli.spaces.live.com/blog/cns!5BB64CF526505D83!325.entry .

    I's not exactly the same situation, but I think you'll get the idea how to solve your problem (write different T-SQL, the one that will return you 10 records, your buckets, the rest should be easy).

    Regards,


    Tomislav Piasevoli
    Business Intelligence Specialist
    www.softpro.hr
    • Marked as answer by polaro7 Monday, July 27, 2009 3:56 PM
    Friday, July 24, 2009 6:20 AM
    Answerer
  • Dear Tomislav,

    Thanks for the insight, very interesting. As suggested, I have built the following calc member and the MDX below correctly links the calc with dimension:

    With MEMBER [Measures].[Bucket] AS Avg(existing [Groups].[Group Name].children, iif( [Weigthed Source TQ Buckets].[Weigthed Source TQ Buckets].CurrentMember.MemberValue =[Measures].[Measures].[Weighted Source TQ Buckets],[Measures].[Measures].[Weighted Source TQ Buckets],
    null))
    select bucket on 0,
    [Weigthed Source TQ Buckets].[Weigthed Source TQ Buckets].
    children on 1
    from <cube>

     Bucket
    1 (null)
    2 2
    3 3
    4 4
    5 5
    6 6
    7 7
    8 8
    9 9
    10 10

    But when I try to bring the bucket dimension on axis 1, and a different measure on 0, there is no apparent relationship:

     

     

    with
    MEMBER [Measures].[Bucket] AS Avg(existing [Groups].[Group Name].children, iif( [Weigthed Source TQ Buckets].[Weigthed Source TQ Buckets].CurrentMember.MemberValue = [Measures].[Measures].[Weighted Source TQ Buckets], [Measures].[Measures].[Weighted Source TQ Buckets], null))
    select [gross revenue] on 0,
    [Weigthed Source TQ Buckets].[Weigthed Source TQ Buckets].
    children on 1
    from <cube>

     

     

       Gross Revenue
    1 $1000
    2 $1000
    3 $1000
    4 $1000
    ..
    10 $1000

     



    So it seems that the link is established between the [buckets] - measure and dimension only. What I would like to see is that when I bring other measures against the [Weigthed Source TQ Buckets] dimension is that the measure is sliced accordingly.

    All ideas are always appreciated


    • Edited by polaro7 Friday, July 24, 2009 6:36 PM
    Friday, July 24, 2009 6:32 PM
  • As I said (a bit too optimistic, I admit), your case is not exactly the same. You did good so far, but in case you need calculations for any measure, you should consider dummy dimension to be a real shell/utility dimension. Which means you should provide calculations on it, not by defining new calculated measure, as it was the case with histograms (and as you did above).

    By not going much into your latest post, I suggest you either scope your utility dimension with a case statement, similar to your initial case statement in this thread, or by assigning a calculation per each bucket. I think the first option is feasible, you can do it in a one shot (solve the problem). You're close. All you have to do is put the logic on utility dimension. In case you fail, I'll assist, but as I can see, you're managing quite fine, so I trust you'll score this on your own. Nevertheless, don't hesitate to ask in case you need additional help.

    Regards,


    Tomislav Piasevoli
    Business Intelligence Specialist
    www.softpro.hr
    Friday, July 24, 2009 9:51 PM
    Answerer
  • Dear Tomislav,

    Thanks for all your assistance. The following MDX buckets rev nicely by quality score. The only complication here is that we have more than 100 measures to consider and I need to write a separate MDX for each of them (any way around?).

    with MEMBER

    [Measures].[Bucket] AS Avg(existing [Groups].[Group Name].children, iif( [Weigthed Source TQ Buckets].[Weigthed Source TQ Buckets].CurrentMember.MemberValue = [Measures].[Measures].[Weighted Source TQ Buckets], [Measures].[Measures].[Weighted Source TQ Buckets], null))
    MEMBER [Measures].[gross revenue] AS sum(existing [Sites].[Site].children, iif( [Weigthed Source TQ Buckets].[Weigthed Source TQ Buckets].CurrentMember.MemberValue = [Measures].[Measures].[Weighted Source TQ Buckets], [Measures].[Measures].[gross revenue], null)),
    FORMAT_STRING = "Currency"
    select [gross revenue] on 0,
    [Weigthed Source TQ Buckets].[Weigthed Source TQ Buckets].
    children on 1
    from <cube>

     gross revenue
    1 $0.13
    2 $758,708.68
    3 $2,099,174.58
    4 $2,424,040.33
    5 $48,158,160.67
    6 $7,191,771.19
    7 $2,793,108.99
    8 $2,450,744.39
    9 $8,779,654.85
    10 $1,883,954.65
    • Edited by polaro7 Monday, July 27, 2009 3:56 PM
    Monday, July 27, 2009 3:55 PM
  • Hi Polaro,

    no, not this way. In my previous post I advised you to provide calculations on utility dimension and to avoid creating new calculated measure(s). What you need to do is to scope [Weighted Source TQ Buckets] dimension (that's your utility dimension, I presume) and assign a proper value using a reference to Measures.CurrentMember. That way no matter which measure you use, it should work correctly. In short, it's the other way around.

    Let me try. You'll correct what's wrong:

     

    Scope( [Weigthed Source TQ Buckets].[Weigthed Source TQ Buckets].[Weigthed Source TQ Buckets].MEMBERS );
        This = Sum( Existing [Sites].[Site].children,
                    iif( [Weigthed Source TQ Buckets].[Weigthed Source TQ Buckets].CurrentMember.MemberValue =
                         [Measures].[Measures].[Weighted Source TQ Buckets],
                         [Measures].[Measures].CurrentMember,
                         null )
                  );
    End Scope;



    Regards,


    Tomislav Piasevoli
    Business Intelligence Specialist
    www.softpro.hr

    Monday, July 27, 2009 7:29 PM
    Answerer
  • Dear Tomislav,

    Thanks for the suggestion. I have tried several modifications of the SCOPE statement but I either get one non-null bucket or no relationship between a measure and the buckets. I confess I do not use SCOPE that often, need to learn more.

    If I use the following:

    Scope

     

    ( [Weigthed Source TQ Buckets].[Weigthed Source TQ Buckets].children );
    This = Sum( Existing
    iif( [Weigthed Source TQ Buckets].[Weigthed Source TQ Buckets].CurrentMember.MemberValue =[Measures].[Measures].[Weighted Source TQ Buckets],[Measures].[Measures].CurrentMember, null ));
    End Scope;

    Then I get just a single bucket with a measure:

     

    Bucket Rev
    6 $12,276,275.34
    Grand Total $12,276,275.34


    If I try to "spread out" the bucket measure by say, sites, the relationship between measures and buckets gets broken - the pivot only shows totals, all distinct buckets have no associated measures:

    Scope( [Weigthed Source TQ Buckets].[Weigthed Source TQ Buckets].children );
    This = Sum(Existing [Sites].[Site].children,
    iif( [Weigthed Source TQ Buckets].[Weigthed Source TQ Buckets].CurrentMember.MemberValue =[Measures].[Measures].[Weighted Source TQ Buckets],[Measures].[Measures].CurrentMember, null ));
    End Scope;

    Bucket Rev
    Grand Total $12,276,275.34

     

     

    As I understand it, the right SCOPE has to define separate areas of the cube where different bucket measures are described. In my cases, I either get the bucket measure associated with ALL or break the relationship completely. So far that is where I am struggling. Thanks for your help.

     

    Tuesday, July 28, 2009 2:10 PM
  • Hi Polaro, I can't tell what the exact problem is (without additional info), but I can tell you few suspects.

    One is that we should compare membervalue with a tuple, not a measure only. Something like this:

    ( [Measures].[Measures].[Weighted Source TQ Buckets], [Weigthed Source TQ Buckets].[Weigthed Source TQ Buckets].[All] )

    The next is that you have not transfered everything from calculated measures into scope. What is the definition of [Measures].[Weighted Source TQ Buckets] ?

    Maybe it should be included in the scope, instead of referring to it. Afterall, it's a measure, so it might corrupt our intentions to calculate per current measure.

    One more thing, the first scope is not good, the second might be ok (existing over something, not nothing).

    Finally, there is an option to include another utility dimension, which would have only 2 members - normal result and weighted calculation. It could be build as usual, normal member is a regular one, the other is calculated with a formula. Just like YTD etc. calculations. That option, that scenario, should be triggered only if we fail to bring all calculations inside the scope. Only then we will require it. Right now I feel some calculations are still on some measures, which is not good. Maybe the first tuple helps there (that would be the best), but if not, we'll try to put calculations inside the scope. Just be sure to provide everything necessary next time - definitions of relevant calc measures and a few words about your design and intentions in the cube.

    I'm sorry we didn't solve this so far, but I still believe we might succeed.

    Regards,


    Tomislav Piasevoli
    Business Intelligence Specialist
    www.softpro.hr
    Thursday, July 30, 2009 12:48 AM
    Answerer
  • Dear Tomislav,

    Thanks for your assistance, I think you almost nailed it. The first approach by comparing membervalue with a tuple does produce a breakdown but the bucketed values are incorrect. That is, when I apply the comparison against

    ( [Measures].[Measures].[Weighted Source TQ Buckets], [Weigthed Source TQ Buckets].[Weigthed Source TQ Buckets].[All] )

    I get revenue bucketed incorrectly even though the actual "bucketing" now happens! I think the only thing left is to scope the dimension correctly, need to bring the combination of sites and sources into scope:

    Scope

     

    ( [Weigthed Source TQ Buckets].[Weigthed Source TQ Buckets].children );
    This = Sum( existing ([Sites].[Site].children*[Sources].[Sources].children),
    iif( [Weigthed Source TQ Buckets].[Weigthed Source TQ Buckets].CurrentMember.MemberValue =
    ([Measures].[Weighted Source TQ Buckets], [Weigthed Source TQ Buckets].[Weigthed Source TQ Buckets].[All]),
    [Measures].
    CurrentMember, null ) );
    End Scope


    The way I audit it is bringing, say, Revenue alongside [measures].Weighted Source TQ Buckets=7. My sum(revenue) for the bucket 7 value should equal sum(revenue) for [Weigthed Source TQ Buckets].[Weigthed Source TQ Buckets].[7]. They are not.

    Sorry for not responding earlier. Exciting stuff, thanks


    Tuesday, August 04, 2009 8:10 PM
  • Ok,

    I think I made one more oversight. But a simple tweak should correct it - use tuple again, for Measures.CurrentMember, like this:


    Scope
        ( [Weigthed Source TQ Buckets].[Weigthed Source TQ Buckets].children );
          This = Sum( Existing ( [Sites].[Site].children * [Sources].[Sources].children ),
                      iif( [Weigthed Source TQ Buckets].[Weigthed Source TQ Buckets].CurrentMember.MemberValue =
                           ( [Measures].[Weighted Source TQ Buckets], [Weigthed Source TQ Buckets].[Weigthed Source TQ Buckets].[All] ),
                           ( [Measures].CurrentMember, [Weigthed Source TQ Buckets].[Weigthed Source TQ Buckets].[All] ), null )
                    );
    End Scope;
    

    Hope that settles it.

    Regards,


    Tomislav Piasevoli
    Business Intelligence Specialist
    www.softpro.hr
    • Marked as answer by polaro7 Tuesday, August 04, 2009 9:10 PM
    Tuesday, August 04, 2009 8:35 PM
    Answerer
  • Bull's eye! Thanks a million!
    Tuesday, August 04, 2009 9:10 PM
  • Hi Polaro,

    I'm really glad we did it!



    Since this is a very interesting case, similar to Histograms (a link I provided earlier as a starting point in this solution), I'd like to present it in a compact form (cleaned from all distractions) in my blog. And for that I need your help. You see, I know how to prepare it using Adventure Works database, but I'm missing two things here:

    1. what was/is the definition of your "weighted average quality score" measure? (or describe what are your weights)
    2. why didn't you create another fact table? (maybe the definition explains everything)

    They shall help me make a better introduction into this intriguing case.

    If the weights were just another field in your fact (or the one that could easily be added into the fact, i.e. by referring to a dimension), a simple tweak in DW/DSV could solve it. Somehow I believe this was not the case, but something more complex, for what you reached for calculations in your cube. I'm curious to find out what it was.

    If you prefer not going public about it, you can send me an e-mail (see my profile). All I want to do is this - make the best similarity to your case using Adventure Works 2008 and explain it the best I can (leaving no questions unanswered), so that others can profit from it.

    Thank you in advance for consideration.



    Best regards!


    Tomislav Piasevoli
    Business Intelligence Specialist
    www.softpro.hr
    Wednesday, August 05, 2009 7:14 AM
    Answerer
  • Dear Tomislav,

    I will gladly help you out. We receive daily revenue numbers from many sources and the numbers are stored in a fact table, call it FactRevenue, along with the revenue's quality scores, something like:

    Date      site      rev     qscore
    Jul 30    site1    $100    9
    ....

    Since I need a weighted average of qscore ([weighted average quality score]), I prepare the fact table by introducing one more fields to DSV: RevTQ=rev*qscore

    When the cube is built, the new calc measure [weighted average quality score] is introduced as RevTQ/Rev (cube does summation according to the slices). So the new calc is a "virtual" measure which I still might have introduced somehow in the DSV but the measure I need - [weighted buckets] is based on the  [weighted average quality score] and must be calc measure not to create a DSV nightmare. The [weighted buckets] is


    create member measure.[weighted buckets] as
    case
    when [weighted average quality score]>=0 and [weighted average quality score]<1 then 0
    when [weighted average quality score]>=1 and [weighted average quality score]<2 then 1
    ...
    when [weighted average quality score]>9 and [weighted average quality score]<10 then 9
    else 10 end

    Hope it helps

    One more question - is it possible to define an aggregation based on the cube's aggregation, not only Sum? In the SCOPE below:

    ( [Weigthed Source TQ Buckets].[Weigthed Source TQ Buckets].children );
    This = Sum( existing ([Sites].[Site].children*[Sources].[Sources].children
    ),
    iif( [Weigthed Source TQ Buckets].[Weigthed Source TQ Buckets].CurrentMember.MemberValue
    =
    ([Measures].[Weighted Source TQ Buckets], [Weigthed Source TQ Buckets].[Weigthed Source TQ Buckets].[All]),
    [Measures].
    CurrentMember, null
    ) );
    End Scope

    would like to have something like This=Aggregate instead of This=Sum to be able to bring averages and counts together with the bucket dimension. Thanks



    Wednesday, August 05, 2009 2:05 PM
  • Hi,

    thanks for your help. Actually, I was interested in knowing how do you determine "q", in order to simulate similar calculation in Adventure Works DSV. Based on what was the score 9? Revenue amount (tresholds/ranges), other dimension (special store or territory), content of the transaction (non-moving product sold), etc?

    Additionally, after I made some thoughts about the subject, I realized it was the combination of bucketing over weighting that's hard to implement.

    Thanks for bringing the question about "Sum vs. Aggregate". You could use the Aggregate function instead, BUT only for regular measures. In case of a calculated measures, SSAS would throw you an error. See this thread for more info: http://social.msdn.microsoft.com/Forums/en-US/sqlanalysisservices/thread/5c32c9a2-fd80-458a-96fc-ce5cb0d7b78f .

    A workaround would be to define a calculated member before the scope as Aggregate of the set. On one of dimensions (Sites or Sources). And then dynamically build it inside a scope. So that you can use it in a tuple. That way it should work no matter the "regularity" of a measure (regular/calculated).


    CREATE MEMBER CurrentCube.[Sites].[Site].[myAggregate] AS
        null,
    VISIBLE = 0;
    
    SCOPE ( [Weigthed Source TQ Buckets].[Weigthed Source TQ Buckets].CHILDREN );
        [Sites].[Site].[myAggregate] =
            Aggregate(
                  Filter(
                          EXISTING ( [Sites].[Site].CHILDREN * [Sources].[Sources].CHILDREN ),
                          [Weigthed Source TQ Buckets].[Weigthed Source TQ Buckets].CurrentMember.MemberValue =
                          ( [Measures].[Weighted Source TQ Buckets],
                            [Weigthed Source TQ Buckets].[Weigthed Source TQ Buckets].[All] )
                        )
                     );
        This = ( [Sites].[Site].[myAggregate], [Measures].CurrentMember );
    End Scope;


    That's the idea, basically. To build a set that satisfies the condition, aggregate it and use it inside a tuple. Untested yet :-). Could be slower.

    Does it work? It does look that way to me (that it should according to the idea), but this is a mindbending subject and you never know what I might have forgotten.

    Regards,


    Tomislav Piasevoli
    Business Intelligence Specialist
    www.softpro.hr
    Thursday, August 06, 2009 1:49 PM
    Answerer
  • Dear Tomislav,

    Thanks for the insight, will try it out in a couple of days. Regarding your question, qscore is given to us as is, we only have a general idea on how it is calculated. For your purposes, you can use any ratio in Adventure Works, for instance, currency exchange rate can be weighted by revenue and then bucketed. Otherwise, you can introduce a dummy quality score for each line of the fact table where revenue is stored and assign 10, 9, etc for a certain customerids etc (whatever is available there). Should be easy to implement in DSV.

    Once you get the qscore, other calcs have been provided above. Please feel free to contact me if you have more questions.

    Best regards and thanks again
    Thursday, August 06, 2009 2:17 PM
  • Ok, thanks.

    I'll post here again when I'm done with this example.


    Tomislav Piasevoli
    Business Intelligence Specialist
    www.softpro.hr
    Thursday, August 06, 2009 3:26 PM
    Answerer
  • Dear Tomislav,

    I have finally tried your suggestion, apologies for the delay. I thought the below looks like a potential issue and the results did indeed return empty:

    CREATE

     

     

    MEMBER CurrentCube.[Sites].[Site].[myAggregate] AS null,
    VISIBLE = 0;

    Weigthed Source TQ Buckets
    Grand Total

    Getting no data for [weighted source tq buckets] members. Thanks



    Tuesday, August 18, 2009 3:01 PM
  • I presume you added the scope part.

    Can you describe a bit more what you did and how you've tested?

    Afterall, it could be that thing when the Aggregate function takes the original definition of a member. Is it empty for regular measures too?

    I think I better make an example in AW2008 by the end of the week.

    Regards,


    Tomislav Piasevoli
    Business Intelligence Specialist
    www.softpro.hr
    Wednesday, August 19, 2009 1:27 PM
    Answerer
  • Dear Tomislav,

    I pretty much used your script first:

    CREATE MEMBER CurrentCube.[Sites].[Site].[myAggregate] AS
        null,
    VISIBLE = 0;

    SCOPE ( [Weigthed Source TQ Buckets].[Weigthed Source TQ Buckets].CHILDREN );
        [Sites].[Site].[myAggregate] =
            Aggregate(
                  Filter(
                          EXISTING ( [Sites].[Site].CHILDREN * [Sources].[Sources].CHILDREN ),
                          [Weigthed Source TQ Buckets].[Weigthed Source TQ Buckets].CurrentMember.MemberValue =
                          ( [Measures].[Weighted Source TQ Buckets],
                            [Weigthed Source TQ Buckets].[Weigthed Source TQ Buckets].[All] )
                        )
                     );
        This = ( [Sites].[Site].[myAggregate], [Measures].CurrentMember );
    End Scope;

    That returned nulls for [weighted source tq buckets]

    Then I read your suggestion again and changed it a bit by introducing sites.site into the scope:

    CREATE

     

    MEMBER CurrentCube.[Sites].[Site].[myAggregate] AS aggregate([Sites].[Site].CHILDREN, [Measures].CurrentMember),
    VISIBLE = 0;

    SCOPE

     

    ( [Weigthed Source TQ Buckets].[Weigthed Source TQ Buckets].CHILDREN );
    [Sites].[Site].[myAggregate] =
    Aggregate(Filter(EXISTING ( [Sites].[Site].CHILDREN * [Sources].[Sources].CHILDREN ),
    [Weigthed Source TQ Buckets].[Weigthed Source TQ Buckets].
    CurrentMember.MemberValue =( [Measures].[Weighted Source TQ Buckets], [Weigthed Source TQ Buckets].[Weigthed Source TQ Buckets].[All] )));

     

    This = ( [Sites].[Site].[myAggregate]);
    End Scope;

    Still the same result

    Weigthed Source TQ Buckets                    
    Grand Total              Null

     

    Thursday, August 20, 2009 1:36 PM
  • Hi, I made the example on Adventure Works. See here if it helps: http://tomislavpiasevoli.spaces.live.com/blog/cns!5BB64CF526505D83!370.entry .

    Regards,


    Tomislav Piasevoli
    Business Intelligence Specialist
    www.softpro.hr
    Monday, August 24, 2009 6:40 AM
    Answerer
  • Implemented it, works beautifully. Thanks a lot!
    Thursday, September 03, 2009 2:50 PM
  • Dear Tomislav,

    Buckets on a cell level - possible or not? The idea is to evaluate the calc measure cell by cell and assign the values into buckets irregardless of dimension members hard-coded in the SCOPE statement. The reason is that if we have dozens and hundreds of defined related dimensions (not members), I need to bring them all into scope which is a calculation nightmare. So why not define the buckets on a micro level and then roll the aggregations up.

    Appreciate your thoughts
    Tuesday, September 15, 2009 8:10 PM
  • Hi,

    in that case, take a look at Leaves() function in MDX script. Additionally, perform a search on the net for more resources on that subject (use MDX Leaves or similar keywords).

    Regards,


    Tomislav Piasevoli
    Business Intelligence Specialist
    www.softpro.hr
    Wednesday, September 16, 2009 5:06 PM
    Answerer