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.