Infobright Blog

An Infobright evaluator recently asked, "What is the best way of defining date dimension using Infobright?" on our Community forum here http://www.infobright.org/Forums/viewthread/1506/ . In reality, and as you can read in my response, the question of designing a time (or date) dimension is generic to data warehousing.

For over 15 years I have advocated creating the most robust time dimension possible. In my experience, the time dimension is the most heavily elaborated dimension in the data model in order to eliminate as much effort on the database or business intelligence server as possible while processing user queries. Have as many columns of as many variations of the date and its derivatives in as many data types as necessary/possible. It is not unusual to have several 10’s of different explicit representations for each time element.

This is a critical dimensional table and should be as detailed as possible. Your guide should be the queries themselves and what they need. Don’t force any functions or translations onto your tool or queries - perform the function or translation once and save it in the time dimension. That means if one tool or user needs ‘month’ as an integer, save it in an integer column/data type. If another needs the numeric value for ‘month’ represented as a text value, save it again in a CHAR(2) field. It doesn’t matter - in a traditional database or Infobright. There are only 366 days in a year, maximum. For a date dimension with even 100 years’ worth of data, that’s only 36,525 records. And in Infobright, that’s less than one Data Pack (a Data Pack in Infobright contains 65,536 elements) for an egregiously deep time dimension. With compression, it’s nothing in total disk space.

For example, consider the following time dimension DDL as a ** starting point **.

CREATE TABLE dim_time (
date_id            SMALLINT,                      -- surrogate key
date               DATE,                          -- YYYY-MM-DD
date_as_text       VARCHAR(25),                   -- "January 31, 2010"
day_of_week        TINYINT,                       -- 1 through 7
day_of_month       TINYINT,                       -- 1 through 31
day_of_period      TINYINT,                       -- 1 through ...
day_of_quarter     TINYINT,                       -- 1 through 90(ish)
day_of_year        SMALLINT,                      -- 1 through 366
day_of_week_text   VARCHAR(10) COMMENT 'lookup',  -- e.g., "Monday"
week_in_month      TINYINT,                       -- 1 through 5
week_in_period     TINYINT,                       -- 1 through ...
week_in_qtr        TINYINT,                       -- 1 through 13 (or 14)
week_in_year       TINYINT,                       -- 1 through 52 (or 53)
short_day_desc     CHAR(3)     COMMENT 'lookup',  -- e.g., "MON"
long_day_desc      VARCHAR(10) COMMENT 'lookup',  -- e.g., "Monday"
month_num          TINYINT,                       -- 1 through 12
month_num_text     CHAR(2)     COMMENT 'lookup',  -- e.g., "01"
short_month_name   CHAR(3)     COMMENT 'lookup',  -- e.g., "JAN"
long_month_name    VARCHAR(10) COMMENT 'lookup',  -- e.g., "January"
qtr_num            TINYINT,                       -- 1 through 4
qtr_text           CHAR(2)     COMMENT 'lookup',  -- e.g., "01"
period_num         TINYINT,                       -- 1 through ...
period_text        CHAR(1),                       -- "1" through ...
year_num           SMALLINT,                      -- e.g., 2010
year_text          CHAR(4)     COMMENT 'lookup',  -- e.g., "2010"
year_2_digits_num  TINYINT,                       -- e.g., 10
year_2_digits_text CHAR(2)     COMMENT 'lookup',  -- e.g., "10"
holiday            CHAR(1),                       -- "Y" or "N"
national_holiday   CHAR(1),                       -- "Y" or "N"
religious_holiday  CHAR(1),                       -- "Y" or "N"
state_holiday      CHAR(1),                       -- "Y" or "N"
weekend            CHAR(1),                       -- "Y" or "N"
3_day_weekend      CHAR(1),                       -- "Y" or "N"
4_day_weekend      CHAR(1)                        -- "Y" or "N"
-- etc.
) ENGINE=BRIGHTHOUSE
;

And these are just calendar attributes. Fiscal period attributes, business calendar attributes, marketing calendar attributes, etc. all exist and cannot be determined by SQL functions. You must "bake" them into the time dimension. Once. You can create any number of years' worth of time dimension values with data generation tools like those available from vizubi.com or datamelt.com or with a variety of functions in your spreadsheet of choice, such as Microsoft Excel, OpenOffice.org Calc,Lotus 1-2-3. These are just some of the many options avaiable.

With this approach you can return whatever value, string or concatenation you need from the time dimension without adversely affecting performance for the database server or the query/reporting platform.