This project has moved and is read-only. For the latest updates, please go here.

Creating a generic calculation as a stored procedure

Topics: Developer Forum, Project Management Forum, User Forum
Sep 19, 2012 at 4:30 PM
Edited Sep 19, 2012 at 4:31 PM

I need to create a calculation (which I will then use to define a calculated measure), which would be generic in the following way. I need it to calculate a share of [Measure1] associated with the current dimension member, compared to the total count of [Measure1] in the cube.

Total count of [Measure1] in the cube is also sometimes tricky — for dimensions that are related to my measure as many-to-many, I want to count only the facts that are actually related to at least one member of the current dimension.

Can I, in my stored procedure, somehow find out which dimension is currently being used, in order to calculate the right value of total? If more than one dimension is used in the query, I guess it's even more complex then..

Sep 19, 2012 at 9:56 PM

Creating calculations as stored procedures is possible, but it's generally not a good idea and should only be done as a last resort. There is a lot of overhead in calling out to stored procedure and you generally want to use them for things that are not called too often. So they are good for admin functions and for calculating sets on your axis, but should be avoided for calculating values.

I'm not sure If I understand what you mean by "calculating a share". It sounds a bit like you may be wanting to calculate an average. If this is the case then best thing to do is to create 2 measures over your measure1 column - one using the count aggregate function and another using sum (the aggregate function is a property that each measure has). Then create your average as a simple MDX calculated measure that is [Sum of Measure1] / [Count of Measure1].

Sep 20, 2012 at 10:27 AM
Edited Sep 20, 2012 at 10: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] =
  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]
  );

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
FROM [MyCube]

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 [Share of M].

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.

Sep 25, 2012 at 4:11 PM

There was a great deal of help given here: http://social.msdn.microsoft.com/Forums/en-US/sqlanalysisservices/thread/04019705-5b65-48f8-b403-a6d015c9dec5

So I managed to implement this using MDX.

Sep 25, 2012 at 10:13 PM

That's good news. I believe that the designers of MDX tried to make is so that calculations work regardless of the context which makes it tricky to write context sensitive calculations like this. Using MDX is definitely the best approach. I managed to track down some blog posts that my friend Tomislav wrote about similar scenrios.

http://tomislav.piasevoli.com/2010/01/23/average-among-siblings/

http://tomislav.piasevoli.com/2008/11/03/goldfish-rank/