Tracking Support Tickets: accumulating snapshot and transaction grain fact (a presentation question)

View previous topic View next topic Go down

Tracking Support Tickets: accumulating snapshot and transaction grain fact (a presentation question)

Post  SamiMajed on Thu May 30, 2013 1:04 pm

I have a need to provide analysis for "support tickets" throughput.

Business Questions are:
1. How many tickets were worked on a given day/week/month/etc.
2. How long did it take for a ticket to hit three statuses (open/assigned/closed)

I have:
------------------------------
DimDate [DateKey, etc...]

DimTicketStatus [StatusKey, Name]
Rows:
1, Open
2, Assigned
3, Closed

DimTicket [TicketKey, ProblemStatement, DateKey]

-------------------------------------
FactTicketProgress [TicketKey, Open (datetime), Assigned (datetime), Closed (datetime)]
(this is my Accumulating Snapshot Fact Table) Grain Statement: 1 Row represents exactly 1 Ticket when it gets saved as "Open"

FactTicketHistory [TicketKey, StatusKey, DateKey]
(this is a Transaction Grain Fact Table) Grain statement: 1 Row represents each Ticket's Status when it saved in the system (clicking Save)

-------------------------------------
The problem is that our business users are only going to be using PowerPivot for analysis.
So the question is: How can I answer #2 (How long did it take for a ticket to hit the three statuses?)
Can we only do this through a regular report, calculating the time differences e.g.: DateDiff(minute, Open, Closed)?

Instead I'd like to provide analysis through PowerPivot where you can slice using a range table that has [1 - 2 hours, 3 - 6 hours, 6 or more hours]. This way, the business can see how many tickets took the different 'ranges' of time. There has to be a way to do this, right?

Please help.
Thank you so much for this forum.
-Sami
avatar
SamiMajed

Posts : 4
Join date : 2013-05-23
Location : Santa Ana, CA, USA

View user profile http://www.linkedin.com/in/samimajed

Back to top Go down

Solved

Post  SamiMajed on Mon Jun 03, 2013 2:47 pm

Update:

I resolved the issue by adding a fact table with this schema:

DimTicketStatusRange
-----------------------------
StatusRangeKey int
Name varchar(50)
Low int (note: in minutes)
High int (note: in minutes)

Sample Population:
-----------------------------
1, 'Under an Hour', 0, 59
2, 1 - 2 Hours, 60, 120
3, 2 - 4 hours, 121, 240
...

FactTicketProgress
-----------------------------
TicketKey int (FK references DimTicket.TicketKey)
StatusKey int (FK references DimTicketStatus.StatusKey)
TicketStatusRangeKey int (FK references DimTicketStatusRange.StatusRangeKey)
InternalUserKey int (FK references InternalUsers.UserKey) (note: InternalUsers contains User's department [e.g. client services])

I can now use PowerPivot to slice/dice how long tickets sit in a particular status, who is keeping it in this status, their department, etc.
I also added a IsFirstTime (char(3) Yes|No)) to FactTicketProcess in case tickets go back and forth through the statuses. This helps me to differentiate when a ticket first hit the status and how many times it hit the same status again (e.g. tickets getting re-assigned multiple times)

Of course, there is another transaction grain fact table (FactTicketHistory) that contains the history in order (TicketHistorySK, TicketKey, TicketStatusKey, InternalUserKey, DateKey, TimeKey)

Thought I'd post this solution in case anyone else runs into the same situation.
avatar
SamiMajed

Posts : 4
Join date : 2013-05-23
Location : Santa Ana, CA, USA

View user profile http://www.linkedin.com/in/samimajed

Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum