Problem when creating link table from 4 fact tables
I'm trying to create a QV model based on a complex call center database. I've got my fact tables spread in four:
CD= Has all the calls, no matter if they were successful or not.
AD= Has only calls connected to agents.
QD=Has calls that went through a Queue (not always arrived to an agent)
RD=Has more information about calls that have been queued.
All the tables share four key fields (CD's keys). AD, QD and RD have additional keys.
I built a script and created a Link Table (please, see script attached). It seems to be all fine, but then I realized that I could not get information from QD when there is no AD record (e.g.: I want to analyzed calls that went to a queue but no agent has been reached (abandon calls)).
¿Please, could you tell me if something is wrong with the script? The key fields from each table are the ones that have been used to build the unique key.
Re: Problem when creating link table from 4 fact tables
I've tried creating a common key with the four common fields to build the linktable and that solved the reference problem when calculating abandon calls in queue. However it created additional problems in other indicators.
I think that the basic problem is the cardinality between tables. That can be identified by the qindex field (which is in ACD, CQD, CRD but not in CCD, that means that for each CCD record, there might be 0, 1 or many records in the other three tables).
Is there any guideline to follow when building linktables in complex cases? Most of the examples are quite simple and I'm afraid of being loosing something.