Unanswered: Tricky Question about using Calculated Member in AS
I am really stuck here and need some help.
The scenario is that I have a dimension called Product... each product is given points and this is defined in the underlying product table.
The table on which my fact is based has a field called QtySold (which is the qty sold for the product). WhatI want is to somehow use Calculated Member (if that is the right solution) so that for any selected product, the user get's to see a calculated field called, say' Points earned, which will be QtySold multiplied by the points for that product.
Hoping that my question is clear, can someone please help me find a solution.
The best way is to put the points into the fact table. You know the product and the quantity, so just multiply them and put the points value in the fact table. Also consider what happens if the points values change over time -- does someone get retroactive credit based on the new points value? If not, then definitely put it in the fact table or track the points as a type-2 SCD.
The other way is to create a member property for points (from the underlying table) and create a calculated member in your measures dimension that computes the total points. Here's why you might not want to do that: the default behavior is for AS to give you the product of the sums, not the sum of the products (of quantity and points) that you want. To get around this, you have to write recursive MDX that checks to see if Product is at a leaf node, multiply if it is, and recurse into its leaf-level descendants if not. That's fine if you have just a few levels and members, but the server bogs down if it's wide and deep or if you try this technique on multiple dimensions. So it depends on your situation.
The recursive MDX works this way:
create a calculated member called [Measures].[Total Points]
the mdx formula for it is
IIF(isleaf([Product].CurrentMember), StrToValue([Product].CurrentMember.Properties("Points")) * [Measures].[Quantity], Sum(Descendants([Product].CurrentMember,,Leaves), [Measures].[Total Points]))