Need Realtive Time dimension in DMR

[madhancog]

Hi Gurus,
  I am in need to get Rolling 12 months,Last 12 months,Last Quarter,current Quarter,Last YTD,upto Last full month in DMR model can i know how can i achieve this.

Thanks in advance
K.m

[blom0344]

AFAIK there are 2 approaches:

1. Create a table or view that stores the relative time information and is synchronized each night (either by ETL or by through the view definition)
(relative time demands refering to the systemdate, which makes an indexed view impossible)

2. Create seperate query subjects that store (through the query subjects filter) those date keys that relate to the portion of date/time that you need.
Join these query subjects to the fact.
Since you can only use one relative time dimension at a time this means different slices out of the fact through the join.

Next 2 year would be like:

Code Select Expand


extract(year,[time].[date]) between (extract(year,current_date)+1 and extract(year,current_date)+2)


[cognosjon]

Morning,
I used many years ago a document by Cognos Titled "Modeling Relative Time as Measures"

Its an old document but it may have some benefit for you.

http://www.ibm.com/developerworks/data/library/cognos/page90.html