How do we design parent-child relationship of the lowest grain in the fact.

[Hemapr]

Basically, i'm working on telecommunications data mart. The grain of the fact is Ticket number (which customer opens whenever there is some issue..) But, a ticket number can have child ticket numbers associated with it.
The approach, i thought was : create ticketnumber as degenerate dimension in the fact instead of having its own dimension. But, with parent-child relationship of the ticket number then what's the best approach in the design?

[ngalemmo]

This is a dimensional hierarchy just like any other dimensional hierarchy, the only difference is it is a degenerate dimension. Implement it using a hierarchy bridge table. As this is a degenerate dimension, the bridge would contain the ticket numbers rather than dimension table keys.

[hang]

Is it like order header-item situation. I would think the fact table itself grained at item level with two DD's would supersede the need for a bridge.

[Hemapr]

Its not like a header-item situation. The reason is, not all ticket numbers will have child tkts only in some major outages (telecom) then it will have child tkt nos. So, grain cannot be the child tkt number.

[hang]

OK, then you allocate the ticket number to child ticket fact table while still having another fact table grained at ticket number level, assuming all the child tickets do have parent. The point is ticket and child ticket number are DD in fact table with all the relevant attributes as smaller dimensions instead of having ticket or child ticket dimensions with similar size of fact tables.

[ngalemmo]

Is it like order header-item situation. I would think the fact table itself grained at item level with two DD's would supersede the need for a bridge.

This will work, while having two facts is a bad idea. Essentially this is almost the same as a bridge. The fact grain is by ticket (expressed as a degenerate dimension column). The second ticket column would reference the parent ticket (again a degenerate dimension value) and would be null if the row does not represent a child ticket.

[Hemapr]

Thanks a bunch for all your suggestions!!

I'll create 2 DDs in the fact and it looks like a good idea than having a separate bridge table.

[ngalemmo]

Thanks a bunch for all your suggestions!!

I'll create 2 DDs in the fact and it looks like a good idea than having a separate bridge table.

For your particular situation it works. If there is a situation where a child ticket could have children, then you are dealing with a multilevel recursive hierarchy. In a case like that, you should always use a bridge.

[Hemapr]

I need to use bridge as suggested by ngalemmo because the child tkts becomes parent of some other tickets.

Thanks all!!

[Sponsored content]