Calendar Dimension

While working on a data mart and reporting system for a project I needed a way of pulling data for specific time periods for reports. This was done by using a calendar dimension, which is a table that specifies time frames primarily for reporting purposes. The dimension can be setup for any level of granularity that one needs. Since my smallest reporting period was a single month I setup a table like:

CREATE TABLE calendar_dim(
calendar_id NUMBER,
year NUMBER,
quarter NUMBER,
month_number NUMBER,
month_name VARCHAR2,
month_abbr VARCHAR2 )

The calendar_dim table is then populated with the calendar data that you need like:

1, 2006, 1, 1, 'January', 'JAN'
2, 2006, 1, 2, 'February', 'FEB'

Each table that contains dated information has a calendar_id column like:

CREATE TABLE foo_bar(
calendar_id NUMBER NOT NULL,
foo VARCHAR,
bar NUMBER )

The calendar dimension can then be used to control the time frame that queries are run for. As an example the following query sums the value bar over the first quarter of the year 2006.

SELECT fb.foo, SUM(fb.bar)
FROM foo_bar fb, calendar_dim c
WHERE
fb.calendar_id = c.calendar_id AND
c.year = 2006 AND
c.quarter = 1
GROUP BY
fb.foo;