Hi guys,

I am tearing my hair out over what must be 1) something that no one
has ever tried before or 2) something so obvious that I can't see it.
I'm betting its #2.

My client has given me data at a WEEK level. This means my fact table
is something like this:

SiteID
WeekID
Downloads

Now as much as I would want to create a logical fk relationship
between my DimDate table and Fact table on WeekID, I can't because
when viewing by Year or Month, and then Week, I really need my totals
to stay correct like this:

Year 2009 Week 12/28/09 - 01/04/10 Downloads = 250
Year 2010 Week 12/28/09 - 01/04/10 Downloads = 250

Total should always be 250, not 500. This is also a requirement when
viewing Month --> Week.

Because of this, I've created a DimTimeFilter table / dimension that
holds the Start and End date of all my Weeks, then I created a
FactlessTimeFilterDate table / dimension that holds the DateID for all
days in a Week. Then I created the many to many relationship against
the Fact table and everything pretty much works as advertised.

Ok - so here's the PROBLEM:

I need to be able to show a YEAR OVER YEAR value - so for the Week of
9/26/2010 - I need to grab the "this time last year's WEEK" - so this
should just be a calculated measure like this:

([Measures].[Downloads], [Time Filter].[Time Filter].Lag(52)), right?

The only problem is, when I do this, I all of a sudden get the 2010
weeks appearing in the 2009 Year space! Like this:

Year Week Downloads YOY Downloads
2009 9-20-09 - 09-26-09 199 <blank> -
this is normal, I don't have 2008 data
2009 9-27-09 - 10-03-09 250 <blank> -
this is normal, I don't have 2008 data
2009 (!!!!) 4-04-10 - 04-10-10 <blank> 300
2009 (!!!!) 4-11-10 - 04-17-10 <blank> 345

The values themselves are correct AND when I remove Year from the
Browser, it all works correctly. Can someone explain how to just get
the VALUE out of ([Measures].[Downloads], [Time Filter].[Time
Filter].Lag(52)), rather than the 2009 WEEK as well?

I know my example is confusing.. hopefully it is just a change on my
YOY calc'ed measure syntax.

Also - I have a very simple Date dimension - Date --> Month --> Year.

Thanks!

Sincerely,

J'son

If the lowest level is always Week, then you could create a Date
dimenion that only goes down to the week., therefore essentially
having 52 records per year. For each week you could use the End of
Week or Start of Week date as its key. This should solve the week
issue.
On the other hand, if you already have a DimDate at a day level, then
you could make a rule in the fact load to apply the End of Week and
Start of Week dates for the week provided and then link that back to
the DimDate at a day level. This would still rollup nicely to week >
month > year. Not sure why you need the other factlesss fact table.
I am doing the exact same for my fact tables, where sales are provided
for stores at a weekly level, the date is always standardisded in the
fact to revert to EOW (end of week date).
As for YOY, you do not need a separate time filter dimension, the
calculation should be on the existing time dimension, using the
Parallel Period function or if you were to use LAG, just ensure the
LAG 52 is applied at the Week level only.


Cheers
Dharmesh


On Oct 5, 8:57*am, Json <sitexc... (AT) hotmail (DOT) com> wrote:

On Oct 5, 3:53*am, Dharmesh <dharmesh_kal... (AT) hotmail (DOT) com> wrote:
Hi Dharmesh,

Thanks a bunch for your response. On your second suggestion - where in
Date already exists at the Day level - I've tried just creating the
Fact Table with a WeekID and then linking that back to DimDate's
WeekID field via a logical fk relationship in the Data Source View.
This actually caused more of a headache. It turns out that I need to
maintain a Week --> Year attribute hierarchy, which is fine, but some
weeks, such as 12/28/09 - 01/03/10 for instance, fall into both 2009
and 2010. This means that my WeekID key needed to be composite of
WeekID AND Calendar Year. To make matters worse, my cube wouldn't
build unless I added both a WeekID and CalendarYearID to my Fact
table. This was required to create the "normal" relationship now that
WeekID's key became composite. THAT meant that I had to store the same
value for the week of 12/28/09 - 01/03/10 twice - once for 2009 and
once for 2010. Ick.

On your first suggestion - that sounds promising! If I stored the data
in a Fact table with just the End Date of each Week, is there a way
that I could "trick" the Name of Date or some other member property of
Date to show my Week value like "12/28/09 - 01/03/10"? Wait.. I think
I'm getting there -

1. If I don't store my data at the Week level, I won't have the
composite key issue.
2. I can have my Day - Week - Year hierarchy without having to worry
about weekly crossovers because the data will be at a single day
level.
3. The data will STILL show up in the correct way when I swivel by
Year --> Week --> Downloads.

Am I on the right track? :P

Thanks again!

J'son