Problem when creating link table from 4 fact tables

[ccaporaso]

Hello,

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.

Thanks in advance and regards,

Ceci.

[#2]

Hi,

     Could you attack a database diagram to understand better the table relationships

[ccaporaso]

Hi Gregorio!

Thanks for your answer!

I'm attaching (as a .PNG file) the complete structure as seen in QV.

Please, keep in mind:

- The script attached in my original post is a brief version: I avoided some unnecessary tables and fields in order to focus on linktable issue.

- The original table names (as you can see in the diagram) is:

CCD=CD

ACD=AD

CQD=QD

QRD=RD

Thanks!

Ceci.

[#4]

Hi ccaporaso,

     Why you don't create this key for all tables,

sessionid & sessionseqnum & nodeid & profileid as CommonKey.

I hope helps.

[ccaporaso]

Hi Gregorio!

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.

Thanks once more, any idea is welcome.

Ceci.

[felcar2013]

Hi Cecilia

did you achieve your Goal? I have some indicators loacated in four or five fact tables and wanted to use link tables.

concatenate seems not to be a solution for my case

thanks for any comment

felipe