CODEWITHSUNDEEP
sqlumldata-warehousestar-schema
joris
joris

Reputation: 435

Attributes of my Time dimension table in star schema

I'm building a DW with a star schema modeling. I'll use it for a BI project with pentaho. I'll have of course a time dimension table. I'll analyze my fact table with differents granularity (day, week, month year, perhaps other)

Should I put one attribute for each of those granularity in my dimension table (so I have one day attribute, one month attribute, one year attribute ...) or should I just write the date and then calculate everything with this date (get the month of the date, the year of the date ...)?

thks a lot for your help

Upvotes: 3

Views: 3567

Answers (3)

Christopher Browne
Christopher Browne

Reputation: 66

Typically, the more, the merrier.

Here is an example I'm using...

ledger@localhost-> select * from date_dimension where date = '2015-12-25';

-[ RECORD 1 ]----+--------------------

date | 2015-12-25

year | 2015

month | 12

monthname | December

day | 25

dayofyear | 359

weekdayname | Friday

calendarweek | 52

formatteddate | 25. 12. 2015

quartal | Q4

yearquartal | 2015/Q4

yearmonth | 2015/12

yearcalendarweek | 2015/52

weekend | Weekday

americanholiday | Holiday

austrianholiday | Holiday

canadianholiday | Holiday

period | Christmas season

cwstart | 2015-12-21

cwend | 2015-12-27

monthstart | 2015-12-01

monthend | 2015-12-31 00:00:00

It's based on queries from the PostgreSQL wiki here... https://wiki.postgresql.org/wiki/Date_and_Time_dimensions

It would be interesting to augment this with further things:

  • Religious days (Easter, some of the numerous Saints' days, Ramadan, Jewish festivals, etc)

  • Statutory holidays for relevant jurisdictions. The firm I work for winds up publicizing Irish banking holidays because a number of the customers pay via bank transfers.

  • If you operate in France, you might want Lundi, Mardi, Mercredi, ... rather than English day names.

  • Daylight Savings Time (as true/false) would be a nice addition.

Upvotes: 0

Walter Mitty
Walter Mitty

Reputation: 18940

In addition to day, week, month, and year, you should think of other attributes like "company holiday", or "fiscal quarter". This can be an enormous resource for driving the same query off of different time windows.

Upvotes: 3

David Aldridge
David Aldridge

Reputation: 52376

I would add the attributes of the dates as their own columns. This does not take up significantly more space, and generally gives the query optimiser a better shot at working out how many of the dimension table records match a given criterion (for example, that the day_of_month = 31).

Upvotes: 2

Related Questions