Dimensional modeling becomes challenging, sometimes in uncommon situations, or that are not straightforward. Dimensional modeling with multiple stages of processing can sometimes be confusing and difficult.
Such is the nature of a ticket-based support system where a single ticket can go through multiple stages and multiple people. So, how do you do dimensional modeling for such a process?
Understanding the Support Process First
Before we talk about modeling, let’s take time to understand the processing phases that a support ticket goes through and how reporting needs to happen.
The process of a support ticket based system goes like this:
- A customer enters a support ticket in the system (either calls or creates online.)
- The general support manager assigns the ticket to one of the support employees or an employee picks up the ticket himself/herself.
- The support employee processes, the support ticket and in the process:
- Asks the customer further questions
- Provides updates through comments
- Resolves the ticket
- The customer confirms satisfaction
- Employee closes the ticket
- If a support employee needs somebody else to look further, in case the work is complex, the ticket is re-assigned to another employee or escalated to the next level
- If a customer finds the same issue again even after the ticket was closed, he/she can re-open the same ticket with additional comments
As you can see, a ticket can go through cycles of assignments, re-assignments, re-opens, escalations etc.
Now, some of the questions management would like to know from this data could be:
- How many support tickets are opened/being worked on/closed/reopened on a daily basis?
- How many support tickets does Bob process every day?
- How many of Bob’s closed tickets have been re-opened in last 6 months?
- How many tickets were opened by phone versus online?
Those were just a few examples. There could be other interesting reports too. But this gives a good idea of what kind of questions need to be answered.
Designing the Dimensional Model
Now, having understood the need, one may think that snapshot fact tables would do the job since there are stages in which the support tickets are processed. However, the problem with that approach is that a closed ticket can be re-opened and now it has to go through all the stages again.
From my experience, the best thing to do is to create a fact table with granularity to have one record per event about the ticket.
For example, you can have dimension tables to represent Support Agents, Customers, Date, Time and Geography (if location is involved,) with a fact table that records each event about a ticket in it. That means, there will be one record inserted when a ticket was created, another record when the ticket was assigned to someone, another record when the ticket was updated with some more information by either customer or agent and so on.
An example of a dimensional model for such a fact table is depicted in the image above in this post. If you have any questions about this pictorial representation of the data model, please use comments section to ask them.
Such a design is very powerful in the sense that you can answer any questions pertaining to a ticket and support around it such as:
- How many tickets were open in a given time period?
- How many tickets were re-opened in a given period?
- How many tickets were resolved last week?
- How many of Bob’s tickets were re-opened in the last 6 months?
Now, you don’t have to worry about duplicating data if a ticket was re-opened. Every event is stored as it comes.
For trending reports on a daily basis, monthly basis and so on, you can now add a few aggregate tables based on this central granular fact table and obtain fairly comprehensive answers on any kind of analytical questions.
Any Thoughts about This Dimensional Model?
I have just shared with you my experience with dimensional modeling of ticket based support systems and I have found it to be very useful. However, I do know that there is always a scope of improvement especially in today’s world.
If you know another way of dimensional modeling for this scenario which may be better, please share it through your comments and help this community. Thank you kindly!