Surrugated key in fact table

View previous topic View next topic Go down

Surrugated key in fact table

Post  maso on Fri Jun 03, 2011 3:40 pm

Im in charge of making the telemarketing database in my company so Ive made a fact table with a atom grain of one record for every telephone for channell for campain and I include a surrugate key and a child key so this way I can make a inner join and have a trace of the calls

the idea is to have a way of tracing exactly the history of outbound calls we have for a client but also if one was because of another the first part you can get from a simple order by date but the second part you need to include the Id of the previous call id

It works like the menu and submenu in some websites

any coments ???

maso

Posts : 2
Join date : 2011-06-03
Age : 43

View user profile http://www.telnor.com

Back to top Go down

Re: Surrugated key in fact table

Post  ngalemmo on Tue Jun 28, 2011 11:06 am

I wonder if it is overkill...

A simpler solution would be to carry a thread ID on related calls rather than a child (or parent) key. This option will not allow you to represent splits in the thread, but it doesn't seem that is needed in this case.

The advantage is all related calls would carry the same thread ID. Getting a list of related calls becomes a much simpler query. Using a parent/child relationship would require a bridge table or a recursive SQL query.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Surrugated key in fact table

Post  maso on Tue Jun 28, 2011 3:15 pm

I like your suggestion, It would work like this forum you see all the replays that originated from the first contact... but in some way you still need two columns; one for the id_sk (thread ID) and another to hold the original thread ID in the other contacts

Did I get you right? or can you give me an example of the table design your talking about


maso

Posts : 2
Join date : 2011-06-03
Age : 43

View user profile http://www.telnor.com

Back to top Go down

Re: Surrugated key in fact table

Post  ngalemmo on Tue Jun 28, 2011 4:09 pm

You would have two columns: a call ID that is unique for each call and a thread ID that is shared across calls in the same thread.

When a new, unrelated call is initiated, you create a row where the call ID and thread ID are the same value. If another call is made that relates to the previous call, you get the thread ID from the previous call and use it as the thread ID for the new call. Subsequent dependent calls would do the same thing, getting the thread ID from the prior call.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Surrugated key in fact table

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum