Re: extract(field from timestamp) vs date dimension

On 1/23/07, Tobias Brox <tobias(at)nordicbet(dot)com> wrote:
>
> Ralph Kimball seems to be some kind of guru on data warehousing, and
> in his books he's strongly recommending to have a date dimension -
> simply a table describing all dates in the system, and having

I would tend to agree with this line of thought.

out from elsewhere - but as for now, I'm mostly only interessted in
> grouping turnover/profit by weeks/months/quarters/years/weekdays. It
> seems so much bloated to store this information, my gut feeling tells it
> should be better to generate them on the fly. Postgres even allows to
> create an index on an expression.

I guess go with your gut, but at some point the expressions are going to be
too complicated to maintain, and inefficient.

Calendar tables are very very common, because traditional date functions
simply can't define business logic (especially things like month end close,
quarter end close, and year end close) that doesn't have any repeating
patterns (every 4th friday, 1st monday in the quarter, etc). Sure you can
stuff it into a function, but it just isn't as maintainable as a table.

--
Chad
http://www.postgresqlforums.com/