Thursday, July 28, 2011 6:16 AM
I am trying to determine the best way to warehouse our companies call detail records as we are a customer service based company that makes hundreds of thousands of phone calls each day. After looking through the data for quite some time now, I am not sure exactly how I would model the Call Detail Records (CDR's) into Fact and Dimension tables.
The problem I am running into is that the CDR table has multiple rows for a single call, for example there is one row for the call connecting to an IVR, there is one record for the call connecting to an agent, there is another record for the call being transferred to another agent, there are records for when the call is sent to a voice mailbox, etc. I was thinking that I would have to find every combination of different call types, but I have counted over 500 unique call types, so to me this would be too complicated for a business user to understand.
If anyone could explain to me what the best method of going about warehousing phone call data or point me in the right direction, that would be fantastic!
Tuesday, August 02, 2011 6:36 AMModerator
I am consulting between internal teams. Once i get valuable information, i will update here for your refernece.
Friday, January 20, 2012 3:41 AM
I was wondering if you had any success in building a dimensional model around call detail records? I'm still at a loss as to how to do this and any help would be greatly appreciated.
Tuesday, January 24, 2012 9:37 PM
Could you pivot on some of the more oppurtunistic types of calls for your incoming call facts? Call's time to ACD, First Option Picked or first call route direction, If/Where transfered too, and time Abandoned, ect... Obviously you might not have to do this on everything but if you talk to your call center managers they would know the metrics they use.
Enterprise Applications Specialist
Monday, January 30, 2012 1:20 AM
Looking from a Dimensional modelling perspective, there should be a Dimensional table called CallType, where you put the dimensional members as different Types of call (Or perhaps stages of call in your scenario). Then you should be able to create view(s) based on your CDR transactional table to separate out the granularities.
Simpler model: That way, you have one dimension, connected to different fact tables.( But I'm very sure you might have other similar dimensions that will correspond to different facts (Example customer dimension)) Although your Fact tables are in different "grain", using MDX, you can create calculations between different measure groups as long as you have one similar dimension.
If this doesn't work in your business scenario:
You can model a complex model, where you can use "Factless Fact table" to create a bridge between different granularities.(To make a connection between granularity)
hope this will give you some ideas!
please remember to mark as answered if the post helped resolve the issue.
- Edited by rok1 Monday, January 30, 2012 1:25 AM
Wednesday, February 01, 2012 8:25 AM
I have a solution that i used before in a similar situation (not 500 :) ) but i had a lot of measures each with different dimension. i call it the diagonal Fact Table where you put all columns that applies for the 500. common data will be there and when there's a type that doesn't apply for another one leave it NULL. and configure your dimension not to require data and accept Nulls.
aggregation will work correct and you'll have 1 Fact Table and dimensions will drilldown properly. I've wrote a detailed blog post about it here
Wagdy Ishac www.sqldair.com
Thursday, February 02, 2012 3:58 AM
if you have relationship between these two records then combine these two records into the single dimension or insert these two rows in dimension. it's depend upon your requirments that how you want to populate your dimension table.
if you haven't any measure(dollar amount,quantity etc) in the data then you need to create the fact less fact table which only contains the count of rows.
Note:- Granualirity is very important in dimensional modeling
please have a look into the following slide about dimensional modeling basics:
Thursday, February 23, 2012 10:56 AM
this concept appears to be similar to a pipeline where a single service request (or a similar process) goes through various stages that have to be captured into fact tables. you might want to refer to this article for some ideas-