Setting the default date to last available date

I always forget what the MDX is for setting the default member of the date dimension to point to the last date available in the fact table. Worse still, when I found it after I searched in Google, I always forgot what they mean. I thought it’s about the time I write it down.

This is the MDX:

tail(nonempty([DateDim].[DateAttribute].Members)).item(0)

Note:

• [DateDim] is the name of the date dimension.
• [DateAttribute] is the name of the date attribute in the date dimension, which you want to get the latest member.
• [Measure1] is the measure from where you want to get the latest member on.

We can test it in Advanture works like this:

select tail(nonempty([Date].[Date].Members)).item(0) on 0
from [Adventure Works];

And this is what they means:

• Tail returns a set (the last N subset).
• To get a tuple out of a set, we use Item function.
• Item(0) means the first tuple in the set.
• The nonempty function limits the Date members to just the ones which has the default measure.

The above MDX expression uses the default measure. To use a specific measure, the MDX is like this:

select
  tail
  ( NonEmptyCrossJoin
    ( [Date].[Date].Members *
      [Measures].[Internet Sales Amount]
    )
  ).item(0).item(0) on 0
from [Adventure Works];

Meaning:

• The tail returns 2 dimensional set of “[July 31st, 2008], [Internet Sales Amount]”.
• The first Item(0) gives us “[July 31st>, 2008], [Internet Sales Amount]” as a 2 dimensional tuple.
• The second Item(0) gives us the first item of that tuple, which is “[July 31st, 2008]”, as a member.
• You can try it be removing just 1 item(0), to see the difference.

If the date dimension is used a few times in the cube with different names (role play dimensions), we can’t put the Default Member in the Property of the Date attribute because it will cause name resolution problem. Instead, we need to put it in the Calculation Script, like this:

ALTER CUBE CurrentCube UPDATE DIMENSION [Date],
DEFAULT_MEMBER = 'tail(NonEmptyCrossJoin([Date].[Date].Members * [Measures].[Internet Sales Amount])).item(0).item(0)';

We can put this ALTER CUBE anywhere in the Calculation Script; it can be at the top or bottom, it doesn’t matter.

The error message we get if we put the default member in a role play date dimension is:

Error 21: The specified default member '[Date].[Date].&[YYYYMMDD]' must be from the '[Delivery Date].[Date]' hierarchy.

But if we change it to [Delivery Date], it will say:

Error 21: The specified default member '[Ship Date].[Date].&[YYYYMMDD]' must be from the '[Delivery Date].[Date]' hierarchy.

Hope this helps. As usual if you have any questions or points to discuss you can contact me at vrainardi@gmail.com.