Thursday, July 31, 2014

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.

image

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:

image

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:

image

You would expect Q4 FY 2014!

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

image

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

No comments:

Post a Comment