Call center

Hi

We are stating a project : build a cube for a call center.

I have identify the grain (fact) to be the call with some measures (Total work time in minutes, Total time before call get assigned, number of time call get reopen).

I have also dimensions, Employes, Call type, Clients, Priority, etc ...

I have a situation where multiple employes can be assigned to a call and a call can have multiple employes assigned to it. In a regular relationnal database model I would have
no problem to design that.

But can you guys give me any hints/suggestions on how you could design that for the DW ?

Thanks and have a great day !

Richard

It depends on what you are trying to track and report on. I'm assuming that when you say a call can be assigned to multiple employees this is sequential and not concurrent (i.e. call is received by employee A, forwarded to employee B, returned to A who completes it, etc)?
If you want to track the progress of the call between the employees then the grain is probably call + employee: so you potentially have multiple fact records per call. As long as you have a call dimension (or a call_id you could use as a degenerate dimension on the fact table) you can tie the facts together to a single call - and aggregate them up to a new "Call" fact table that doesn't have employee as a Dim if that's what you want to do.

If you don't need to measure which employees took the call, in which order and for how long - but just need to know that Employees A, B and C were involved in the call at some point then you could use a bridge table: the Fact table has an Employee group key, the bridge table has 2 columns: Employee Group Key and Employee Key (and multiple records per Employee Group key); and the Employee Key joins to the Employee Dim. If the same set(s) of Employees often work on the same call (so there is, in effect, the concept of a team working on the call) then there may be advantages in setting up an Employee Group dimension - as an extension of this bridge table design

Hope this is of some help

nick_white wrote:It depends on what you are trying to track and report on. I'm assuming that when you say a call can be assigned to multiple employees this is sequential and not concurrent (i.e. call is received by employee A, forwarded to employee B, returned to A who completes it, etc)?
If you want to track the progress of the call between the employees then the grain is probably call + employee: so you potentially have multiple fact records per call. As long as you have a call dimension (or a call_id you could use as a degenerate dimension on the fact table) you can tie the facts together to a single call - and aggregate them up to a new "Call" fact table that doesn't have employee as a Dim if that's what you want to do.

If you don't need to measure which employees took the call, in which order and for how long - but just need to know that Employees A, B and C were involved in the call at some point then you could use a bridge table: the Fact table has an Employee group key, the bridge table has 2 columns: Employee Group Key and Employee Key (and multiple records per Employee Group key); and the Employee Key joins to the Employee Dim. If the same set(s) of Employees often work on the same call (so there is, in effect, the concept of a team working on the call) then there may be advantages in setting up an Employee Group dimension - as an extension of this bridge table design

Hope this is of some help

Hi thanks for the reply,

In fact, when the call enter the automated process (example : email made to call center), it's assigned to multiple ppl, but there's only 1 person that can work on the call (employe take charge of the call). When call is in assigned mode, it means that call is still not open and pending. We want stats on those pending call by employees.

Richard

Do what Nick said in paragraph one then. Set the grain of the fact table to call+employee. Add a metric for the employee that picked up the call (set to 1). For everyone who didn't pick up the call, they would get a 0. From a relational perspective, your associative entity becomes your fact table.

BoxesAndLines wrote:Do what Nick said in paragraph one then.  Set the grain of the fact table to call+employee.  Add a metric for the employee that picked up the call (set to 1).  For everyone who didn't pick up the call, they would get a 0.  From a relational perspective, your associative entity becomes your fact table.

Hi, just to be sure that I explain my needs (sorry if i'm not clear, english is not my first language)

The measures we want to track are related to the call (time before closing the call, time before taking charge of the call, number of time it was reopen, etc ...)
The fact inside are ClosedBy, CreatedBy, DateReceived, DateCompleted, Client, CallType, Status, etc ....

When a call is entering the automated process (client send a mail) or the employee can't resolve the issue on phone ... depending of the type of call, it's assigned to a number of ppl (pending mode status).
When 1 of those ppl take the call (open status), all the other assigned person are removed from the call. So when the call is open (open status) , it's assigned to 1 and only 1 person.

The manager of the call center want to have another stats regarding the number of call that are assigned (pending mode status) by employees. So many employees can be assigned to a call at a certain time.

If I set the fact grain at call+employee, the measures are not gonna be wrong ?

Thanks again !

Hi,

bear in mind that you can have as many fact tables as you need and they can all be at different grains
1) Identify the events that you need to measure
2) Identify the grain/dimensions of those events
3) Create at least one fact table for each unique set of grains/dimensions you identify
4) Hold measures at the lowest level of granularity possible as you can always roll them up to a higher level. If you start with your data at a higher level then you can't split subsequently split it down to a lower level when business requirements change (and they will!)

It normally makes sense to put all measures that have the same set of Dims in the same fact table but you may have valid reasons (performance, partitioning, data access, etc.) for splitting them out into multiple fact tables. It is critical that you don't put measures with different grains in the same fact table - for many reasons but one is, as you've said, "the measures are not gonna be wrong?".

Your design decisions may also be driven by how/when you are receiving the data, data volumes, etc. For example, one of the measures you mention is "number of times the call is re-opened"; if you only load the data into the DW once the call has been finally closed, at which point you know how many times it has been re-opened, then the relevant fact table might be at the grain of the call - as you can insert a single record per call into your DW. However, if you load data about a call before it has been finally closed then it may make more sense for the relevant fact table to be at the grain of "call + re-opened date/time" - so you can insert a record each time it is re-opened (fast) rather than have to perform an update (slow).
You can then summarise the data in this fact table into an aggregate (table loaded by your ETL process, View, Materialised View or just your BI report) which gives you the count of "re-opens" for each call