Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Transport Fact Table

3 posters

Go down

Transport Fact Table Empty Transport Fact Table

Post  netpoint 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

Back to top Go down

Transport Fact Table Empty Re: Transport Fact Table

Post  gsidhu 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

Back to top Go down

Transport Fact Table Empty Re: Transport Fact Table

Post  ngalemmo 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.
ngalemmo
ngalemmo

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

http://aginity.com

Back to top Go down

Transport Fact Table Empty Re: Transport Fact Table

Post  netpoint 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

Back to top Go down

Transport Fact Table Empty Re: Transport Fact Table

Post  netpoint 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

Back to top Go down

Transport Fact Table Empty Re: Transport Fact Table

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum