Limitations of MeasureExpression

I have a many to many dimensional problem that I am trying to solve.

Am I right in thinking that MeasureExpression is limited to a simple expression such as [value]*[percantage] and cannot cope with [value]*[percentage1]*[percentage2]?

What I am trying to achieve is similar to the example under the heading Measure Expressions and Many-to-Many Dimensions in the article http://msdn2.microsoft.com/en-US/library/ms345139.aspx. In this example there is a many to many relationship between Organisation and Store Sales; and the weighted [Amount] is calculated in the MeasureExpression as [Amount]*[Ownership].

They key difference in my scenario is that instead of a single many to many relationship between Organisation and Store Sales I have several relationships so I would need to have a calculation such as [Amount]*[Ownership]*[Sales Director Percentage].

Is there a way that MeasureExpression could support more complex calculations - or is there a better way to achieve this?

Thanks

Yes, the measure expressions are limited to A*B or A/B. You should check out Marco Russo's paper on many-to-many dimensions and see if there is anything in there you can use: http://www.sqlbi.eu/Default.aspx?tabid=80|||

Thanks for your input Michael. In fact I had come across that document the previous day, but it doesn't cover my particular scenario. The tricky bit is always that fact that there is a weighting to be applied to each of the many-to-many dimensions.

In the end I decided to handle this in the ETL by breaking down the fact table into constituent parts representing each unique combination and have SSAS aggregate only those needed for each calculation. It seems to me that SSAS is very good at aggregation, but not so good at apportioning values.

We live and learn ...

|||

What about this case:

create intermediate measure as AB = A * B and set its visible property to false

and than create your target measure as MyMeasure = AB * C

|||

I guess that would work in theory, but since I actually need A * B * C * D * E I would need three invisible measures and one visible one which seems a little clunky.

Having talked this through with a couple of other people I think the solution of breaking this down in the ETL process and having SSAS simply perform the aggregation seems to be the best approach.

Thanks.

|||

Hi,

you can try and use Calculated Measures (calculations).

The expressions here can take multiple measures, and also infact invoke .Net assemblies/ stored procedures.

Apart from that , you can use MDX and its array of functions.

check the MSDN site on Calculated Measures.

HTH

Regards