Modeling a fact table - Call Center

View previous topic View next topic Go down

Modeling a fact table - Call Center

Post  marric01 on Thu Dec 20, 2012 10:21 am

Hi Everyone !

This is what i'm using for my BI project

SSAS 2008
SSIS 2008
SQL 2008 Ent
Business Intelligence Studio

Fact table grain : Call

We have a call center here and we are using a system that manage all the call. My client need to have the possibility to analyse information about the call center.
Depending on the priority of a call, we promise to client that a call will be assign/taken by a call center employe in the next XYZ hours after call is received/entered in the system.
For a call, we have many dates columns :

- (Date_Entered) Date/time call entered/created in system (By hand or automatically)
- (Date_Assign) Date/time when someone take charge of the call
- (Date_RealCompleted) Date/time call completed/resolved
- (Date_TargetAssign) Maximum Date/time calculated by the system for this call to be taken care of by an call center employe.
- (Date_TargetCompletion) MAximum date/time calculated by the system for this call to be completed by an call center employe.
- (Priotity_ID) FK that indicates the priority of the call, this impact the target date/time of this call.

This is my question :

- It is a good thing to transfert those date/time columns informations in my fact table ?
- My client want to know information on call that doesnt respect the targetAssign and traget completion but I can figure out how to display that information to my client (A simple boolean value that say SLA [Service Level Agreement] is respected or not ?)
- Should I store the delay between the Target date/time VS the Real date/time in my fact tables (ie number in days or hours or minutes) ?

Any sugestion/advice is greatly appreciate on how to display information in my SSAS cube on that particular scenario ;-)

thanks you and have a great day !

* Sorry for my bad english, it's not my first language

marric01

Posts : 23
Join date : 2010-08-18
Age : 41
Location : Montréal, Québec , Canada

View user profile

Back to top Go down

Re: Modeling a fact table - Call Center

Post  Jeff Smith on Thu Dec 20, 2012 12:54 pm

I would include the Target dates and the Actuals. I would have a Date Dimension with just dates and a Time dimension. You could set the level of the time dimension to whatever you needed it to be - seconds or minutes or even tenths of a second. I would definitely calculate the length of time between the Target and Actual dates and use them as the measures.

Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

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