hi there. i do not understand how to use an attribute in a calculation. In the query below, I'm trying to say:
for all members of the date.calendar hierarchy, show me the sum of (sales amount fact * dealer price attribute for the product sold)
with
member [measures].[fact times attribute]
as cdbl([Measures].[Sales Amount]) * cdbl([Product].[Dealer Price].CurrentMember.memberValue)
select [measures].[fact times attribute] oncolumns,
[Date].[Calendar].allmembersonrows
from [Adventure Works]
where [Geography].[City].&[Beaverton]&[OR]
this gives a type mismatch error complaining that "All Products" cannot be cast to double. But I don't understand how to force the calculation to occur at the lowest level and for aggregation to be done subsequently.
Product is currently at the "All Product" member. What you are actually wanting to do is get the price for each product and multiply that by the sales of that product and then aggregate (i'll assume sum) those values. That query looks like the one below. (Actually, the one below is kinduva shortcut to the solution. Instead of going to the individual product, I just went to the product price. If two products have the same price, the query kinda says that we treat them the same. Nit-picky, I know.)
Good luck.
Code Snippet
withmember [measures].[fact times attribute] as
SUM([Product].[Dealer Price].[Dealer Price].Members,
[Measures].[Sales Amount])
select [measures].[fact times attribute] oncolumns,
NONEMPTY [Date].[Calendar].allmembersonrows
from [Adventure Works]
where ([Geography].[City].&[Beaverton]&[OR])
;
|||Dear Bryan,
Thanks for your pointers. I actually work in hong kong so were ~12 hours ahead of you. I'm checking this from home and so can't test it. but i'm reviewing it now, because I'd love to get this right for work tomorrow.
Looking at your solution, one thing I find very conceptually troubling:
>>I don't see a multiplication sign anywhere!!!!<<
So at the very point where I would imagine attribute and fact really connect, the code is silent! Also when you refer to a shortcut and being nitpicky, that is all just going straight over my head - my knowledge of mdx (rather like my knowledge of cantonese 
just isn't evolved enough to understand the subtleties here. I can just about order lunch with a lot of gesticulation but that's it.
i will of course kick the tires on this tomorrow when I get into work. But [embarrassingly] I've been trying to figure out what the missing link is here for almost 3 days without success! Would you mind just leaving a working "best practice" solution for me? I feel like I sound like a lazy person - I'm not, I just don't grock this yet and its driving me nuts.
Yours,
John G.
|||
Sorry about that. I was pushing to get the code assembled and totally dropped that one critical part of the code. Try this one. I use the CurrentMember of the Dealer Price to get to the value.
Code Snippet
withmember [measures].[fact times attribute] as
SUM(
[Product].[Dealer Price].[Dealer Price].Members,
[Measures].[Sales Amount]*[Product].[Dealer Price].CurrentMember.MemberValue
)
select [measures].[fact times attribute] on columns,
NON EMPTY [Date].[Calendar].allmembers on rows
from [Adventure Works]
where ([Geography].[City].&[Beaverton]&[OR])
;
|||
Jo,
Try to use named calculations in the datasourceview of your factTable... it would be a better performance, because you only run the formulas once when the cube is processing.
Regards!
|||
ok it works fine - THANKS!
I also tried this which returns the same results - is it logically identical?
with
member [measures].[fact times attribute]
asSUM([Product].[Product].[Product].Members,
[Measures].[Sales Amount]*[Product].[Dealer Price].CurrentMember.MemberValue
)
select {[measures].[fact times attribute]} oncolumns,
NONEMPTY [Date].[Calendar].allmembersonrows
from [Adventure Works]
where ([Geography].[City].&[Beaverton]&[OR])
i'm not sure if you'd have the patience of a saint to wade through what follows, but I'm trying to understand the execution of the query. Would you mind critiquing the below, or if it's easier for you just describe the above query in pseudo code for dummies
For my general understanding when you refer to [Product].[Dealer Price].CurrentMember.MemberValue,
what is the context of the CurrentMember? Is it:
1. The current member in the context of traversing the set of members of the product hierarchy as defined by the set argument passed to the SUM function.
or 2. the currentmember in some wider context of the query (actually I'm not sure if that makes sense, so I'll go with answer 1).
Assuming 1. above, then I would interpret the query as follows:
1. slice by beaverton
2. iterate through all members of the date hierarchy.
3. for each sales fact within the intersection of beaverton and the current date, calculate [fact times attribute] as follows:
a) for each member of the product hierarchy ...
i) ... extract the tuple set defined by the intersection of that product within the current region [for many product members this will be a null set]
ii) for that set of tuples, sum up (sales amount * dealer price).
iii) keep running total of that sum, and the final total will be your result of the current date member.
|||
thanks - this did occur to me, since as i understand it calculated members (even cube scoped ones) are calculated at runtime not at processing time, correct?
however, in my real calculation I'm using another calculated member which doesn't exist in the underlying table so this makes it tricky.
Would you say that in general, one should strive to do as much data cleaning / preparation / precalculation as possible outside the cube, and then just leave the cube to prepare aggregations and pure analysis calculations that are hard to do outside of mdx?
|||Yeah joGo, I'm with you! If you have other calculated members that you cannot replace for named calculation, so you are right...|||
Your code is logically similar and should produce the same result. You may want to test for performance differences, but the only way I can imagine a performance difference would exist would be under a very particular situation I suspect does not exist in the cube. (In other words, they probably have the same performance so don't sweat it.)
Regarding the CURRENTMEMBER question, you have to keep in mind context at all times. In the SUM function, you generate a set. That set definition is in the context of the cube as a whole. If I want to limit that context based on my slicer (WHERE clause), I can use the EXISTING keyword in the set definition.
So, now I have a set. Then, for each member in that set, I will return and/or calculate a value. That value is determined in the context of the member from the set I am currently working with. At this point, how that set came to be is unknown to me. The set has been determined and I'm just working through it blindly. I think this is what you're saying in the section at the bottom of your email.
Sorry I am being slow to respond today. The MSDN forum email seems to be jammed up a bit and I'm not getting alerts like I should.
Thanks,
Bryan
