In PMF, is it possible to have a custom time dimension with data in both Month level and week level.
For example,

TIME_KEY,Year,Month,Week,START_DATE,END_DATE,PRIOR_Year,PRIOR_Month,PRIOR_week
314,2009,1,1,20090104,20090110,2008,1,1
315,2009,1,2,20090111,20090117,2008,1,2
316,2009,1,3,20090118,20090124,2008,1,3
317,2009,1,4,20090125,20090131,2008,1,4
318,2009,2,1,20090201,20090207,2008,2,1
319,2009,2,2,20090208,20090214,2008,2,2
320,2009,2,3,20090215,20090221,2008,2,3
321,2009,2,4,20090222,20090228,2008,2,4

Can we also have records representing each month of the year along with the above type of records

TIME_KEY,Year,Month,'N/a',START_DATE,END_DATE,PRIOR_Year,PRIOR_Month,'N/a'
751,2009,1,NA,20090101,20090131,2008,1,NA
752,2009,2,NA,20090201,20090228,2008,2,NA
753,2009,3,NA,20090301,20090331,2008,3,NA
754,2009,4,NA,20090401,20090430,2008,4,NA

Where N/A is the placeholder data for the "week" column.

The reason why we want to do this is because some of the measures are joining
to the time dimension in the Week level and some in Month level.

You can have month level and week level but you must ensure that your "months" will roll up isomorphically to weeks and weeks back to months. Months can begin in the middle of a week. Weeks can start and cross months. You want to make those rollups work properly - otherwise things won't add up. So when building your Time Dimension your months have to be looser.

Example. July 2009 started on a Wednesday and ends on a Friday. That means there are two partial weeks in Jul - the first and the last. You can define (with a Sun-Sat week) that July begins on the a real calendar date of either June 28 or July 5 for example. You might then have July be 5 weeks starting June 28 and ending August 1.

You can of course design your calendar however you want.

Hope that makes sense.

Raghunath -

Just to expand a little a Bob's response -

Yes, you can have records representing the month or even the year level in your source data. I recommend you use spaces instead of "NA". NA will display in the Preview but it may not work with reports.

Otherwise, looking at your data, it appears that your week 1 of 2009 starts on Jan 4th. So the question is what will happen when you run a report on Jan 1st through Jan 3rd and specify the Current Time Period? I've never tried this, but my best guess is that for weekly data you will see the last week of 2008 while for monthly data you will see Month 01. This may or may not be what want you want to see.

Please let us know how this works out for you.

EricH

How about changin the Month level data to

751,2009,1,NA,20090104,20090131,2008,1,NA
752,2009,2,NA,20090201,20090228,2008,2,NA

so the month start date is aligned with the first week begin date
and
the month end date is aligned with the last week end date

I see that the "From Date" "To Date" ranges overlap between weeks and months. Is that ok?

Endre

That would work, however you would have to insure that month 12 of 2008 ended on 20090103.

EricH

Year,
Last Month of year
Last Week of year all must end on the same day.

That still leaves the question. Is that ok to have rows date ranges overlap? See below, ID 751 overlaps with 314,315,316,317

314,2009,1,1,20090104,20090110,2008,1,1
315,2009,1,2,20090111,20090117,2008,1,2
316,2009,1,3,20090118,20090124,2008,1,3
317,2009,1,4,20090125,20090131,2008,1,4

751,2009,1,NA,20090104,20090131,2008,1,NA