Answered by:
How to create a dimension based on calculated measure?

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
Question
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 TSQL, 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

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
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 helpsAshwani Roy
Please click the Mark as Answer button if a post solves your problem!

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 

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

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
Please click the Mark as Answer button if a post solves your problem!

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 
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 TSQL, 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

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:
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

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

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 
Dear Tomislav,
Thanks for the suggestion. I have tried several modifications of the SCOPE statement but I either get one nonnull 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

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


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 email (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 
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 
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 (nonmoving 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/enUS/sqlanalysisservices/thread/5c32c9a2fd80458a96fcce5cb0d7b78f .
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 
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 
Ok, thanks.
I'll post here again when I'm done with this example.
Tomislav Piasevoli
Business Intelligence Specialist
www.softpro.hr 
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
VISIBLE = 0;
Weigthed Source TQ Buckets Grand Total
Getting no data for [weighted source tq buckets] members. Thanks 
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 
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

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 

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 hardcoded 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 
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