Best Date Dimension design to sync DayNameOfWeek between several years

[marceSS]

Hi all,
the business department needs to compare daily data between last 3 years (N current year, N-1 last year....).

Before performing the comparison, days must be synchronised ( 20110101 was the Saturday of the first week of 2011, we need to get the Saturday of the first week of 2010 and it is 20100102 ).

What do you suggest as the best solution to satisfy this requirement?

1) manage it only on the BI side
2) add columns to the Date dimension (i.e adding the surrogate keys of the days of the precedent years)
3) as in solution 2 but using a sub dimension table only for this need
4) other

Thank you in advance for your help.
Marcello

[hkandpal]

Hi,

Adding columns to the Date dimension is better as you can join with the date dimension and have your output as required.

thanks

Himanshu

[nick_white]

Just to expand on Himanshu's reply, the attributes you need to add to the Date Dimension are the attributes of the date record in question, not FKs to some other date.
So you have attributes for Year, WeekOfYear, DayOfWeek and you' run queries such as:

Select ....
From Date_Dimension D...
Where d.Year in (2012, 2013, 2014) -- or some expression using SYSDATE that will dynamically give the last 3 years
and d.WeekOfYear = 1
and d.DayOfWeek = 'Saturday'
...
Group By d.Year...

[marceSS]

Thank you Himanshu and nick_white,

so probably I can use query like the following:

Select d.WeekOfYear,
d.DayOfWeek,
d.Year
.....
FROM Date_Dimension d
Where d.Year in (2012, 2013, 2014...)
GROUP BY
d.WeekOfYear,
d.DayOfWeek,
d.Year

I have still a doubt. I read my first post and I think it's not fully correct. Sorry for this.

When Synchronising date across two years, I have to get the closest date with the same DayOfWeek, but it can have a different WeekOfYear.
For example: 20120101 was Sunday with WeekOfYear 1, the closest Sunday in 2011 was 20110102 but with WeekofYear 2.

So using only WeekOfYear and DayOfWeek seems not to be sufficient. Am I correct?

[Sponsored content]