The data we have gets grouped into report month and year, 04 2006 for
example. The reporting we do is based on rolling periods.

For example, the report period is 200605 but we do 3 compute periods; 1
month, 3 month rolling, and 12 month rolling.

So for May 2006 it breaks down as the following:

1 mo is May 2006, 3 month has report periods of 2006 03 through 2006
05, and 12 month has 2005 06 through 2005 05.

The other trick to this is that our hierarchy can change from month to
month, meaning an entity can switch what parent he belongs too. For
historical purposes, we need to preserve that hierarchy. For example,
when I look a January 2006 scores, I need to see an entity in the
parent it was during January 2006, not what it is today.

What I started with was a just the hierarchy. This worked out well and
matched what would be the 12 month rolling scores. I then created a
hierarchy with durations (what I call a combination of the report
period and computer period). The duration is connected to the fact
table via another look up table that has which report periods are in
which durations.

When I process my cube, I do not get a breakdown by time like I
expected. When expand the dimension, each of my durations shows the
same numbers.

How can I create a dimension or set of dimensions that will all me to
do "time" slicing on a rolling scale and also be able to preserve
the hierarchy?

Can anybody point me the in the right direction?

We have similar requirements here. We have to maintain history on an employee
hierarchy. Meaning that when someone changes bosses we keep track of their
old and new reporting structure. It's a little complicated but we've made it
work so far. We don't have the rolling periods you have but I think that can
be handled. Is your hierarchy setup as parent-child?

Ken

"bundy" wrote:

Well I have done a few different things just trying to make things
work. Currently, I have a flat table, and populated with all the
levels and then i created the heiarchy off of that. Our source data
really is a vertical table with a parent.

Have you considered implementing the compute periods as Time
Intelligence MDX calculations (assuming that you're using AS 2005 -
though a similar approach can be implemented in AS 2000, using an
analytical time dimension)? The default member could be the current
month, with calculated members for the past 3 and the past 12 monhs.

http://msdn2.microsoft.com/en-us/lib...0(SQL.90).aspx
Wizard

The time intelligence enhancement is a cube enhancement that adds time
calculations (or time views) to a selected hierarchy. This enhancement
supports the following categories of calculations:

Period to date.

Period over period growth.

Moving averages.

Parallel period comparisons.

You apply time intelligence to cubes that have a time dimension. (A time
dimension is a dimension whose Type property is set to Time).
Additionally, the time attributes of that dimension must also have the
appropriate setting (such as, Years or Months) for their Type property.
The Type property of both the dimension and its attributes will be set
correctly if you use the Dimension Wizard to create the time dimension.
...

If you use the Time Intelligence Wizard, this support article may be
helpful:

http://support.microsoft.com/Default.aspx?kbid=912136


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***