I am really struggling with this problem that im sure others have come across (and hopefully solved without pulling their hair out!)
My cube measures cases and elapsed time between opening and resolving. This could be from a number of source systems, including workflow apps and helpdesk request logging.
Scenerio is simple, request comes in on one day and can be closed on same day or any subsiquent days. One of the requirements is to see the opening balance and any particular period. The opening balance is simply anything that was open
at that point
In sql this can be calculated by
where receiveddatekey <= @date
and (completeddatekey = 19000101 or
completeddatekey > @date)
But i am really struggling to get this logic in mdx so a calculated measure can be created to allow users to drag time dimension.
To allow this design work, i am pretty sure i will need to include in the fact an open date, close date and report date. Each time a new request comes in a new fact record will be created and (closed also if applicable). If request is not closed
on that day, the completeddatekey will be set to 19000101. On subsiquent days when the request is completed a further fact record will be completed with including all of the opening at
View Complete Post