MDX Calculation of time between events

Question

I have a cube that retrieves data from 4 facts / dull tables.

• FactCaseEvents (EventID,CaseID,TimeID)
• DimEvents (EventID, EventName)
• DimCases (CaseID,StateID,ClientID)
• DimTime (TimeID,FullDate)

Developments: CaseReceived,CaseOpened,CaseClientContacted,CaseClosed

DimTime contains a record for every hour.

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

CaseRecievedToCaseOpenedOver5will contain the number of cases that had a time difference of 5 hours between CaseReceivedand CaseOpened.

I assume that " CaseRecievedToCaseOpenedOver5" and " CaseClientContactedToCaseClosedOver5" will be calculated by members, but I need help on how to create them.

Thanks in advance.

Answer 1

, ETL.

Answer 2

AdventureWorks (DateDiff 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]

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

, "", .