Modelling a related facts scenario

[arfoot653@gmail.com]

Hi,

Iam working on modelling a dimensional warehouse for a ticketing system. I have a accumulating snapshot fact table and the grain is individual ticket and there are a few dimensions involved. An individual ticket can be related to few other tickets in the system. How do i model this scenario?

Should this be a table where i store the fact id of the ticket and fact id of the related tickets, so that they can be joined with the fact table to retrieve the attributes?

Thanks in Advance.

[ngalemmo]

Maybe, it depends. Is there a hierarchy among a group of tickets? How does one ticket relate to others?

[arfoot653@gmail.com]

There are two cases. 1) Some tickets could be identified as realted to another Global ticket, which becomes the parent of the ticket. 2) Some tickets could be raised to address an already entered ticket, in which case its considered a related ticket, no hierarchy, but related.

Thanks,
Arun.

[hang]

It sounds like a role playing dimension by ticket. Is the ticket number a degenerate dimension (DD)? If it is, then put DD in different columns in the fact table. Otherwise have ticket SK by different aliases in your fact table like a normal role playing dimension.

[arfoot653@gmail.com]

Hi hang,

Thx for your reply. For the global ticket i had done a role playing dimension, since every ticket can be related to only one global ticket. But one ticket can be related to variable number of multiple tickets. So role playing dimensions are ruled out.

Thanks,
Arun.

[Sponsored content]