MDX Calculating Time Between Events

Question

I have a Cube which draws its data from 4 fact/dim tables.

1. FactCaseEvents (EventID,CaseID,TimeID)
2. DimEvents (EventID, EventName)
3. DimCases (CaseID,StateID,ClientID)
4. DimTime (TimeID,FullDate)

Events would be: CaseReceived,CaseOpened,CaseClientContacted,CaseClosed

DimTime holds an entry for every hour.

I would like to write an MDX statement that will get me 2 columns: "CaseRecievedToCaseOpenedOver5" and "CaseClientContactedToCaseClosedOver5"

CaseRecievedToCaseOpenedOver5 would hold the number of cases that had a time difference over 5 hours for the time between CaseReceived and CaseOpened.

I'm guessing that "CaseRecievedToCaseOpenedOver5" and "CaseClientContactedToCaseClosedOver5" would be calculated members, but I need some help figuring out how to create them.

Thanks in advance.

Answer 1

This looks like a good place to use an accumulating snapshot type fact table and calculate the time it takes to move from one stage of the pipeline to the next in the ETL process.

Answer 2

Query for AdventureWorks (DateDiff works in MDX):

WITH 
MEMBER Measures.NumDays AS 
'iif(ISEMPTY(([Delivery Date].[Date].CurrentMember
,[Ship Date].[Date].CurrentMember
,Measures.[Order Count]))
,null
, Datediff("d",[Ship Date].[Date].CurrentMember.Name
,[Delivery Date].[Date].CurrentMember.Name))'
SELECT
NON EMPTY {[Ship Date].[Date].&[63]
:[Ship Date].[Date].&[92]} ON COLUMNS,
NON EMPTY {[Delivery Date].[Date].&[63]
:[Delivery Date].[Date].&[92]} 
* {[Measures].[NumDays]
, [Measures].[Order Count]} ON ROWS
FROM [Adventure Works]

Taken from: http://www.mombu.com/microsoft/sql-server-olap/t-can-i-have-datediff-in-mdx-265763.html

If you'll be using this member a lot, create it as a calculated member in the cube, on the Calculations tab if I remember right.