none
Warehousing Call Detail Records

    Domanda

  • Hi,

    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!

     

    Thanks,

    Christopher Haws

    giovedì 28 luglio 2011 06:16

Tutte le risposte

  • Hi Christopher,

    I am consulting between internal teams. Once i get valuable information, i will update here for your refernece.

    thanks,
    Jerry

    martedì 2 agosto 2011 06:36
  • Hi Jerry,

     

    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.

     

    Thanks,

    Chris

    venerdì 20 gennaio 2012 03:41
  • 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
    martedì 24 gennaio 2012 21:37
  • Hi Chris,

     

    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!

    Rok


    please remember to mark as answered if the post helped resolve the issue.
    • Modificato rok1 lunedì 30 gennaio 2012 01:25
    lunedì 30 gennaio 2012 01:20
  • 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

    http://blogs.technet.com/b/meacoex/archive/2010/11/29/the-diagonal-warehouse-design.aspx

     


    Wagdy Ishac www.sqldair.com
    mercoledì 1 febbraio 2012 08:25
  • Hi,

    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:

    http://www.atlantamdf.com/Presentations/AtlantaMDF_091106.pdf

    http://dylanwan.wordpress.com/bi-and-olap-glossary/factless-fact-table/

     

    cheers,

    Zaim Raza.

    giovedì 2 febbraio 2012 03:58
  • 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-

    http://www.kimballgroup.com/html/designtipsPDF/DesignTips2002/KimballDT37ModelingPipeline.pdf

    giovedì 23 febbraio 2012 10:56