Whether to use additional column or use existing

View previous topic View next topic Go down

Whether to use additional column or use existing

Post  mr_neal on Thu Nov 01, 2012 1:45 pm

Hello,
I am tracking when call center cases (fact_case) are re-assigned from one person to another using that persons id. Now I find that cases can leave the call center for in-person visits (different mode). I want to use the same column, fk_reassigned_to_id when a case switches modes. Is that an ok solution or should I create a new column something like fk_mode_switch_id?

My main concern with using existing column is that a call center re-assignment is a different process than the mode switch. However, in using different column, I thought there would be difficulty reporting on the complete history of the case, from call center assignment, to re-assignment, to mode switch and possible further re-assignments.

Please let me know your thoughts. thanks

mr_neal

Posts : 17
Join date : 2012-01-26

View user profile

Back to top Go down

Re: Whether to use additional column or use existing

Post  BoxesAndLines on Fri Nov 02, 2012 12:15 pm

Sounds like you need a new fact table instead of adding additional FK's. How many times can it get reassigned, once, twice, three times? A reassigned transaction fact would easily handle counting all the variations you have.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Whether to use additional column or use existing

Post  at710 on Fri Nov 02, 2012 1:03 pm

-


Last edited by at710 on Fri Nov 02, 2012 1:04 pm; edited 1 time in total (Reason for editing : wrong thread)

at710

Posts : 10
Join date : 2012-11-02

View user profile

Back to top Go down

Thanks Boxes and Lines!

Post  mr_neal on Fri Nov 02, 2012 3:21 pm

Thanks Boxes and Lines! Yes, the case can get re-assigned countless times, and can go from call center to visit, and back to call center, or even Internet and email. However, could you explain your reasoning? I think my concern with creating another fact table is that records would be entered into both fact tables; fact_case to show latest status of a case and "fact_assignment" to show assignments/re-assignments.


Last edited by mr_neal on Fri Nov 02, 2012 9:24 pm; edited 1 time in total (Reason for editing : html error)

mr_neal

Posts : 17
Join date : 2012-01-26

View user profile

Back to top Go down

Re: Whether to use additional column or use existing

Post  BoxesAndLines on Fri Nov 02, 2012 8:53 pm

Status can generally be tracked via a dimension. The exception is when the status change is an important enough of an event to warrant its own fact table. Otherwise, a type 2 on status can always give you the current and historical status.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Status via Dim

Post  mr_neal on Fri Nov 02, 2012 9:21 pm

BoxesAndLines wrote:Status can generally be tracked via a dimension. The exception is when the status change is an important enough of an event to warrant its own fact table. Otherwise, a type 2 on status can always give you the current and historical status.

I do track status via Dim_status, and have a status of re-assigned as well. However, I use the re_assigned_to_id column to track who has the case over time, so I can report on the history of the case. If I create a new fact table, then wouldn't I have to join 2 fact tables in my query to produce the history of a case? I guess what I am failing to understand is the rule for why and when I would create a new fact table. I thought the fact table represented a business process, and our fact_case table tracks the history of a case from "new", "open", "contact made", "supervisor review", "re-assigned", ending with "completed", "incomplete", or "partial" case. Based on your reply, significant events are re-assignment, mode switch, QA/QC, etc. I would need at least 3 additional fact tables, correct? That would mean 4 joins to get case history.

mr_neal

Posts : 17
Join date : 2012-01-26

View user profile

Back to top Go down

Re: Whether to use additional column or use existing

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