Time Periods

I've written before about populating your TIME dimension in the datawarehouse. What about a simple normalized table in your OLTP environment?

PERIODS table to the rescue.

I didn't invent this of course, I learned it from my first boss. I'm not sure whether he came up with the idea or found it on his own.

Disclaimers aside, the PERIODS table allows you to build dynamic reports without having to resort to hard coding or the like. The concept is simple, there are periods of time (PERIOD_TYPE_CODE) like Day, Week, Month Quarter and Year, and you just map the from date and the thru date.

The tables are defined as follows:

CREATE TABLE period_types
(
  periodtypecode VARCHAR2(20)
    CONSTRAINT pk_periodtypecode PRIMARY KEY
);


CREATE TABLE periods
(
  periodid NUMBER(12)
    CONSTRAINT pk_periodid PRIMARY KEY,
  date_from DATE,
  date_thru DATE,
  periodtypecode 
    CONSTRAINT fk_periodtypecode_periods 
      REFERENCES period_types( periodtypecode )
);

INSERT statements for PERIOD_TYPES:

INSERT INTO period_types( periodtypecode ) VALUES ( 'DAY' );
INSERT INTO period_types( periodtypecode ) VALUES ( 'WEEK' );
INSERT INTO period_types( periodtypecode ) VALUES ( 'MONTH' );

Now we need to populate the PERIODS table. First up, DAYS:

INSERT INTO periods
  ( periodid,
    date_from,
    date_thru,
    periodtypecode )
SELECT
    periods_seq.nextval,
    startdate,
    enddate,
    'DAY'
FROM 
  ( 
    SELECT
      TO_DATE( '31-DEC-99', 'DD-MON-YY' ) + rownum startdate,
      TO_DATE( '31-DEC-99 23:59:59', 'DD-MON-YY HH24:MI:SS' ) + rownum enddate
    FROM dual
      CONNECT BY level <= 10000
  );

This will give you 10,000 records starting on January 1, 2000.

WEEKs

INSERT INTO periods
  ( periodid,
    date_from,
    date_thru,
    periodtypecode )
SELECT
    periods_seq.nextval,
    startdate,
    enddate + 6,
    'WEEK'
FROM 
  ( 
    SELECT
      TO_DATE( '31-DEC-00', 'DD-MON-YY' ) + rownum startdate,
      TO_DATE( '31-DEC-00 23:59:59', 'DD-MON-YY HH24:MI:SS' ) + rownum enddate
    FROM dual
      CONNECT BY level <= 10000
  )
WHERE TO_CHAR( startdate, 'D' ) = 1;

To generate the WEEKs data, you could simply use the records created for DAY (like I did with MONTHs below), but I was lazy.

Finally, MONTHs:

INSERT INTO periods
  ( periodid,
    date_from,
    date_thru,
    periodtypecode )
SELECT
    periods_seq.nextval,
    date_from,
    ADD_MONTHS( date_from, 1 ) - ( 1 / 86400 ),
    'MONTH'
FROM periods
WHERE periodtypecode = 'DAY'
  AND TO_CHAR( date_from, 'DD' ) = 1;

OK, so how do you use this?

Simple, wherever you have a query that uses time as a driver or component, you simple do WHERE time_column BETWEEN date_from AND date_thru. What that will do is generate a record for every period type that it falls in. So given one record in your driving table, you'll get three records back when joined to the PERIODS table. One for DAY, one for WEEK and one for MONTH. Now your users can look at their data in a couple of different dimensions and best of all, no more hardcoding!

How does this differ from the TIME dimension?

The TIME dimension is a de-normalized table with TRUNC( date_of ) (or a surrogate, but I'm not getting into that debate here) as the key. In that record you will have different views of that time like what month it fell in, what quarter, what year, etc. You'll get one row back given the query above but you'll be able to view all the different time dimensions from there.