December 20, 2016 at 4:35 am
Hi all
We've built a date dimension which has a single date field followed by a lot of computed columns for working out a lot of other items (week end, week start, previous week/month stuff, etc.)
The table looks like this:-
CREATE TABLE [dbo].[Dim_Date](
[pkDateKey] [int] IDENTITY(1,1) NOT NULL,
[ActualDate] [date] NULL,
[DayNumber] AS (datepart(weekday,[ActualDate])),
[DayName] AS (case when [pkDateKey]=(-1) then 'Unknown' else datename(weekday,[ActualDate]) end),
[IsWeekend] AS (case when datepart(weekday,[ActualDate])=(7) OR datepart(weekday,[ActualDate])=(6) then 'Y' else 'N' end),
[DayNumberOfMonth] AS (datename(day,[ActualDate])),
[Ordinal] AS (CONVERT([char](2),case when datename(day,[ActualDate])/(10)=(1) then 'th' else case right(datename(day,[ActualDate]),(1)) when '1' then 'st' when '2' then 'nd' when '3' then 'rd' else 'th' end end)),
[FiscalWeekNumber] AS ((1)+datediff(week,dateadd(month,(3),CONVERT([char](4),case when datepart(month,[ActualDate])<(4) then datepart(year,[ActualDate])-(1) else datepart(year,[ActualDate]) end)),[ActualDate])),
[FiscalWeekDesc] AS (case when [pkDateKey]=(-1) then 'Unknown' else case when [pkDateKey]=(-1) then 'Unknown' else CONVERT([varchar],(1)+datediff(week,dateadd(month,(3),CONVERT([char](4),case when datepart(month,[ActualDate])<(4) then datepart(year,[ActualDate])-(1) else datepart(year,[ActualDate]) end)),[ActualDate]))+' wk' end end),
[CalendarMonthNumber] AS (datepart(month,[ActualDate])),
[FiscalMonthNumber] AS ((datepart(month,[ActualDate])+(9))-case when (datepart(month,[ActualDate])+(9))>(12) then (12) else (0) end),
[MonthName] AS (case when [pkDateKey]=(-1) then 'Unknown' else datename(month,[ActualDate]) end),
[MonthNameYear] AS (case when [pkDateKey]=(-1) then 'Unknown' else (datename(month,[ActualDate])+' ')+CONVERT([varchar](4),datepart(year,[ActualDate])) end),
[AbbrevMonthName] AS (case when [pkDateKey]=(-1) then 'Unknown' else case when [pkDateKey]=(-1) then 'Unknown' else CONVERT([varchar](3),datename(month,[ActualDate])) end end),
[YearNumber] AS (datepart(year,[ActualDate])),
[AbbrevMonthNameYear] AS (CONVERT([varchar](3),datename(month,[ActualDate]))+CONVERT([char](4),[ActualDate])),
[FiscalQuarter] AS (case when [pkDateKey]=(-1) then 'Unknown' else case when datepart(month,[ActualDate])>=(4) AND datepart(month,[ActualDate])<=(6) then 'Q1' when datepart(month,[ActualDate])>=(7) AND datepart(month,[ActualDate])<=(9) then 'Q2' when datepart(month,[ActualDate])>=(10) AND datepart(month,[ActualDate])<=(12) then 'Q3' when datepart(month,[ActualDate])>=(1) AND datepart(month,[ActualDate])<=(3) then 'Q4' end end),
[FiscalQuarterYear] AS (case when [pkDateKey]=(-1) then 'Unknown' else case when [pkDateKey]=(-1) then 'Unknown' else (case when datepart(month,[ActualDate])>=(4) AND datepart(month,[ActualDate])<=(6) then 'Q1' when datepart(month,[ActualDate])>=(7) AND datepart(month,[ActualDate])<=(9) then 'Q2' when datepart(month,[ActualDate])>=(10) AND datepart(month,[ActualDate])<=(12) then 'Q3' when datepart(month,[ActualDate])>=(1) AND datepart(month,[ActualDate])<=(3) then 'Q4' end+' - ')+case when datepart(month,[ActualDate])=(3) OR datepart(month,[ActualDate])=(2) OR datepart(month,[ActualDate])=(1) then (CONVERT([varchar],datepart(year,[ActualDate])-(1))+'/')+CONVERT([varchar],datepart(year,[ActualDate])) else (CONVERT([varchar],datepart(year,[ActualDate]))+'/')+CONVERT([varchar],datepart(year,[ActualDate])+(1)) end end end),
[CalendarQuarter] AS (case when [pkDateKey]=(-1) then 'Unknown' else case when datepart(month,[ActualDate])>=(1) AND datepart(month,[ActualDate])<=(3) then 'Q1' when datepart(month,[ActualDate])>=(4) AND datepart(month,[ActualDate])<=(6) then 'Q2' when datepart(month,[ActualDate])>=(7) AND datepart(month,[ActualDate])<=(9) then 'Q3' when datepart(month,[ActualDate])>=(10) AND datepart(month,[ActualDate])<=(12) then 'Q4' end end),
[CalendarQuarterYear] AS (case when [pkDateKey]=(-1) then 'Unknown' else case when [pkDateKey]=(-1) then 'Unknown' else (case when datepart(month,[ActualDate])>=(1) AND datepart(month,[ActualDate])<=(3) then 'Q1' when datepart(month,[ActualDate])>=(4) AND datepart(month,[ActualDate])<=(6) then 'Q2' when datepart(month,[ActualDate])>=(7) AND datepart(month,[ActualDate])<=(9) then 'Q3' when datepart(month,[ActualDate])>=(10) AND datepart(month,[ActualDate])<=(12) then 'Q4' end+' - ')+CONVERT([varchar],datepart(year,[ActualDate])) end end),
[MonthStart] AS (CONVERT([date],(CONVERT([datetime],[ActualDate])-datepart(day,[ActualDate]))+(1))),
[MonthEnd] AS (eomonth(CONVERT([date],(CONVERT([datetime],[ActualDate])-datepart(day,[ActualDate]))+(1)))),
[WeekStart] AS (CONVERT([date],CONVERT([date],(CONVERT([datetime],[ActualDate])-datepart(weekday,[ActualDate]))+(1)))),
[WeekEnd] AS (CONVERT([date],dateadd(day,(6),CONVERT([date],(CONVERT([datetime],[ActualDate])-datepart(weekday,[ActualDate]))+(1))))),
[PreviousWeekStart] AS (CONVERT([date],CONVERT([date],((CONVERT([datetime],[ActualDate])-datepart(weekday,[ActualDate]))-(7))+(1)))),
[PreviousWeekEnd] AS (CONVERT([date],CONVERT([date],dateadd(day,(6),CONVERT([date],((CONVERT([datetime],[ActualDate])-datepart(weekday,[ActualDate]))-(7))+(1)))))),
[PreviousMonthStart] AS (CONVERT([date],dateadd(month,(-1),(CONVERT([datetime],[ActualDate])-datepart(day,[ActualDate]))+(1)))),
[PreviousMonthEnd] AS (eomonth(CONVERT([date],dateadd(month,(-1),(CONVERT([datetime],[ActualDate])-datepart(day,[ActualDate]))+(1))))),
[FiscalYear] AS (case when [pkDateKey]=(-1) then 'Unknown' else case when datepart(month,[ActualDate])=(3) OR datepart(month,[ActualDate])=(2) OR datepart(month,[ActualDate])=(1) then (CONVERT([varchar],datepart(year,[ActualDate])-(1))+'/')+CONVERT([varchar],datepart(year,[ActualDate])) else (CONVERT([varchar],datepart(year,[ActualDate]))+'/')+CONVERT([varchar],datepart(year,[ActualDate])+(1)) end end),
[CalendarISOWeek] AS (datepart(iso_week,[ActualDate])),
[CalendarMonthNumberYear] AS (case when [pkDateKey]=(-1) then 'Unknown' else (right(CONVERT([varchar](2),'00')+CONVERT([varchar](2),datepart(month,[ActualDate])),(2))+'-')+CONVERT([varchar](4),datepart(year,[ActualDate])) end),
[FiscalMonthNumberYear] AS (case when [pkDateKey]=(-1) then 'Unknown' else (right(CONVERT([varchar](2),'00')+CONVERT([varchar](2),(datepart(month,[ActualDate])+(9))-case when (datepart(month,[ActualDate])+(9))>(12) then (12) else (0) end),(2))+'-')+case when datepart(month,[ActualDate])=(3) OR datepart(month,[ActualDate])=(2) OR datepart(month,[ActualDate])=(1) then (CONVERT([varchar],datepart(year,[ActualDate])-(1))+'/')+CONVERT([varchar],datepart(year,[ActualDate])) else (CONVERT([varchar],datepart(year,[ActualDate]))+'/')+CONVERT([varchar],datepart(year,[ActualDate])+(1)) end end),
[IsCurrentDate] AS (case when [ActualDate]=CONVERT([date],getdate()) then 'Yes' else 'No' end),
[IsFutureDate] AS (case when [ActualDate]>CONVERT([date],getdate()) then 'Yes' else 'No' end),
[IsYesterday] AS (case when [ActualDate]=CONVERT([date],dateadd(day,(-1),getdate())) then 'Yes' else 'No' end),
[IsCurrentCalendarYear] AS (case when datepart(year,[ActualDate])=datepart(year,getdate()) then 'Yes' else 'No' end),
[IsCurrentFiscalYear] AS (case when case when datepart(month,[ActualDate])=(3) OR datepart(month,[ActualDate])=(2) OR datepart(month,[ActualDate])=(1) then (CONVERT([varchar],datepart(year,[ActualDate])-(1))+'/')+CONVERT([varchar],datepart(year,[ActualDate])) else (CONVERT([varchar],datepart(year,[ActualDate]))+'/')+CONVERT([varchar],datepart(year,[ActualDate])+(1)) end=case when datepart(month,getdate())=(3) OR datepart(month,getdate())=(2) OR datepart(month,getdate())=(1) then (CONVERT([varchar],datepart(year,getdate())-(1))+'/')+CONVERT([varchar],datepart(year,getdate())) else (CONVERT([varchar],datepart(year,getdate()))+'/')+CONVERT([varchar],datepart(year,getdate())+(1)) end then 'Yes' else 'No' end),
[IsCurrentMonth] AS (case when datepart(year,[ActualDate])=datepart(year,getdate()) AND datepart(month,[ActualDate])=datepart(month,getdate()) then 'Yes' else 'No' end),
[IsCurrentFlexMonth] AS (case when CONVERT([date],(CONVERT([datetime],[ActualDate])-datepart(day,[ActualDate]))+(1))=[dbo].[fn_Flex_Start_Date]() then 'Yes' else 'No' end),
[IsCurrentFreezeMonth] AS (case when CONVERT([date],(CONVERT([datetime],[ActualDate])-datepart(day,[ActualDate]))+(1))=[dbo].[fn_Freeze_Start_Date]() then 'Yes' else 'No' end),
[IsPreviousCalendarYear] AS (case when datepart(year,[ActualDate])=(datepart(year,getdate())-(1)) then 'Yes' else 'No' end),
[IsPreviousFiscalYear] AS (case when case when datepart(month,[ActualDate])=(3) OR datepart(month,[ActualDate])=(2) OR datepart(month,[ActualDate])=(1) then (CONVERT([varchar],datepart(year,[ActualDate])-(1))+'/')+CONVERT([varchar],datepart(year,[ActualDate])) else (CONVERT([varchar],datepart(year,[ActualDate]))+'/')+CONVERT([varchar],datepart(year,[ActualDate])+(1)) end=case when datepart(month,dateadd(month,(-12),getdate()))=(3) OR datepart(month,dateadd(month,(-12),getdate()))=(2) OR datepart(month,dateadd(month,(-12),getdate()))=(1) then (CONVERT([varchar],datepart(year,dateadd(month,(-12),getdate()))-(1))+'/')+CONVERT([varchar],datepart(year,dateadd(month,(-12),getdate()))) else (CONVERT([varchar],datepart(year,dateadd(month,(-12),getdate())))+'/')+CONVERT([varchar],datepart(year,dateadd(month,(-12),getdate()))+(1)) end then 'Yes' else 'No' end),
[IsPreviousMonth] AS (case when datepart(year,[ActualDate])=datepart(year,dateadd(month,(-1),getdate())) AND datepart(month,[ActualDate])=datepart(month,dateadd(month,(-1),getdate())) then 'Yes' else 'No' end),
[IsPastYear] AS (case when [ActualDate]>=dateadd(day,(-365),CONVERT([date],getdate())) AND [ActualDate]<=CONVERT([date],getdate()) then 'Yes' else 'No' end),
[IsPast28Days] AS (case when [ActualDate]>=dateadd(day,(-28),CONVERT([date],getdate())) AND [ActualDate]<=CONVERT([date],getdate()) then 'Yes' else 'No' end),
[IsMeditechLive] AS (case when [ActualDate]>='01/06/2012' then 'Yes' else 'No' end),
[IsFirstDayOfMonth] AS (case when datename(day,[ActualDate])=(1) then 'Yes' else 'No' end),
[IsLastDayOfMonth] AS (case when [ActualDate]=eomonth([ActualDate]) then 'Yes' else 'No' end),
[IsLeapYear] AS (case when datepart(year,[ActualDate])%(4)=(0) then 'Yes' else 'No' end),
[IsMonthStart] AS (case when [ActualDate]=CONVERT([date],(CONVERT([datetime],[ActualDate])-datepart(day,[ActualDate]))+(1)) then 'Yes' else 'No' end),
[IsMonthEnd] AS (case when [ActualDate]=eomonth(CONVERT([date],(CONVERT([datetime],[ActualDate])-datepart(day,[ActualDate]))+(1))) then 'Yes' else 'No' end),
CONSTRAINT [PK_Dim_Date2] PRIMARY KEY CLUSTERED
(
[pkDateKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
As you can see, most of the columns are computed.
We also have a record in there with a key (pkDateKey) of -1 which is a dummy record for any dates not in the table.
When I import this into a cube and try to process it, I get the following errors:-
Errors in the OLAP storage engine: A duplicate attribute key has been found when processing: Table: 'dbo_Dim_Date', Column: 'PreviousWeekEnd', Value: '00:00:00'. The attribute is 'Previous Week End'. Errors in the OLAP storage engine: The record was skipped because the attribute key is a duplicate. Attribute: Previous Week End of Dimension: Dim Date from Database: DataWarehouseCubes, Record: 10465.
Errors in the OLAP storage engine: A duplicate attribute key has been found when processing: Table: 'dbo_Dim_Date', Column: 'WeekEnd', Value: '00:00:00'. The attribute is 'Week End'. Errors in the OLAP storage engine: The record was skipped because the attribute key is a duplicate. Attribute: Week End of Dimension: Dim Date from Database: DataWarehouseCubes, Record: 9884.
As you can see the values are times but, assuming the record numbers equate to the primary key number, the dates for those are:-
9984 - 22/01/1877
10465 - 26/08/1878
Both of the above fields are being classed as dates in all the data-types I've found.
Anyone any ideas?
December 20, 2016 at 9:22 am
Unless the computed columns in the table are defined as "PERSISTED", then this table is as bad or worse than using a view to create the same thing.
I'll also state that table is probably massive overkill. Seriously. For example, do you actually need a column for whether or not a year is a Leap Year or not?
And things that change every bloody day, like the IsCurrentCalendarYear, column, is a real killer that cannot be persisted and will continue to be a performance problem because the whole column must be recalculated every time someone uses the column in a criteria or SELECT list.
This is actually one of the worse designed attempts at a Calendar table I've ever seen and is in desperate need of some sane modification.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 20, 2016 at 9:35 am
Hi Jeff
Unless the computed columns in the table are defined as "PERSISTED", then this table is as bad or worse than using a view to create the same thing.
None of the columns are persisted (yet), this was a rough draft just to see what the cube performance would be like.
I'll also state that table is probably massive overkill. Seriously. For example, do you actually need a column for whether or not a year is a Leap Year or not?
Bizarrely, yes. It's one we've specifically been asked for from a reporting point of view.
It's the same with IsCurrentCalendarYear, etc. I'll agree that some of this is pure laziness from the recipients point of view (how hard can it be to change a date criteria in a cube extract put in Excel???).
And things that change every bloody day, like the IsCurrentCalendarYear, column, is a real killer that cannot be persisted and will continue to be a performance problem because the whole column must be recalculated every time someone uses the column in a criteria or SELECT list.
Agreed, although the performance doesn't seem to be a problem ..... yet.
This is actually one of the worse designed attempts at a Calendar table I've ever seen and is in desperate need of some sane modification.
I'd welcome any ideas you have for improvement.
December 20, 2016 at 1:43 pm
richardmgreen1 (12/20/2016)
Hi JeffUnless the computed columns in the table are defined as "PERSISTED", then this table is as bad or worse than using a view to create the same thing.
None of the columns are persisted (yet), this was a rough draft just to see what the cube performance would be like.
I'll also state that table is probably massive overkill. Seriously. For example, do you actually need a column for whether or not a year is a Leap Year or not?
Bizarrely, yes. It's one we've specifically been asked for from a reporting point of view.
It's the same with IsCurrentCalendarYear, etc. I'll agree that some of this is pure laziness from the recipients point of view (how hard can it be to change a date criteria in a cube extract put in Excel???).
And things that change every bloody day, like the IsCurrentCalendarYear, column, is a real killer that cannot be persisted and will continue to be a performance problem because the whole column must be recalculated every time someone uses the column in a criteria or SELECT list.
Agreed, although the performance doesn't seem to be a problem ..... yet.
This is actually one of the worse designed attempts at a Calendar table I've ever seen and is in desperate need of some sane modification.
[font="Arial Black"]I'd welcome any ideas you have for improvement.[/font]
PERSIST the columns that you can, see if you can change the column code for those you can't persist from non-deterministic to deterministic and then change them to PERSISTED, and remove the ones that you cannot persist or convert them to hard-populated columns in the table.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 21, 2016 at 2:11 am
Thanks Jeff, I'll have a look at that little lot.
There will be a couple of columns I can't make deterministic due to their nature (i.e. the "Is" flags) but I can't do a lot about that.
The only way I could do that is to reload the table on a daily basis.
December 21, 2016 at 7:39 am
richardmgreen1 (12/21/2016)
Thanks Jeff, I'll have a look at that little lot.There will be a couple of columns I can't make deterministic due to their nature (i.e. the "Is" flags) but I can't do a lot about that.
The only way I could do that is to reload the table on a daily basis.
Or build an inline Table Valued Function (iTVF) that does the trick as if it were a scalar function for folks to use.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply