Location Dimension(s) with multiple hierarchies
Page 1 of 1
Location Dimension(s) with multiple hierarchies
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.
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
Similar topics
» How to store multiple hierarchies within a dimension
» Dimension hierarchies having One child multiple parent
» Attributes from a dimension appearing in other dimensions; multiple-inheritance in hierarchies.
» Product DIM with multiple hierarchies
» Splitting hierarchies and hierarchy levels into multiple dimensions.
» Dimension hierarchies having One child multiple parent
» Attributes from a dimension appearing in other dimensions; multiple-inheritance in hierarchies.
» Product DIM with multiple hierarchies
» Splitting hierarchies and hierarchy levels into multiple dimensions.
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum