Currently I have a Time dimension that has levels Year/Month/Day/Hour.
I am getting requests for per-Week reports. How do I add a Week level to my
hierarchy, but keep members at the Day level associated directly with the
Month they are in, rather than associated with the Month in which each Day's
Week starts.

For example, assume Week starts from Saturday midnite. The last week in
March/05 ran from Mar 27 to Apr 2 inclusive. I want Apr 1 and Apr 2 to be
counted in that week, but I want those two days counted with April, not March.

Thanks,

Les

You could check out these examples...there is a time dimension using week

I succesfully got the underlying time dimension structure / ETL scripting
pulled out and a currently using it....

http://www.microsoft.com/sql/ssabi/D...s/download.asp

"lmcphee" <lmcphee (AT) discussions (DOT) microsoft.com> wrote

I always create a separate table for each level of my time dimension
and then connect them via foreign keys. I then create snowflake
dimensions in Analysis Services to bring the dimensions together as
need be. In your case...you day table would have a foreign key
pointing to both the month table and the week table. The week table
would have a foreign key pointing to the year table.

This technique gives you the flexability in design to attach a fact
table at any of the various time level granularities. For
instance...you may have actual values at the day level and goal values
at the month or quarter level.

In Analysis Services, you can then use a shared dimension based on your
snowflake time schema and attach it to cubes that are different levels
of granularity. It will calculate properly if you disable the lower
level time levels that are not at the proper grain for a given cube.