Thursday, March 29, 2012

Default member behaviour

I was under the impression that the behaviour of an MDX query would be the same if I didn't specify anything in the where clause or if I specified the hierarchies default members explicitly. But I've found an example on the Adventure Works cube where this is not the case. Is this expected?

For example:

If I execute the following:

SELECT { [Account].[Account].MEMBERS } ON COLUMNS FROM [Adventure Works]

The first few members I get back are

All Accounts, Balance Sheet, Net Income, Assets, etc. etc.

The [Account].[Accounts] hierarchy has the default member [Account].[Accounts].&[47] so I would expect the following MDX to return me exactly the same members, but it doesn't.

SELECT { [Account].[Account].MEMBERS } ON COLUMNS FROM [Adventure Works] WHERE ( [Account].[Accounts].&[47] )

It misses out Balance Sheet, Assets and other members.

Your assertion is right about omitting default members as long as we are talking about hierarchies belonging to *other dimensions*. Meaning dimensions you do not have on columns or rows.

For hierarchies belonging to the same dimension your assertion does not apply. Other forum participants may be able to describe this in more detail.

No comments:

Post a Comment