CODEWITHSUNDEEP
sql-server-2008-r2ssasmdx
Dave Markle
Dave Markle

Reputation: 97671

Should these two MDX queries return identical results?

I have two MDX queries (the basis for which come from Excel's CUBEVALUE() function). I expect the output of both of these queries to be identical, but they're not.

Consider this account structure:

some accounts ...
  Account A
      Account B
      Account C
      Account D
      Account E
other accounts ...

My Account dimension is defined using Account Intelligence -- it's also a Parent/Child dimension.

Query 1:

SELECT [Measures].[Actual] ON 0 
FROM [MYSYSTEM] 
WHERE (
    {[Account].[Accounts].&[Account B], [Account].[Accounts].&[Account C]}, 
    [Time].[Year - Month].&[2011-12-01T00:00:00]
)

Returns the aggregate [Measures].[Actual] value for Account A (yes, the parent), not the values for Accounts B and C in the specified time interval.

Query 2:

SELECT [Measures].[Actual] ON 0 
FROM [MYSYSTEM] 
WHERE 
    { 
    ([Account].[Accounts].&[Account B], [Time].[Year - Month].&[2011-12-01T00:00:00]),
    ([Account].[Accounts].&[Account C], [Time].[Year - Month].&[2011-12-01T00:00:00])
    }

Returns what I expect -- the [Measures].[Actual] value for Accounts B and C in the specified time interval.

My question is this -- is there something I'm missing conceptually about MDX as a language? Am I wrong to think that these queries should generally return the same value? Does this point to a flaw in how I laid out my Account Dimension?

Upvotes: 0

Views: 110

Answers (1)

daniel_aren
daniel_aren

Reputation: 1924

I think it has to do with you dimension, i replicated the queries with my cubes and got the same result both times:

select [Measures].[Amount] on 0,
[Account].[Account Type].[Account Type] on 1
from [Cube]
where (
{[Account].[Accounts].&[4],[Account].[Accounts].&[5]}
,[Date].[Calendar].[Month].&[2002]&[11]
)

select [Measures].[Amount] on 0,
[Account].[Account Type].[Account Type] on 1
from [Cube]
 where {
([Account].[Accounts].&[4],[Date].[Calendar].[Month].&[2002]&[11]),
([Account].[Accounts].&[5],[Date].[Calendar].[Month].&[2002]&[11])
}

Could it have something to do with the use of account intelligence? http://msdn.microsoft.com/en-us/library/ms174759(v=sql.100).aspx I haven´t used that myself..

Upvotes: 1

Related Questions