MDX:time between fatcs / time spent in a state

Hi all

I am an MDX newbie and it happens i have to do immediately with a query that seems (to me) pretty unorthodox.

The fact table i have is made up of the following entities: StateMachineId, StateId, TransitionDateTime.
In fact, i have several State Machines that can find themselves in different states and i log in a DB the time of their transitions and the new state.
I need to generate reports mostly based on the time spent in each state and i have no clue on what is the best way to do that in mdx.
In sql, what i did was to join this FactTable (FT1) with itself (FT2) ON StateMachineId adding the condition that FT2.TransitionDateTime BETWEEN FT1.TransitionDateTime AND FT1.TransitionDateTime + SomeTimeInterval , in this way i narroewed the size of the join. At this point, i took the row from FT2 with the lesser TransitionDateTime .
All this works perfectly, but it is akward and pretty slow. Another downside is that some longer transitions (those longer than SomeTimeInterval) are excluded.
I think working with MDX could be better but, as i stated before, i am new in this field.
Do you have some ideas to create a suitable cube for these queries?

Thank You So Much

Best regards

Wentu
It's me again

I found this SQL solution:

Code Snippet

with modnums as (
select *, row_number() over (order by StateMachineId, TransitionDateTime) as rn
from FactTable
)
select m_this.StateMachineId, m_this.TransitionDateTime, m_next.StateId, m_next.TransitionDateTime, m_this.StateId, datediff(ms,m_this.TransitionDateTime, m_next.TransitionDateTime) elapsedTime
from
modnums m_this
inner join
modnums m_next
on m_next.rn = m_this.rn + 1
and m_this.StateMachineId = m_next.StateMachineId

It's working very well (Thankx to Rob Farley for this) .
Now, i would like to use it in Analysis Services but the "OVER" is not allowed in Named Queries. Any Idea on how to write this query in MDX ?

Thankx again

Wentu