Call Center Dimensional Model

[billcarson]

I am in the process of designing a data model for a call center for an insurance company. We will be processing data feeds from several different data sources and some of the subject areas are as follows: call segment (a record for different segments of a call such as IVR, routing point, queue, agent 1, agent 2, etc ...), IVR events (a record for each IVR node that a call hits), customer survey (a record for each answer given in a customer survey), agent observation (a record for each component of a supervisor's scoring of an agent's work), call reasons (a record per call/reason code given to calls by agents or automatically assigned when calls hit specific queues). For each of these subject areas, the common "dimension" is the call itself however there is not a single source of information for a call. There are 10 or so attributes of the call as well as a few metrics (i.e. total call time, agent handle time, etc ....) that would/could be stored in this "call dimension". There are many other common dimensions that are really associated with the call and not the individual facts in these subject areas such as "member", "provider", "plan". In addition, there are other dimensions that tie better to the individual facts such as "resource" , "agent", "survey", etc ....

My quandry is this ............ is designing a "call dimension" the right thing to do. In a way, this is similar to the order/order line design issue in that the call segment fact will be the main source of information for this "call dimension". However what makes this a bit different is that I have several facts that tie in directly to a call which is why I'm trying to figure out a way to have this "call dimension". I want to tie survey data to call segment data to agent observation data and the only way to do that is through the call id. Since several of the other dimensions "member", "provider", "plan", etc ..... are really attributes of the call, this gets me into snowflaking territory. Of course, one of the difficulties here is correctly reporting on a metric such as "total call time" that is stored in this dimension when joining it to facts that have multiple rows per call.

I guess I'm looking for some tips from those who hopefully have experience designing call center solutions or have dealt with something similar. I've scoured the internet and really can't find any examples of call center data mart design other than the very simple transaction/customer/date examples. Most of the reporting will be done via Business Objects XI sitting on a Oracle 11g database. The volumes are not significant (e.g. 25,000 calls a day and we will maintain 3 years of data).

Thanks for your help!

[BoxesAndLines]

It seems to me that you're too focused on normalized data modeling. You rarely build relationships from one dimension to another, rather you relate all of the dimensions through the fact table. Without reviewing all of the data, it's hard to say whether you actually need a call dimension. The call id can certainly live on the fact table as a degenerate dimension. It may be that you create an aggregate fact at the call level and add the call level dimensions there.

[billcarson]

Yes, I can see your point. I've been putting together dimensional models for 12+ years now so I understand the concept of facts and dimensions and star schemas. What I'm dealing with here though seems to be something a bit out of the ordinary and thus my post. The attributes of the call are many and only available in some of the fact sources but I'd like to make all of these attributes available to the other facts. This is my reasoning for creating a so-called call dimension. We currently have this modeled as an aggregate fact as you suggested but that is because we were only dealing with 2 facts that were call related up until now. Now we are sourcing many other facts and the reporting requirements dictate that when we analyze the data in these facts that we be able to slice-and-dice by some of the attributes that are currently only available in the current aggregate fact. Certainly someone out there has put together a data mart/warehouse for a call center.

[Bob Probst]

Do you have a diagram you could share so I could better understand what you're trying to do?

[Sponsored content]