S
S
Shedal2012-09-20 14:50:53
SQL Server
Shedal, 2012-09-20 14:50:53

[SSAS] Generic calculation measure (perhaps using a stored procedure)?

What to achieve
There are dimensions [D1], [D2], [D3] and measure [M].
Each dimension is related to [M] as many-to-many.
I need to create a generic measure with the formula [M] / S( [M] ), where S is the sum of all measure facts associated with at least one member of the current dimension (current = used in the MDX query).
What I tried
S( [M] ) can be given as a calculated measure using MDX:

CREATE HIDDEN [Total M] =
  AGGREGATE(
    DESCENDANTS(
      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 ),
    [Measures].[M]
  );

We have to aggregate all members except [All], but this solution works. (If you take the total value of [M] over the entire dimension, then the value is incorrect, because the type of relationship is many-to-many.)
Using AXIS( 1 ), - in fact, AXIS( [First Dimension Index] ) , — allows this calculated measure to be universal and work with any of the dimensions [D1], [D2], [D3].
Now you can also set the measure using the formula [M] / S( [M] ):
CREATE MEMBER CURRENTCUBE.[Measures].[Share of M] AS
  [Measures].[M] / [Measures].[Total M]

However, this versatility also has disadvantages. For example, the following query will return an "infinite recursion" error:
SELECT [Measures].[Share of M] ON 0,
ORDER( [D1].MEMBERS, [Measures].[Share of M] ) ON 1
FROM [MyCube]

This, however, is quite logical, because in order to get AXIS( 1 ).ITEM( 0 ), which we use in [Total M], SSAS needs to first get the set of all members on axis 1. But to get this set, we first need to sort members of [D1] using the same measure. There is a cyclical dependency.
It turns out that such a universal measure can be used only in the simplest queries. And in real conditions it is practically useless.
What to do?
Maybe there is a way to solve the problem using MDX, but it doesn't work for me. So, if you have any ideas, please tell me which way to go.
Another direction that I want to try is to write a stored procedure in C # that would perform all the necessary calculations. But here I'm stuck - I don't know how to get the current dimension (similar to AXIS( 1 ).ITEM( 0 ).DIMENSION).

Answer the question

In order to leave comments, you need to log in

1 answer(s)
S
Shedal, 2012-09-25
@Shedal

The solution was found on the MSDN forums: social.msdn.microsoft.com/Forums/en-US/sqlanalysisservices/thread/04019705-5b65-48f8-b403-a6d015c9dec5

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question