Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

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.