Counting members within timerange

Hi,

I want to analyse support tickets... They are opened at a specific time and closed at a specific time... The question now is: How many support tickets are open at a specific date... Or better: I want to see a statistic for each day how many tickets were open on that day...

Besides thinking about some very fancy MDX stuff I only can imagine to build up a special fact table in my ETL process which builds a record per day per open ticket... That's also not nice...

Any other ideas?

Thanks,

Hi Thomas,

It's one of those problems that's easier in SQL than MDX. You could take an approach similar to the one I describe here:

http://cwebbbi.spaces.live.com/blog/cns!7B84B0F2C239489A!113.entry

...although in this case it's not just a simple sum. You would have a Start Date and an End Date dimension, then sum the set of all tickets which have a) start dates between the currentmembers on Start Date and End Date, or b) end dates between the currentmembers on Start Date and End Date, or c) start dates before the currentmember on Start Date and end dates after the currentmember on End Date (I think). Performance might be a problem, but I'd still prefer to do this than blow up the fact table in the ETL.

Regards,

Chris

|||

You might find this entry in Mosha's blog interesting as well:

Counting 'in-flight' events in MDX

Time dimension has special meaning in OLAP and DW. The classic problems involve looking at previous period, parallel period, computing moving averages and running sums. Today we will look into less common, but nevertheless interesting problem of 'in-flight' events. This scenario arises when there is more than one date/time attribute associated with the fact.

...

|||

...interesting read... Thanks for the link!