Best Date Dimension design to sync DayNameOfWeek between several years
3 posters
Page 1 of 1
Best Date Dimension design to sync DayNameOfWeek between several years
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
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
Re:Best Date Dimension design to sync DayNameOfWeek between several years
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
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
Re: Best Date Dimension design to sync DayNameOfWeek between several years
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...
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 : 364
Join date : 2014-01-06
Location : London
Re: Best Date Dimension design to sync DayNameOfWeek between several years
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?
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
Similar topics
» Date Dimension: Representing partial dates/Imputing date values
» Always link date fields to Date Dimension?
» Eliminate Date Dimension Surrogate Key
» Using the Date Dimension for Summary Fact Tables or new specialized Month Dimension?
» Question about using date dimension keys in other dimension tables
» Always link date fields to Date Dimension?
» Eliminate Date Dimension Surrogate Key
» Using the Date Dimension for Summary Fact Tables or new specialized Month Dimension?
» Question about using date dimension keys in other dimension tables
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum
|
|