Sunday, March 25, 2012

Calculated Measure Aggregation Seems to differ from MSAS 2K5 and 2000

I'm in the process of upgrading a cube in MSAS 2000 to MSAS 2005 (SP 1). In the existing MSAS 2000 cube, I have the following calculated measure defined:

SUM(Descendants([Term].[Term].CurrentMember, [Term Name]),
SUM
(
PeriodsToDate
([ActivityDate].[Std].[(All)]
,strToMember("[ActivityDate].[Std].&[" + Cstr([Term].[Term].CurrentMember.Properties("CensusDateValue")) +"]"
)), [Measures].[Activity Hours]))

This measure is supposed to sum up all activity up until the "CensusDateValue". it seems to be working fine and Aggregates up correctly even when the Term is a level other then [term name]

I came up with the following AS 2005 MDX that attempts to perform the same calculation(Some of the dimension names and properties have be modified):

SUM(Descendants([Term Dimension].CurrentMember, [Term Dimension].[TermName] ),
SUM(
PeriodsToDate(
[Activity Date Dimension].[Date].[(All)]
,STRTOMEMBER("[Activity Date Dimension].[Date].&[" + CSTR([Term Dimension].[Term Name].CurrentMember.Properties("Census Date SK")) + "]")), [Measures].[User Activity Hours]))

The calculation seems to work fine as long as I have the [Term Dimension].[Term.name] in the rows axis, otherwise it returns null. I suspect this is a difference between how member properties are calculated and aggregated between MSAS2K5 and MSAS200

Does anyone have any ideas or suggestions for troubleshooting?
It's hard to tell what's going on without looking at your cube design. Why don't you debug the problem by picking a row which caused the problem. Break your calculation into individual components and check to see if the component evaluates as expected at the given coordinate.

No comments:

Post a Comment