Cousin

The Cousin functionality will get you the member on same position as the starting based on a new toplevel member. Most of the time you use it in the same way you use ParallelPeriod.

If you look at a date or calendar dimension is might look like this: All ==> Year ==> Semester ==> Quarter ==> Month ==> Date.

If you use a query like this:

SELECT Cousin
   ( [Date].[Fiscal].[Fiscal Quarter].[Q4 FY 2011],
     [Date].[Fiscal].[FY 2012]
   ) ON 0
   , [Measures].[Sales Amount] on 1
FROM [Adventure Works]

you get:

What happens is that the server first figures out one which level [Date].[Fiscal].[FY 2012] is (Year).

Next it figures out the position of [Date].[Fiscal].[Fiscal Quarter].[Q4 FY 2011] within the level [Date].[Fiscal].[Fiscal Quarter] (fourth).

Followed by how many levels there are between Fiscal Year and Fiscal Quarter (2).

Finally it will start at [Date].[Fiscal].[FY 2012] go down two levels to [Date].[Fiscal].[Fiscal Quarter] and pick the fourth member ==> [Date].[Fiscal].[Fiscal Quarter].[Q4 FY 2012]

!WARNING!

All levels have to have exactly the same structure on all levels. Check out this on adventure works 2014:

SELECT Cousin
   ( [Date].[Fiscal].[Fiscal Quarter].[Q4 FY 2005],
     [Date].[Fiscal].[FY 2012]
   ) ON 0
   , [Measures].[Sales Amount] on 1
FROM [Adventure Works]

You get:

You would expect Q4 FY 2014!

This is caused by the fact that Fiscal Date Hierarchy in 2005 isn’t a complete year:

It is missing the first half year, making Q4 FY 2005 the second element of the quarter level instead of the fourth!

Have Fun And Till Next Time