Question on Dimenstion refernece to another dimension.

[mru22]

I am trying to Add a PersonIncdient Dimension And Claims fact table as shown below:


Dimension
PersonIncident
PersonIncidentKey
AffectedPersonId
IncidentId
[CostCenterKey] <== Not Yet Added


Fact
Claims
PersonincidentKey
DateKey
[CostCenterKey] <== Not Yet Added


I have an existing Dimension CostCenter which in the operational table has a foreign key to the PersonIncident Table.

I was wondering if I should make the Foreign key directly to the fact table from cost center or should I keep the Cost Center Dimension foreign key reference in the PersonIncident Dimension ? A person Record can only be associated to one cost center so I think I could answer the same questions either way. I was trying to avoid snowflaked dimensions if possible.

So I wasn't sure what the tradeoffs are of going one route vs another, especially performance wise ?


Thank you.

[ngalemmo]

I would make person and incident independent dimensions off the fact. The fact table would naturally infer the person and incident relationship.

[mru22]

How about the Cost Center ? Since it was originally a foreign key in the Person, Should I key it into the fact directly as well ?

[Sponsored content]