Hi, I'm new to cubes and MDX and have been struggling with this problem for a while now.
My fact table has the following columns (Start and End contain dates and times down to the minute level):
- ID (int)
- Start (DateTime)
- End (DateTime),
- plus other dimensions.
I've created a table called DimDateTime that has a sequence of 15 minute intervals over 20 years. I've set up this table as a dimension in a cube. A fact is said to be Active for a time T if Start <= T < End and it is Active for an interval T1 to T2 if T1 < End and Start <= T2.
I wish to define a measure called AverageActive which sums as normal over the other dimensions, but over the time dimension it should calculate as the average over each 15 minute interval in the time dimension. Any clues?
Another way of calculating this is to sum up for each fact the ratio Min(End, T2)-Max(Start, T1)/(End-Start), but I don't know how to do that in MDX either.
Any help would be greatly appreciated :)