Unbalanced Hierarchies with Fixed Levels

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

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?

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

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.

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?

Yeah, use a bridge.

Yes bridge can solve our problem so always use bridge.