Arbitrarily picking a dimension to add members to

Question

The following script gives exactly the result I want.

It feels like a hack as I've added the custom members VALUE and VALUE_MTD onto the hierarchy [Customer].[Country]. I've chosen this hierarchy arbitrarily - just not used [Measures] or [Date].[Calendar] as they are already in use.

Is there a more standard approach to returning exactly the same set of cells?

WITH 
  MEMBER [Customer].[Country].[VALUE] AS 
    Aggregate([Customer].[Country].[(All)].MEMBERS) 
  MEMBER [Customer].[Country].[VALUE_MTD] AS 
    Aggregate
    (
      PeriodsToDate
      (
        [Date].[Calendar].[Month]
       ,[Date].[Calendar].CurrentMember
      )
     ,[Customer].[Country].[VALUE]
    ) 
SELECT 
  {
    [Customer].[Country].[VALUE]
   ,[Customer].[Country].[VALUE_MTD]
  } ON 0
 ,NON EMPTY 
      {
        [Measures].[Internet Sales Amount]
       ,[Measures].[Internet Order Quantity]
      }
    * 
      Descendants
      (
        {
            [Date].[Calendar].[Month].&[2007]&[12]
          : 
            [Date].[Calendar].[Month].&[2008]&[01]
        }
       ,[Date].[Calendar].[Date]
      ) ON 1
FROM [Adventure Works];

Answer 1

The standard approach is called utility dimension. If you Google this term, you will find several descriptions of this approach. A "utility dimension" is one which does not reference any data, but is just added to the cube for the purpose of being able to cross join them with all other dimensions for calculations. You can have one or more of them.

Thus, in most cases, physically there is nothing in the dimension. It is just used for calculated members. (Depending on the implementation, you may have the attribute members defined physically, if you want to have some properties for them. But then, only the default member is referenced in the star schema from the fact tables. The attribute member values are then overwritten in the calculation script.)

Typical applications for this are time calculations like YTD, MTD, MAT (Moving Annual Total, i. e. a full year of data ending in the selected date), or comparisons like growth vs. a previous period.