The infamous Date Table
-
As an intern, I've been tasked with "maintaining" a SS*S-based system. First task I get:
"So, there's this DimDate table which we need for multidimensional analysis, and it's running out. Could you write a script which adds some more rows?"
Okay, I assume optimistically that there's no better way and fire up the VM with the database and check out the code. Just one look tells me it's not gonna be pretty:
- There are 14 columns to the table.
- Most of them make zero fucking sense.
- There's an int key column which is basically year10000 + month100 + day.
- There's a char(10) year column storing a year. We're prepared for Y1B, baby!
- The "Month" column stores a datetime with month and year set, and the rest at default
- There are "MonthSort" and "WeekSort" int columns which are year*100 + month/year. The DaySort column, OTOH, is year+day.
- Month and day-of-week names are somehow capitalized, which is not the default SQL Server output.
- The whole table starts on Monday, despite 2006-01-01 (the earliset entry) being Sunday.
Okay. I whip the script, whip another script to fix the day-of-week issue, and then think to myself Shit, why am I doing this? The table is already populated, there must be some script in place already? A bit of searching and well, there it is:
IF NOT EXISTS (SELECT 1 FROM DimDate WHERE TimeKey =20060101) INSERT [dbo].[DimDate] ([TimeKey], [YearLabel], [QtrLabel], [MonthLabel], [WeekLabel], [DayLabel], [MonthSort], [WeekSort], [DaySort], [DayofWeekNum], [DayofWeek], [Month], [TimeDate], [DateNameLabel]) VALUES (20060101, N'2006 ', N'Q1-2006', N'Styczeń 2006', N'Tydzień 01 2006', N'2006-01-01', 200601, 200601, 2007, 1, N'Poniedziałek', CAST(0x0000973C00000000 AS DateTime), CAST(0x0000973C00000000 AS DateTime), N'2006-01-01 ')``` Almost 4000 rows of this thing take up pretty much the whole of post-deployment script.
-
What is a ziałek and why has it been ponied?
Anyway, did you find the script-generating script?
-
What is a ziałek and why has it been ponied?
"Poniedziałek" = Monday. We Poles are wacky like that.
Anyway, did you find the script-generating script?
Not in the source control. I just hope it actually did exist at some point...
-
Zecc said:
What is a ziałek and why has it been ponied?"Poniedziałek" = Monday. We Poles are wacky like that.
Zecc just didn't break it down far enough. A zia is a spirit symbol used by some Pueblo Indians (there's one on the state flag of New Mexico). And a lek is either the national currency of Albania, or a mating strategy where groups of male animals gather in one place to performing their courtship displays for the females who then select the one they want to make bouncy-bouncy with (sort of like "The Bachelorette" or the old "Dating Game", except with birds or lizards).
-
So, a mating ritual involving Indian spirits and ponies. Fair enough.
Filed under: there's probably a MLP fanfiction of that already
-
zia[b]ł[/b]ek
And a [b]l[/b]ek is either the national currency of Albania, or a mating strategy
Does the funny 'l' have any effect in the context of the mating strategy?
-
I've been tasked with "maintaining" a SS*S-based system.
SpectateSwamp Everything Search? He's actually outsourcing now?
Filed Under: we need a new tag cloud, to attack
-
I've seen this far too often, where a function with proper date calculations (if those even exist) would suffice until the end of time.
-
SpectateSwampDumpsterSearch.
-
the old "Dating Game", except with birds
Ah, you mean the short-lived TV show "The Dating Game Birds" where partridges had to find a mate?
-
I'm sure Date would disapprove. Kimball would be ecstatic, however.
I can appreciate the value of a star schema when:
- The fact table is billions of rows
- The dimension tables are sensible
This just looks like it facilitates reporting rather than doing anything useful for query performance, and now I'm afraid to ask how one even joins this table (TimeKey? That kills their Y1B readiness!).
-
Does the funny 'l' have any effect in the context of the mating strategy?
It implies a w-strategy instead of an L-strategy.
