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
when_inactive field. The newly created record has its
when_inactive field to
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
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.