Howdy,
I have a calculated measure for a division that is calculated something like this (simplified):
MEMBER [Measures].Grade AS [Measures].Metal / [Measures].Tons
This measure is persisted in the cube. Normally, the measure correctly aggregates (ie shows Sum(Metal) / Sum(Tons)), but when I put it in MDX as follows, it incorrectly shows Sum(Metal / Tons):
WITH
MEMBER [Date].Calendar.YTD AS SUM(YTD( [Date].[Calendar].[Date].&[2006-03-26T00:00:00] )
SELECT [Date].Calendar.YTD ON COLUMNS,
[Measures].Grade ON ROWS
FROM [TheCube]
I assumed that this behaviour was because I was aggregating the measure via the SUM() and YTD() functions, but when I take this measure out of the cube and place it in my MDX as follows, the answer is correct again:
WITH
MEMBER [Measures].Grade2 AS [Measures].Metal / [Measures].Tons
MEMBER [Date].Calendar.YTD AS SUM(YTD( [Date].[Calendar].[Date].&[2006-03-26T00:00:00] )
SELECT [Date].Calendar.YTD ON COLUMNS,
[Measures].Grade2 ON ROWS
FROM [TheCube]
I kind of need that calculated measure to live in the cube, so if anybody could explain why, or suggest a fix, that would be wonderful to say the least.
Aranda.
Calc Measure - aggregation behaviour differs between cube and mdx
Namza
Thanks Tigran, I had a look at that. It seems that you may have been referring to SOLVE_ORDER because CALCULATION_PASS_NUMBER is a property of Cell calculations, not Calculated Members. Anyway, I tried using SOLVE_ORDER and was able to correctly switch the order of the division vs the sum when both calculated members were defined in MDX, however, when my division calc member was defined in the cube, it was ALWAYS calculated before my sum calc member (which is still defined in the MDX) regardless of the SOLVE_ORDER of either calc. member.
This seems very strange, especially when you consider this quote (from the link you posted above):
"When a query with calculated members is executed against a cube with calculated members, for example, the solve orders for both the query and the cube are evaluated as if the query were part of the cube"
TheDevilsJoker
You may want to look at CALCULATION_PASS_NUMBER property to be used with CREATE MEMBER statement.
Also the following article could be helpful:
http://msdn.microsoft.com/library/default.asp url=/library/en-us/olapdmad/agmdxadvanced_6jn7.asp
JavaBoy
From the syntax of your examples, it looks like you're using AS 2005; however, the link relates to AS 2000. There are differences in behavior of SOLVE_ORDER between AS 2000 and AS 2005, but using Aggregate() instead of Sum() should suffice for your scenario, like:
>>
WITH
MEMBER [Date].Calendar.YTD AS Aggregate(YTD( [Date].[Calendar].[Date].&[2006-03-26T00:00:00] )
SELECT [Date].Calendar.YTD ON COLUMNS,
[Measures].Grade ON ROWS
FROM [TheCube]
>>
Mosha's blog has an example of a similar usage of Aggregate() in AS 2005:
http://sqljunkies.com/WebLog/mosha/archive/2005/10/11/mdx_functions_as2005.aspx
>>
MDX functions in Analysis Services 2005
...
Aggregate function can also work when the current measure is calculated measure, by switching solve orders with it. I.e. If the current measure is Ratio: Sales/Cost, and we are computing Aggregate({USA, Canada}), then instead of error, it will return Aggregate({USA, Canada},Sales)/Aggregate({USA, Canada},Cost).
...
>>
And Chris Webb's blog also addresses the issue of Solve Order in AS 2005:
http://cwebbbi.spaces.live.com/Blog/cns!7B84B0F2C239489A!649.entry
>>
Solve Order in AS2005
...
>>