Location Dimension for locating different types of infrastruture

View previous topic View next topic Go down

Location Dimension for locating different types of infrastruture

Post  melgra on Thu Apr 14, 2011 4:16 am

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

  • 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 )
[/list]

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

View user profile

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