Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

warehouse

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.

share|improve this question
    
I'm not really clear on why you have a star schema data warehouse for support tickets. Looking at your schema I don't see any real advantage over transactional detail. Tickets open on a day is just select count(*) from ticket where created<=@DateOfInterest and closed>=@DateOfInterest. If you want to get open by day, just join this against a number table with dates, no? –  Joel Brown Jan 16 '13 at 15:38
add comment

2 Answers

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.

share|improve this answer
add comment

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.