I am usnig Analysis Services.

I have a time dimension (Year, Quarter, Month) that covers the last 4 years.
I wish to create a new dimension that is basically a filter of this time
dimension that always contains the last 12 months.

For example, if it is currently October 2004, this new dimension would
contain October 2003 to September 2004. As the months go by, I would like
this dimension to automatically roll forward to alway contain the last 12
months.

What is the best way to handle this?

Thank You.

Just expose your time dimension table through a view.
In the view, only return those rows with some criterion, such as:

create view time_dim (<col list>) as
select * from full_time_dim where date < dateadd(month, 12, getdate())

This gives you all the members whose date is earlier than 12 months from
"now" (into the future).
Then every month schedule an incremental update of the time dimension. Each
month new time members will appear.
--
Dave Wickert [MSFT]
dwickert (AT) online (DOT) microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.

"Jesse Drogin" <JesseDrogin (AT) discussions (DOT) microsoft.com> wrote