I am running Analysis Services 2008 R2 and have come across some behavior that I really do not understand and I can't seem to get to the bottom of it. I have a dimension called Segment which is a simple Parent-child dimension where only one of the four top-level members has any children. This one member, has two children. Only leaf nodes have any values.
In the dimension I have used AttributeAllMemberName to allow "All Segments" to be used to refer to the top-level members. There are three dimensions used in the cube: Segment, Country and Year.
When I run:
SELECT {{Descendants([Country].[Global],, SELF_BEFORE_AFTER)}} ON ROWS,
{[Segment].[All Segments].children}*{[Measures].[Volume tonnes]} ON COLUMNS
FROM [Market]
WHERE [Year].[2012]
I see all members on the columns but the one node that has children has an empty column. My understanding is that "children" should show me only one level not two. If, on the other hand I run
SELECT {{Descendants([Country].[Global],, SELF_BEFORE_AFTER)}} ON ROWS,
{[Segment].[(all)].[All Segments].children}*{[Measures].[Volume tonnes]} ON COLUMNS
FROM [Market]
WHERE [Year].[2012]
I see exactly what I would expect; the four top-level children with correctly aggregated values for the one child that has its own children. No grand-children are shown. In either case the right number of rows are displayed.
The only difference between the two queries is that the "[(all)]" level has been explicitly listed in the second query. Given that the "all" member is defined as the only member of the "(all)" level set, these two queries should return the same values but they don't. I must be missing something in the dimension config, but what? Can someone point me in the right direction to fix this? I need the query to work properly without having to use "[(all)]".
To stop this post becoming too bloated, I have posted some screen-grabs of BIDS to my own website to show the configuration of the dimension. There are three attributes and the dimension itself that require configuration but I can only post two links so have linked them all in from this page: http://coolwire.co.uk/share/BIDS.html
The Hierarchy and the Ordering are related to the Key by rigid attribute-relationships.
It all looks okay to me but the problem must be in here somewhere.