Transport Fact Table

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

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

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.

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

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