Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Location Dimension(s) with multiple hierarchies

Go down

Location Dimension(s) with multiple hierarchies Empty Location Dimension(s) with multiple hierarchies

Post  dwuser30 Mon Aug 19, 2013 5:44 pm

The modeling project pertains to a transportation company that moves goods via rail and/or road. Their location data seems to have several different hierarchies. What is the recommended way of modeling such multiple hierarchies? Here are some example hierarchies:

Country --> Region --> Area --> Markets --> State --> City
Country --> Zones--> Sub zones--> Location (a combination of city, state, zip)
County -->  State--> City -->  Lane (a lane is the route between two road points or two rail points)
Zone --> Pricing Region-->  Zip
Areas -->  Markets -->  Rail Ramp
Zip -->  Lanes
…other geographical and custom hierarchies involving location.

One option is to have a separate dimension for all lowest leaf members i.e. Rail Ramps, Cities, Lanes, etc. and ensure that all possible different hierarchies from related parents are contained within each dimension. All such dimensions would be linked to the FactOrder. Not sure if this is the right approach. The current OLAP layer (SSAS cubes) seems to have built and exposed several custom location hierarchies from a given parent level to any child level. And I am trying to figure out how to best model multiple hierarchies in the dimensional model.

With the above option, the geographical data for parent leaf members like state, regions, areas, zone, networks, etc. could be repeated in each hierarchy within each dimension they are used. Is that ok?

Any thoughts or guidance would be appreciated. Thanks in advance.

dwuser30

Posts : 7
Join date : 2010-08-29

Back to top Go down

Back to top

- Similar topics

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