Unbalanced Hierarchies with Fixed Levels

View previous topic View next topic Go down

Unbalanced Hierarchies with Fixed Levels

Post  kuldeepchitrakar on Fri Apr 29, 2011 12:36 am

Hi

I need confirmation on one of the solution i am thinking of.

I have Merchant dimension.

A merchant can have N number of sub-merchant at level 1 means merchant->Submerchant (Note: Submerchant will never have another submerchant)

and A Merchant may not have submerchant.

e.g.

Code:
M1------M2
            |
        --------
      |          |
      M3        M4


No, Since its fixes levels instead of using Bridge table I am thinking of add ParentMerchant column in dimension table.

i.e. Merchant | Parent Merchant |------

Do you see any issues in this. Also instead of using PK-FK for Merchant-Parent Merchant cant I use Name of the Parent Merchant instead of Key.

--Kuldeep


kuldeepchitrakar

Posts : 17
Join date : 2010-04-21
Age : 34
Location : India

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

Back to top Go down

Re: Unbalanced Hierarchies with Fixed Levels

Post  John Simon on Sun May 01, 2011 6:53 pm

It depends on your reporting tool - does it handle parent-child? If you are using BO then you will probably need a bridge table.
No you cannot use the parent name instead of the key. How would you track SCDs?

John Simon

Posts : 186
Join date : 2009-05-29
Location : Melbourne, Australia

View user profile http://jsimonbi.wordpress.com

Back to top Go down

Re: Unbalanced Hierarchies with Fixed Levels

Post  kjfischer on Fri Jun 17, 2011 4:04 pm

I have a similar situation except it is "customer".

A customer may have a "parent" customer or it may not (where customer_id = parent_customer_id).

It only goes one level deep.

I have looked at Kimball's organization bridge table:
parent_customer_key
subsidary_customer_key
depth_from_parent
lowest_flag
topmost_flag

If I have this customer data, how would I populate this bridge table and is it necessary given I do want to report sales by individual customer_id and by the parent?

customer_id parent_id name
10101 12345 UHT
11122 12345 Ingenix
12345 12345 United Health
20000 20000 3M

Thanks in advance

kjfischer

Posts : 28
Join date : 2011-05-04

View user profile

Back to top Go down

Re: Unbalanced Hierarchies with Fixed Levels

Post  ngalemmo on Sat Jun 18, 2011 8:14 am

Placing all levels of a hierarchy on a single row is known as 'flattening' the hierarchy. It is often done. Flat hierarchies are most useful when levels are easily identifyable and fixed, as is this case.

The only downside is when your two-level hierarchy becomes deeper.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Unbalanced Hierarchies with Fixed Levels

Post  kjfischer on Mon Jun 20, 2011 3:13 pm

Thanks. I just learned that 70% of customers point to themselves as the parent. So, 30% of customers actually have a parent that will have different values for name, address, etc. Does this information influence the decision to include all the extra parent fields (flattening customer table) vs. adding a bridge table to link customer and parent?

kjfischer

Posts : 28
Join date : 2011-05-04

View user profile

Back to top Go down

Re: Unbalanced Hierarchies with Fixed Levels

Post  ngalemmo on Mon Jun 20, 2011 11:56 pm

Yeah, 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: Unbalanced Hierarchies with Fixed Levels

Post  smithwilson182 on Thu Jul 07, 2011 11:00 am

Yes bridge can solve our problem so always use bridge.

smithwilson182

Posts : 2
Join date : 2011-07-07

View user profile

Back to top Go down

Re: Unbalanced Hierarchies with Fixed Levels

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