Saturday, February 25, 2012

Calendars - Hierachy issue

I have date dimension that uses a date_skey as the primary key (format = 20070101). I have both fiscal and calendar hiearchies. My problem is that date based functions (ie YTD and ParallelPeriod) work with my regular calendar hierachy but not the fiscal hierachy. I have the following fields:

Year (type years)

Quarter (type quarters)

Month (type months)

Week (type weeks)

Date (type date)

FiscalYear (type FiscalYears)

FiscalQuarter (type FiscalQuarters)

FiscalMonth (type FiscalMonths)

FiscalWeek (type FiscalWeeks)

date_skey (type regular)

Calendar Hierachy:

Year (type years)

Quarter (type quarters)

Month (type months)

Date (type date)

and

Fiscal Hierachy:

FiscalYear (type fiscalyears)

FiscalQuarters(type fiscalquarters)

FiscalMonths (type fiscalMonths)

Date (type date)

Am I using date and date_skey incorrectly in this setup?

Any recommendations are welcome.

Based on results from the Adventure Works Date dimension, which has both Calendar and Fiscal heirarchies, you may need to use more explicit versions of MDX time series functions for the Fiscal hierarchy. For example, instead of YTD([Date].[Fiscal].CurrentMember), try PeriodsToDate([Date].[Fiscal].[Fiscal Year], [Date].[Fiscal].CurrentMember), etc.|||This was the right answer - YTD, MTD would not work on my Fiscal Calendar hierarchy but I was able to use PeriodsToDate in place of them. Thanks

No comments:

Post a Comment