Foreign key reference

View previous topic View next topic Go down

Foreign key reference

Post  hasooyeh on Thu Jul 08, 2010 2:44 pm

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 DistrictRegion
1 1 1
2 11
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!

hasooyeh

Posts : 5
Join date : 2009-02-25

View user profile

Back to top Go down

Re: Foreign key reference

Post  ngalemmo on Thu Jul 08, 2010 5:51 pm

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?
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

RE: Foreign key reference

Post  hasooyeh on Thu Jul 08, 2010 6:47 pm

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

hasooyeh

Posts : 5
Join date : 2009-02-25

View user profile

Back to top Go down

Re: Foreign key reference

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top


 
Permissions in this forum:
You cannot reply to topics in this forum