Sep 20, 2012 at 9:27 AM
Edited Sep 20, 2012 at 9:30 AM
I have a many-to-many relationship between dimensions [D1], [D2], [D3] and measure [M].
I need to create a generic calculated measure that would have the following formula: [M] / Σ( [M] ), where Σ stands for the sum of all measure's facts that are associated with at least one member of a given dimension.
It is possible to define Σ( [M] ) as a calculated measure, in a way similar to this:
CREATE HIDDEN [Total M] =
AXIS( 1 ).ITEM( 0 ).DIMENSION.LEVELS( 0 ).ITEM( 0 ),
AXIS( 1 ).ITEM( 0 ).DIMENSION.LEVELS.COUNT
) - AXIS( 1 ).ITEM( 0 ).DIMENSION.LEVELS( 0 ).ITEM( 0 ),
I have to aggregate over all members, except the [All] member. Otherwise, the total value of [M] in the cube will be returned, because I'm not slicing by this dimension.
Using AXIS( 1 ) makes the measure generic, being able to work with all of the "many-to-many" dimensions. (Actually, I use AXIS( [DimInd] ), where [DimInd] is a measure I wrote that returns the index of the first axis that contains a dimension).
Now I can define my desired measure:
CREATE MEMBER CURRENTCUBE.[Measures].[Share of M] AS
[Measures].[M] / [Measures].[Total M]
But the generic nature of it has downsides as well. For example, the following query fails because of infinite recursion:
SELECT [Measures].[Share of M] ON 0,
ORDER( [D1].MEMBERS, [Measures].[Share of M] ) ON 1
Which is rather logical — to get
AXIS( 1 ).ITEM( 0 ) in [Total M], we need a set of dimension members on axis 1, but this set is impossible to obtain until the members are sorted by
Maybe there is, indeed, a better solution than implementing this using a stored procedure, but I really don't see such a solution. Please advise me how I can proceed with this task.