Time dimension confusion

[neil phillips]

Hi, first post here so I hope someone can advise on this. I'm looking to remodel time dimensions to somehow combine monthly 'periods' and weekly 'periods'. The aim being similar to a fiscal calendar where each week is allocated into a month period (e.g. 4-4-5 repeated)

The issue is that my employer has two types of timeframe for managing the business income. We may charge our customers on a weekly or monthly basis depending on the agreement. Fiscal Year is 1st April - 31st March. Months are months, e.g. 1st to 31st, weeks run Monday to Sunday.

Transactions are daily and this is where my problem occurs. Currently I have dm_time_week dimension and dm_time_month dimension.
For accounts charged weekly, we want to understand how much of the Monday charge we are still owed on the Sunday (week end); for accounts charged monthly, we want to understand how much of the 1st of month charge we are still owed on the 31st (month end).

We need to understand 'weekly' customers weekly, 'monthly' customers monthly; however we also want to trend, report and understand the above in a standard 12 period year, 4 quarter year.
I'm fine with putting weeks into 'months'. For example, my June month may run from Monday 1st June 2009 to Sunday 28th June 2009 (4 weeks).
My problem is also doing it the other way around. If I do the above then any transactions received on 29th June will be determined as being not in June. This is fine for the customers who pay on a weekly frequency, but for those who pay on a monthly frequency the June period will show them as not paid, when they may have paid on 29th June.

Do I have to retain 2 time dimensions here, or combine into one and have more than one Fact or Summary table reporting across these different customer timeframes? Or could I have 2 different keys in the same Dimension and use them differently depending on whether my customer is a weekly or monthly one to determine which transactional day goes into which 'period'?

The more I think about this the more I confuse myself, but I am sure someone else in the DW world has dealt with something similar.

Sorry the above is long! Thanks in advance, Neil

[juz_b]

This appears to be very similar to the Multiple Fiscal Calendar scenerio. You can reference the following post for design options:

http://forum.kimballgroup.com/dimensional-modeling-and-data-architecture-f6/multi-enterprise-calendar-dimension-t132.htm

Hope this helps.