Advices for modeling facts, time hierarchies based on two conditions

Question

Sorry if my question does not belong to that SE but I tried to find the best place and it ended up here.

My problem is this :

I've got a table modeled as this in our datawarehouse env. :

MYTABLE (service_id, date_input, date_when_active, date_when_inactive, bunch_of_values...)

Each night this table is updated with new records from our prod env. When a record changes in prod and already exist in dw, a new record is created in dw with the same serviceid and the old record gets updated with sysdate in when_inactive field. The newly created record has its when_inactive field to null.

We use a query to extract data from this table using some conditions to get exactly the value of all services in a precise period. The query goes like this :

select *
from mytable
where service_date_input between :p_date_start and :p_date_end
and :p_date_end between date_when_active and nvl(date_when_inactive,:p_date_end)

What I would like to do is to model a time hierarchy in Cognos Framework Manager so I can drill through fiscal years, periods, weeks, months, etc.

What I can't figure out is if I need to remodel the table to include calculated fiscal_year_id, period_id, week_id, month_id, etc. directly in the DB or is it possible to do it directly in Cognos FWM?

One of the requirement would be to not force the use of two date sets on the user (filter by input date AND fiscal year). The input date should be filtered transparently : When you use a fiscal year parameter to filter or display the data, the input date rule should apply accordingly.

Neil McGuigan · Accepted Answer · 2013-07-03T22:08:40.977

You need a date dimension table, and it should look something like this:

create table calendar (
  date_id int primary key,
  name text not null unique,
  date_iso date unique,
  year smallint,
  quarter_of_year smallint,
  month_of_year smallint,
  day_of_year smallint,
  day_of_month smallint,
  fiscal_year smallint,
  quarter_of_fiscal_year smallint,
  month_of_fiscal_year smallint,
  ...

);

and insert data like this:

insert into calendar values 
(0, 'N/A', null, ...),
(20130703, '2013-07-03', '2013-07-03', 2013, 3, 7, 184, 3, ...),
(20130704, '2013-07-04', '2013-07-04', 2013, 3, 7, 185, 4, ...);

Notes:

use a smart key (with the date as the key, like 20130703) as the primary key for your date dimension, for easier fact table partitioning. this is the only time you should use a smart key in your warehouse. use meaningless surrogates otherwise.
use a text 'name' field to allow for 'N/A' dates
you should have ONE date dimension table and your fact table date keys should all point to it
you should not include times in your date dimension table. Consider a time dimension table if you need to track times, with say minute granularity.

Advices for modeling facts, time hierarchies based on two conditions

1 Answers1