Foreign key reference

[hasooyeh]

We have a Location dimension which is at store granularity. Stores can roll up to districts and then regions. The business key is the Location code and we use a surrogate key as the primary key.

Store	District	Region
1	1	1
2	1	1
3	2	2
4	3	3

We have a new dimension called DimCustomerOwner. An owner can be either a location or a person. Originally it was thought that if the owner were a location, it would only be at store level, so we could reference the primary key from DimLocation. However, a district or a region can be an owner. My question is, do I create a hierarchy helper table in order for district and region to have a primary key? Or is it okay to use the attribute fields, District_Code or Region_Code, on the DimCustomerOwner table? (We have a field called Owner_Type where we can distinguish between Location, District, or Region since for example, '1' can be a store, district, or region.)

Thanks!

[ngalemmo]

If a person was not involved, I would recommend the hierarchy bridge approach and build out an 'org unit' dimension that includes location, district and store as individual entries.

Trying to do it based on attributes is cumbersome and inflexible.

But how does 'person' fit in? Are these specific individuals, possibly outside the company, or are they positions relating to an org unit?

[hasooyeh]

Thanks for the reply!

We will actually have two fields, one to reference a location and one to reference a person. Regarding the person, it will be somebody within the company.

Essentially, one field on DimCustomerOwner, either the Location or Person, will be blank for each record because a customer must have one or the other, not both.

So my hierarchy helper table would contain the fields below, correct?
Surrogatekey, Location_Code, Location_Type

[Sponsored content]