Location Dimension for locating different types of infrastruture
Page 1 of 1
Location Dimension for locating different types of infrastruture
Greetings ,
I am in the process of designing a traffic analysis fact and have the following design in mind...
the dims are - date , time , vehicle type , traffic_device and location
facts - counts , average speed
The orginal design contained had devices only and yes now we have new requirements for additional 'assets' or 'infrastructure'
like bridges , roads , as well as incidents or crashes.
Now the original dim_location design is looking shaky , it currently contains geospatial attributes and it's using the traffic_device id as the business_key , with the advent of new location types this design looks flawed.
I've taken some design tips from the transporation example in DW Toolkit i.e. Location Outrigger and come up with the following
Ok here are the questions !
There have been robust discussions about a generic asset dimension to cater for all types of infratructure , to me this is not really the real world so I'm arguing specific dims for each category of infrastruture.
The use of views , is this a good approach , is it a performance killer ?
I'm also thinking ahead to people and places and where they are located and wondering wether my design would cater for these requirements i.e. dim_person , with location attribute and associated view to respresent dim_person_location.
If that all makes sense I'll be happy!
Thanks for your time.
Regards
Graeme
I am in the process of designing a traffic analysis fact and have the following design in mind...
the dims are - date , time , vehicle type , traffic_device and location
facts - counts , average speed
The orginal design contained had devices only and yes now we have new requirements for additional 'assets' or 'infrastructure'
like bridges , roads , as well as incidents or crashes.
Now the original dim_location design is looking shaky , it currently contains geospatial attributes and it's using the traffic_device id as the business_key , with the advent of new location types this design looks flawed.
I've taken some design tips from the transporation example in DW Toolkit i.e. Location Outrigger and come up with the following
- location SK becomes an attribute of dim_device , dim_bridge , dim_crash_location etc.
- use a composoite BK on master dim_location representing source + device_id , bridge_id etc depending on the location_type
- from there create views over the master dim_location to create specific dim_device_location , dim_crash_location outriggers that can joined directly to the fact ( using the master dim_lcoation SK )
Ok here are the questions !
There have been robust discussions about a generic asset dimension to cater for all types of infratructure , to me this is not really the real world so I'm arguing specific dims for each category of infrastruture.
The use of views , is this a good approach , is it a performance killer ?
I'm also thinking ahead to people and places and where they are located and wondering wether my design would cater for these requirements i.e. dim_person , with location attribute and associated view to respresent dim_person_location.
If that all makes sense I'll be happy!
Thanks for your time.
Regards
Graeme
melgra- Posts : 1
Join date : 2011-04-12
Similar topics
» Location in Each Dimension?
» location dimension
» Location Dimension(s) with multiple hierarchies
» Master Data and Dimension location
» Nearby Locations in a Location Dimension
» location dimension
» Location Dimension(s) with multiple hierarchies
» Master Data and Dimension location
» Nearby Locations in a Location Dimension
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum