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

Calendar dimension with multiple Fiscal calendars

I am building a data warehouse using the dimensional model -- i.e. fact and
dimensional tables in a star schema.
For my Calendar dimension, I want to include Fiscal periods, but the data
warehouse has data for different clients that have different fiscal calendars.
How do I handle that ?
If I understand well, you have:
Customer A - 15/10/2006 - CY 2006 FY 2007
Customer A - 30/09/2006 - CY 2006 FY 2006
Customer B - 15/10/2006 - CY 2006 FY 2006
Customer B - 15/10/2006 - CY 2006 FY 2006
You want to see FY 2006 data where FY is the one specific for each
customer.
If this is the case, I think the right model is to have one Fiscal
Calendar dimension (with FY Year, FY MonthNumber, FY QuarterNumber)
that is linked to the fact table.
Of course this model doesn't support you if you want to change the FY
of a Customer without changing the data already loaded into the fact
table.
Marco Russo
http://www.sqlbi.eu
http://www.sqljunkies.com/weblog/sqlbi
Craig HB wrote:
> I am building a data warehouse using the dimensional model -- i.e. fact and
> dimensional tables in a star schema.
> For my Calendar dimension, I want to include Fiscal periods, but the data
> warehouse has data for different clients that have different fiscal calendars.
> How do I handle that ?

Calendar dimension with multiple Fiscal calendars

I am building a data warehouse using the dimensional model -- i.e. fact and
dimensional tables in a star schema.
For my Calendar dimension, I want to include Fiscal periods, but the data
warehouse has data for different clients that have different fiscal calendar
s.
How do I handle that ?If I understand well, you have:
Customer A - 15/10/2006 - CY 2006 FY 2007
Customer A - 30/09/2006 - CY 2006 FY 2006
Customer B - 15/10/2006 - CY 2006 FY 2006
Customer B - 15/10/2006 - CY 2006 FY 2006
You want to see FY 2006 data where FY is the one specific for each
customer.
If this is the case, I think the right model is to have one Fiscal
Calendar dimension (with FY Year, FY MonthNumber, FY QuarterNumber)
that is linked to the fact table.
Of course this model doesn't support you if you want to change the FY
of a Customer without changing the data already loaded into the fact
table.
Marco Russo
http://www.sqlbi.eu
http://www.sqljunkies.com/weblog/sqlbi
Craig HB wrote:
> I am building a data warehouse using the dimensional model -- i.e. fact an
d
> dimensional tables in a star schema.
> For my Calendar dimension, I want to include Fiscal periods, but the data
> warehouse has data for different clients that have different fiscal calend
ars.
> How do I handle that ?