May 18, 2007 at 8:56 am
I have a cube containing bonds that I would like to calculate the the average moody's rating based on the amount. The non-mdx formula would look something like this.
sum of ((moody_rating_weight_number * amount) / total_amount)
I can't seem to get the detial level data as a measure to do the calc and I get errors if I try to use dimension level data.
asset | rating | amount | calc |
| 1 | 1 | 10 | 0.066667 |
| 2 | 1 | 20 | 0.133333 |
| 3 | 2 | 30 | 0.4 |
| 4 | 3 | 40 | 0.8 |
| 5 | 3 | 50 | 1 |
| 150 | 2.4 |
This sample returns the weighted average value of 2.4
Thanks
May 18, 2007 at 10:21 am
Are you using AS 2000 or 2005?
For the fields you outlined, where are these found (ie fact table or dimension/s)? If we can assume that the rating is found in the fact table (a potentially poor assumption as it very well could be time related) but anyways... you need to do the detail level part of the calculation prior to loading to AS otherwise the engine will sum(amount) and sum(rating) and then perform A * B which obviously isn't correct.
If you can get the rating in to the same transaction record as the Amount, then you could create a new fact table field (if you're using a view, this would be easy, alternatively you could even use a claculated field in a table, depending on data sizing) that is simply the rating * amount and then create calc member (in the cube) that uses the new field / the total of amount.
Steve.
May 21, 2007 at 6:57 am
That worked. I had tried doing it with a calculation based on non-additive measures, but that didn't work.
Anyone know how to do lookups with measures and display character values? Now that have the weighted average, I would like to look up the letter rating that goes with the numerica average and display it as a measure. Not sure if this is possible.
Thanks.
May 21, 2007 at 7:48 am
Again, not sure which version you're using but if it's AS2K5, then you could write a Stored Procedure (look them up in BOL) which returns the character value based on the input numerical value. Alternatively, if you have a small enough range (e.g. A through E) then you could simply implement a case statement (or several nexted IIF's) and achieve the same result.
Steve.
May 21, 2007 at 9:33 am
Thanks for the advise. I am using as2k5. If I can use a sp, that would be great since I could easily pass in the weighted average, round it and then look up the value. How do you call a sp from inside metrics section? Do you happend to have any samples?
Thanks.
May 21, 2007 at 10:13 am
I don't personally have any samples but the following links should give you some clear info on how to implement them and also things to be aware of when using stored procs in AS.
Mosha -> Best Practices for ADOMD.Net Stored Procs -> here
Many -> AS Stored PRoc public project -> here
Chris Webb -> Full source of a stored proc for finding current member across all attribute dims -> here
Steve.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply