Many to many dimension query returns incorrect value
I've just been shown a repro of what looks like a bug where a query with a many-to-many dimension and the expression MyM2MDim.MyHier.Children in the Where clause returns an incorrect value. It looks like a case where an incorrect assumption has been made for query optimisation purposes. I can repro on AS2005 SP2 and AS2008 RC0.
Upvotes: 2<=-=Jul 24 2008 8:50PM=-=>
Thanks a lot Chris for reporting the problem.
Unfortunately it is too late to look at this in 2008 release of Analysis Services.
We will take a look at this in the next release.
Edward Melomed<=-=Aug 1 2008 12:56AM=-=>
I think that a good solution would be a different one than simply changing the behavior of the query optimizer. I would add a property to measures (or measure groups) to specify if the evaluation of that specific measure/measure group has to always consider the related m2m dimension and its intermediate measoure group or if this passage can be optimized. This would solve the issue I described in my m2m paper (http://www.sqlbi.eu/manytomany.aspx) related to the distinct count alternative measure.