Nearby Locations in a Location Dimension

View previous topic View next topic Go down

Nearby Locations in a Location Dimension

Post  miommi on Wed Aug 26, 2015 8:46 pm

Hi

I have a Location dimension that contains a Nation, State, City, Suburb hierarchy. The business has want to be able to report on activity within a suburb and its surrounding suburbs. They have defined what the nearby suburbs are for each suburb in variable length lists. I am not sure of the best way to implement this in my dimension. I do not want to simply add a new column to store the nearby suburb and replicate the row for each nearby suburb as this would make it messy to record the activity against a specific location. I am also not keen on adding multiple 'Nearby Suburb' columns eg Nearby Suburb 1, Nearby Suburb 2, Nearby Suburb 3 etc as this will make reporting difficult.

I am leaning towards a bridge table that links the Location dimension to a second, shrunken version of the Location dimension for the Nearby Locations.

The other option I have considered is an Outrigger table on the dimension.

Can anybody help determine which of these options is better or, if there is a better solution I should be considering

Thanks

Mark

miommi

Posts : 6
Join date : 2014-06-18

View user profile

Back to top Go down

Re: Nearby Locations in a Location Dimension

Post  ngalemmo on Wed Aug 26, 2015 8:52 pm

Its a bridge. But all it contains is the relationship: two foreign keys. You don't create a "shrunken version" of the dimension.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Nearby Locations in a Location Dimension

Post  miommi on Wed Aug 26, 2015 9:02 pm

Thanks for your prompt response. Would the bridge be between the Fact and the Dimension or self referential on the Dimension only please?

miommi

Posts : 6
Join date : 2014-06-18

View user profile

Back to top Go down

Re: Nearby Locations in a Location Dimension

Post  ngalemmo on Wed Aug 26, 2015 9:37 pm

No reason it could not be used for both.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Nearby Locations in a Location Dimension

Post  miommi on Wed Aug 26, 2015 10:58 pm

Thanks

miommi

Posts : 6
Join date : 2014-06-18

View user profile

Back to top Go down

Re: Nearby Locations in a Location Dimension

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

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