Data warehouse support tickets

Question

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.

Answer 1

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.

Answer 2

To know how many tickets are active on a particular day, you need to know both when a ticket first became active and when it was closed. Your design already contains this data, so the count can be generated without adding any data. This can be done by calculating the number if days between the open and close and then joining that with a data set large enough to accommodate your longest open duration. Here is an example in Oracle doing something conceptually similar.

DROP TABLE Tickets;
CREATE TABLE Tickets (ID Number(10), CreateDate Date, CloseDate Date);
INSERT INTO Tickets VALUES (1, to_date('12/30/2012','MM/DD/YYYY')
   , to_date('12/31/2012','MM/DD/YYYY'));
INSERT INTO Tickets VALUES (2, to_date('12/30/2012','MM/DD/YYYY')
   , to_date('01/05/2013','MM/DD/YYYY'));
INSERT INTO Tickets VALUES (3, to_date('12/30/2012','MM/DD/YYYY')
   , NULL);
INSERT INTO Tickets VALUES (4, to_date('12/31/2012','MM/DD/YYYY')
   , to_date('01/01/2013','MM/DD/YYYY'));
INSERT INTO Tickets VALUES (5, to_date('12/31/2012','MM/DD/YYYY')
   , NULL);
INSERT INTO Tickets VALUES (6, to_date('12/31/2012','MM/DD/YYYY')
   , NULL);
INSERT INTO Tickets VALUES (7, to_date('01/01/2013','MM/DD/YYYY')
   , to_date('01/20/2013','MM/DD/YYYY'));
INSERT INTO Tickets VALUES (8, to_date('01/01/2013','MM/DD/YYYY')
   , to_date('01/02/2013','MM/DD/YYYY'));
INSERT INTO Tickets VALUES (9, to_date('01/01/2013','MM/DD/YYYY')
   , NULL);
INSERT INTO Tickets VALUES (10, to_date('01/01/2013','MM/DD/YYYY')
   , NULL);
INSERT INTO Tickets VALUES (11, to_date('01/01/2013','MM/DD/YYYY')
   , NULL);
INSERT INTO Tickets VALUES (12,to_date('01/01/2013','MM/DD/YYYY')
   , to_date('01/04/2013','MM/DD/YYYY'));
INSERT INTO Tickets VALUES (13, to_date('01/02/2013','MM/DD/YYYY')
   , to_date('01/20/2013','MM/DD/YYYY'));
COMMIT;

SELECT ActiveDate, count(*) FROM
(
   SELECT ID, CreateDate, CreateDate-1+x ActiveDate, NVL(CloseDate,TRUNC(sysdate)) CloseDate 
   FROM Tickets
   JOIN (SELECT Level x FROM dual CONNECT BY Level < 999) 
   ON x <= (NVL(CloseDate,TRUNC(sysdate)) - CreateDate)
) 
GROUP BY ActiveDate ORDER BY ActiveDate;

^{SQL Fiddle}