Best Date Dimension design to sync DayNameOfWeek between several years

View previous topic View next topic Go down

Best Date Dimension design to sync DayNameOfWeek between several years

Post  marceSS on Wed Sep 17, 2014 12:24 pm

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

marceSS

Posts : 2
Join date : 2014-09-17

View user profile

Back to top Go down

Re:Best Date Dimension design to sync DayNameOfWeek between several years

Post  hkandpal on Wed Sep 17, 2014 1:15 pm

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

hkandpal

Posts : 113
Join date : 2010-08-16

View user profile

Back to top Go down

Re: Best Date Dimension design to sync DayNameOfWeek between several years

Post  nick_white on Wed Sep 17, 2014 1:32 pm

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...

nick_white

Posts : 366
Join date : 2014-01-06
Location : London

View user profile

Back to top Go down

Re: Best Date Dimension design to sync DayNameOfWeek between several years

Post  marceSS on Wed Sep 17, 2014 2:47 pm

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?

marceSS

Posts : 2
Join date : 2014-09-17

View user profile

Back to top Go down

Re: Best Date Dimension design to sync DayNameOfWeek between several years

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum