Dimensional model for support ticketing process

[emonchen]

I am struggling with a general support process that I have to model in my data warehouse. I have been brainstorming about how I could do this, but until so far without luck.
The process is fairly easy:
1. A customer enters a support ticket in the system.
2. The general support manager assigns the ticket to one of the support employees.
3. The support employee processes the support ticket.

The most straight-forward way of putting this in a dimensional model is to put all the support tickets in a fact table, and link all kind of dimensions to it, for example the employee dimension on the support guy and a role-playing time dimension on when the ticket was created, assigned and processed. But... (and here is one of the difficulties)... it could be that the customer isn't happy with the answer and resubmits the ticket. In that case, in the source system the date assigned and the processed date will be different. It could even be that the support employee is someone else... that means the data in the DWH is different from the source system. Resubmitting a ticket could be done an endless amount of time, so I am wondering about some best practices how to model this in my Data Warehouse.

Another difficulty is that reporting needs to be done on the following questions on a monthly basis:
- How many support tickets are currently to be assigned?
- How many support tickets are open for processing?
- How many support tickets does John Smith still have to process?

Would this be a case of taking snapshots in a fact table each month to be able to report on this?

Thanks!

[BoxesAndLines]

You need to use an accumulating snapshot fact table. PDF

[emonchen]

Some of the questions that I need to answer on this would be something that accumulating snapshots do not support (as fas as I understood the concept of accumulating snapshots):

For example, I do not only want to know how many support tickets are currently open, but I'd like to see a trend of the amount of support tickets that were open. So I need to be able to answer questions like "How many support tickets were open exactly one/two/three/... year(s) ago?". Theoretically I could pick last year's date and see which tickets had an earlier creation date and a later processing date, but I wouldn't have the first clue how I could visualize that in a cube...

What I could do is putting this in a periodic snapshot. Currently we're talking about 30.000 to 40.000 records a month (grown to this size in three years), so it's not a very high volume that I would be storing in a fact table. The only thing about this is that when there is a periodic movement of about 100 tickets, the rest of the 29.900 tickets will be duplicated every single month only with a different timestamp to it.

[BoxesAndLines]

You have a date for each event you want to track. This should still support the queries given that you have a date for that event. In the example below, ticket 100 was open from 1/1/2008 throught 6/1/2008.

ticket_nbr = 100
create_dt = 1/1/2008
close_dt = 6/1/2008

[LAndrews]

Here's another approach.

The grain of the fact table should be one record = ticket after a status change. If you effective date the status change, then you overcome the issue of any ticket regression ... there is no limit to the number of times a ticket is open or closed.

Ticket_Number
Effective Start Date
Effective End Date
Current_record
Ticket_Status
Assigned_To_Person
Open_Date
Assign_Date
Resolved_Date
Closed Date
.....

Its pretty powerfull from a query perspective:
- You can count specific status's at a specific point in time (using the effective dates).
- You can count activity over a period of time (How many tickets were resolved last week) by using the status dates.
- The effective dates can also be used in conjunction with a month dimension for your trending example

[Sponsored content]