Modeling a fact table - Call Center

[marric01]

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

[Jeff Smith]

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.