title: Different results in MDX using aggregate function in SQL 2008 R2 and SQL 2012
body: <p>I have a problem in SSAS where a strange behavior happen. I have a cube in prodution, but, for simulation de problem a created a small cube for isolate the problem. I created a small cube that simulate the problem. In this cube, there is one dimension and one fact. In [Sales Territory], I configured the UnknownMember property to true. In cube calculations, I put a simple scope code below:</p>
  
  <pre><code>SCOPE([Measures].[SalesAmountCustomValue]);
      THIS = [Measures].CurrentMember+10000000;
  END SCOPE;
  </code></pre>
  
  <p>I execute the below MDX in SQL Server 2008 R2, and I have different results using and not using UnknownMember with Aggregate function. In SQL Server 2012, the result it's the same as expected.</p>
  
  <pre><code>WITH 
  MEMBER [Sales Territory].[Sales Territory Country].[Countries] AS
    Aggregate(
        {
          [Sales Territory].[Sales Territory Country].[All].UNKNOWNMEMBER,
          [Sales Territory].[Sales Territory Country].&amp;[9],
          [Sales Territory].[Sales Territory Country].&amp;[6]
        }
    )
  MEMBER [Measures].[SalesAmountCustomValueWithAggregate] AS
    (
      [Sales Territory].[Sales Territory Country].[Countries],
      [Measures].[SalesAmountCustomValue]
    ),
  FORMAT_STRING="0,000;-0,000"
  SELECT {
        [Measures].[SalesAmount],
        [Measures].[SalesAmountCustomValue],
        [Measures].[SalesAmountCustomValueWithAggregate]
     } ON 0
  FROM AdventureWorks
  WHERE {
      [Sales Territory].[Sales Territory Country].[All].UNKNOWNMEMBER,
      [Sales Territory].[Sales Territory Country].&amp;[9],
      [Sales Territory].[Sales Territory Country].&amp;[6]
    }
  </code></pre>
  
  <p><b>The result is:</b><p>
  <ul>SalesAmount: 11,038,845</ul><p>
  <ul>SalesAmountCustomValue: 21,038,845</ul><p>
  <ul>SalesAmountCustomValueWithAggregate: <b>41,038,845</b></ul><p></p>
  
  <p><b>Removing UnknownMember of aggregate function, the result is:</b><p>
  <ul>SalesAmount: 11,038,845</ul><p>
  <ul>SalesAmountCustomValue: 21,038,845</ul><p>
  <ul>SalesAmountCustomValueWithAggregate: <b>21,038,845</b></ul><p><p></p>
  
  <p>The behavior of MDX engine is different. In first exemple, the scope calculation is executed for each member before aggregate function. In this case, scope calculation is executed three times. The same occur putting a invalid member in aggregate function as [Sales Territory].[Sales Territory Country].&amp;[AAA], but, in this case, scope calculation is executed two times in SQL 2008 R2 and SQL 2012.</p>
  
  <p>Removing UnknownMember, scope calculation is executed one time, summing 10 billions.</p>
  
  <p>I need have the same behavior as in SQL 2012 for same MDX:</p>
  
  <p><ul>SalesAmount: 11,038,845</ul><p>
  <ul>SalesAmountCustomValue: 21,038,845</ul><p>
  <ul>SalesAmountCustomValueWithAggregate: 21,038,845</ul><p><p></p>
  
  <p>I tested the following versions:<p>
  <ul>SQL 2008R2 SP2 10.50.4279.0 x64</ul><p>
  <ul>SQL 2012 11.0.2100.60 x64</ul><p></p>
  
  <p>Do you have any ideas about what is happening?</p>
  
  <p>Thanks a lot in advance.<p>
  Paulo Corrêa<p><p></p>