Sharing fields among dimensions

[mru22]

I have applied the concept of a conformed dimension in my project but was wondering if it is considered ok to share dimension fields in 3 or more dimensions ?

For example.

I have a ClaimPayment Fact table that originally had a PersonKey, DivisionKey and CostCenterKey.

I denormalized CostCenter and Division into one and then made a conformed division dimension. I wanted to copy some of the Divison and cost center information into the Person Division so I could tell which person belongs to which cost centers/division without joining to see if they have a claim payment.

By doing so however means I would have the Division data now in cost center and person, plus cost center information in person.

Is there a better way to approach those relationships ?

[liao]

You can just use one demansion: Person, the information of cost centers/division looks as its attribute fields.

[ngalemmo]

I think your first model, with the three dimensions was fine. However if CC/Division is a hierarchy, then having division as a dimension off the fact is not a good idea if the CC/Division relationship can change. You are better off rolling division into CC or use a hierarchy bridge.

Assuming a person is someone who makes a claim, why do you need to make special provisions so someone can get a list of persons by CC or division? What would be the purpose of such a list without any measures? How often would such a list be generated that warrants such treatment?

[Sponsored content]