Search code examples
ssasmdxolapcube

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

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?
Solution

  • 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