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

View previous topic View next topic Go down

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

Post  Hemapr on Tue May 15, 2012 1:57 pm

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?

Hemapr

Posts : 12
Join date : 2012-05-15

View user profile

Back to top Go down

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

Post  ngalemmo on Tue May 15, 2012 2:43 pm

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.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

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

Post  hang on Tue May 15, 2012 6:13 pm

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.

hang

Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia

View user profile

Back to top Go down

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

Post  Hemapr on Wed May 16, 2012 11:01 am

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.

Hemapr

Posts : 12
Join date : 2012-05-15

View user profile

Back to top Go down

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

Post  hang on Wed May 16, 2012 5:08 pm

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.

hang

Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia

View user profile

Back to top Go down

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

Post  ngalemmo on Wed May 16, 2012 5:22 pm

hang wrote: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.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

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

Post  Hemapr on Thu May 17, 2012 10:54 am

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.

Hemapr

Posts : 12
Join date : 2012-05-15

View user profile

Back to top Go down

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

Post  ngalemmo on Thu May 17, 2012 2:33 pm

Hemapr wrote: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.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

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

Post  Hemapr on Fri May 18, 2012 12:32 pm

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

Thanks all!!

Hemapr

Posts : 12
Join date : 2012-05-15

View user profile

Back to top Go down

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

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