Transport Fact Table

View previous topic View next topic Go down

Transport Fact Table

Post  netpoint on Thu May 24, 2012 4:10 pm

Hi,

I have a fact table with a grain of job with Collection, Delivery Addresses, Job types and various date dimensions. Each job as facts, sales value, number of pallets and cost.

Each job consists of a number of stages, collect from customer and return to hub - Leg 1. Deliver from hub to delivery address -Leg 3, Some jobs have Leg 3 if the delivery fails - Return to hub. Each leg may use different vehicles. We want to analyse which vehicles are the most productive (also drivers). Drivers use different vehicles. We want to allocate 0% of job value to Leg 1 and 100% to Leg 3 but also split out failed deliveries.

I am unsure whether to create a second fact table at the job leg grain or change the current fact table to the job leg grain.

Any advice would be appreciated.

Thanks
Steve

netpoint

Posts : 3
Join date : 2012-05-24

View user profile

Back to top Go down

Re: Transport Fact Table

Post  gsidhu on Thu May 24, 2012 5:43 pm

Can there be more than three legs or is that the maximum number?

gsidhu

Posts : 10
Join date : 2012-05-10
Location : Southern California

View user profile

Back to top Go down

Re: Transport Fact Table

Post  ngalemmo on Thu May 24, 2012 7:19 pm

If job level measures are simply summaries of leg level data, you should always capture data at the highest grain possible. In this case, leg is a dimension. If you wish to display this data in a flattened image, use a view.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Transport Fact Table

Post  netpoint on Fri May 25, 2012 1:41 am

Yes each job will have either 2 or 3 legs no more.



netpoint

Posts : 3
Join date : 2012-05-24

View user profile

Back to top Go down

Re: Transport Fact Table

Post  netpoint on Fri May 25, 2012 1:51 am

I want the vehicles to be a dimension, so I can summarise all the legs each vehicle has done

Original data structure summary

JobData Table

Jobnumber
Jobvalue
No of packages
Collection date
Delivery Date
Various Address fields

Loads Table

Jobnumber
Stage (1,2 or 3)
Vechicle (i.e. Registration)
Driverid

Drivers
DriverID
DriverName

netpoint

Posts : 3
Join date : 2012-05-24

View user profile

Back to top Go down

Re: Transport Fact Table

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