MDX syntax: Difference between Period.Financial Year.Financial Year and Period.Financial Year.Children?

Question

We have a cube called Revenue.

SELECT
    {[Measures].[Billable Hours]} on columns,
     [Period].[Financial Year].**[Financial Year]** on rows
FROM [REVENUE];

SELECT 
    {[Measures].[Billable Hours]} on columns,
     [Period].[Financial Year].**Children** on rows
FROM [REVENUE];

There is a dimension "Period", with I guess the attribute hierarchy is Financial Year? and if I want rows like 2012, 2013, 2014, I have to say either [Period].[Financial Year].Children or [Period].[Financial Year].[Financial Year]. To get [Period].[Financial Year].[Financial Year], in SSMS, I opened that attribute hierarchy, dragged the little dot under Members that says Financial Year, and that's what it gave me.

Both ways get me the same result. So just wondering,

1. is .Children more preferable, or is it just preference?

2. Also, if one were to "diagram" [Period].[Financial Year].[Financial Year], would it be "Dimension.Level.Member"? so is [Financial Year]/Children a member, or how does one diagram that "whole thing" and

3. what should I call that whole thing?

Answer 1

Unsure what you mean by "diagram".

Both of you column expressions are, I think, short-cuts.

This

[Period].[Financial Year].[Financial Year]

Is short for this

[Period].[Financial Year].[Financial Year].MEMBERS

And this:

[Period].[Financial Year].CHILDREN

Is short for this:

[Period].[Financial Year].[All].CHILDREN

---

(To rewind a bit)

All attribute hierarchies have two levels - an all level and a leaf level. The all level only has a single member [All] which in turn has members - the leaves. There is also the leaf level, made up of the leaves.

So it looks like you've tapped into these two different levels of the hierarchy [Financial Year].

---

Which should you use? Sometimes I don't believe it matters. In more complex scenarios maybe which you use will have an impact on performance.

Sometimes you might like to grab the All member and the leaves - then you can do something like the following:

SELECT 
    {[Measures].[Billable Hours]} on columns,
     [Period].[Financial Year].MEMBERS on rows
FROM [REVENUE]; 

This should return a set that includes the All member?

---

Q2

This [Period].[Financial Year].[Financial Year] is Dimension.Hierarchy.Level. The hierarchy's All member is not accessible at this level.

---

To repeat Greg Galloway's comment:

[Period].[Financial Year].CHILDREN assumes the All member is the default member and the current member in the context and won't work as intended in all contexts.

Preferable to use [Period].[Financial Year].[All].CHILDREN or [Period].[Financial Year].[Financial Year].MEMBERS

Answer 2

John, I have been in a similar situation before and had posted a question on SO on that.

The point is that it is not just a shortcut, but a risky practice to write [Dim].[Attribute].CHILDREN, since the [All] member, which the other answer talks about is a scoped member and it can't always be assumed that you are actually writing [Dim].[Attribute].[All].CHILDREN unless you write that additional [All]. Best to avoid it.

Also, the [Period].[Financial Year].[Financial Year] is not really a suggested way of invoking members.

The moral of the story is we definitely should try to not fall for any "shortcuts".

EDIT: Why it is imperative to be explicit!

The reason why .MEMBERS is the "suggested" way is because the SSAS engine does an additional check when you use the .MEMBERS function.

If a newbie MDX coder accidentally use a nonexistent hierarchy name(typo..uses Calendar1 instead of Calendar, the engine surprisingly doesn't throw any error. (Pardon me for the white gaps below the following images)

But if .MEMBERS is used, an extra layer of checking takes place, which gives a clear and definitive error message:

Obviously, here the engine is to blame, probably this is a bug. But, we must do our part at using the tools at hand. In a similar way, 9 out of 10 times, Dim.Hier.CHILDREN syntax will work, but the fact is, it is misleading. A newbie might look at the code and assume that a hierarchy can have children. While the truth is only a member can have children.

Little off topic, but in SQL too, it is a standard ANSI syntax to use a semicolon at the end of a statement, but very few people actually use that syntax. The engine in turn punishes us by throwing error when semicolons are not used with some operators, like when we use CTE. SO should we make it a habit to use more semicolons? Hell yeah! Is it a really a shortcut? No!