I have a Dimension called ExDimension with 1 single attribute called ExAttribute. I currently have 2 members in that dimension along with the unknownmember with data for it. When I run the following MDX, I get nothing back and I don't understand why. Can anyone give me a direction on where to look?
SELECT
{
[ExDimension].[ExAttribute].CHILDREN
}
DIMENSION PROPERTIES [ExDimension].[ExAttribute].[Attribute Code],
[ExDimension].[ExAttribute].[Company],
[ExDimension].[ExAttribute].[Is Current],
[ExDimension].[ExAttribute].[Is Current Store],
[ExDimension].[ExAttribute].[Is Current Chain],
[ExDimension].[ExAttribute].[Attribute Label]
ON 0 FROM [CR Data Warehouse]
Your code relies on an implicit type conversion of Analysis Services:
Children works on a member, see the documentation. And you give it a hierarchy. In this case, SSAS does an implicit type conversion to the default member of the hierarchy. I. e. the code
[ExDimension].[ExAttribute].CHILDREN
is equivalent to
[ExDimension].[ExAttribute].DefaultMember.CHILDREN
In the standard case, this works as the default member is the All member, which has all the "normal" members as its children. I would assume that your ExAttribute hierarchy has a default member which is not the All member, but a member on the bottom level. And that does not have children, hence the above expression returns the empty set.
Assuming your intention is to get the members of the ExAttribute attribute hierarchy except for the All member, my preferred way of coding this would be to use
[ExDimension].[ExAttribute].[ExAttribute].Members
This does not use an implicit type conversion, and uses the ExAttribute level of the ExAttribute attribute hierarchy. Attribute hierarchies normally have two levels: the All level consisting just of the All member, and the level named like the attribute, which contains all the members coming directly from the dimension table. There can be variations: If you set the attribute property IsAggregatable to false, the All member and the All level is missing, and you can add calculated members to both levels.
You could even use
[ExDimension].[ExAttribute].[ExAttribute]
as, again, there is an implicit type conversion if you have a level and need a set: This implicit type conversion applies the Members function. But I prefer to be explicit, especially as the code does not get that much longer this way.
