Data warehouse support tickets I'm starting to design a data warehouse for a company. The first questions we're trying to resolve are regarding their support ticketing system. My initial schema is as follows



Now one of the questions we want to ask is historically how many tickets were active at anyone time.

The problem is that a ticket will be created one day but maybe open over several days / weeks / months without being updated or created again, meaning we only have one fact record when the ticket was created even though the ticket was opened every day.

I'm not sure what the best way of handling this is, a thought that's just come to me is this.

At the start of the day any ticket which hasn't been marked as resolved has another ticket entered into the fact table at the start of each day regardless of whether there's any updates? Does that seem like a sensible solution? Or am I missing something simpler?

Any feedback on the schema as it is would also be greatly appreciated, as we still have time to change it and get it right from the start.
You might want to decompose the support tickets fact table into transactions; User w on Date x moved ticket y to state z etc. This would facilitate metrics like "bounced tickets" etc. which service desk managers always seem to be keen on. This could be supplemented with the Accumulating Snapshot table you already have. Look here http://www.kimballgroup.com/2012/05/01/design-tip-145-time-stamping-accumulating-snapshot-fact-tables/ for a couple of options to implement this scenario.