Wednesday, July 22, 2015

Roadshow - Route Setup

Roadshow Connection/Integration Setup

1.    ODBC Setups

a.      A MAPDB ODBC must be setup to connect to the Roadshow db or roadshow will not launch
b.      For the integrations, an ODBC must be setup to connect directly to the BWPL database
c.      Login as sqladmin
d.      Use Default dataset
e.      Uses the BW_ROImport_Orders for all orders
f.       Uses the BW_ROImport_Customers for all customers

2.    Create Product Classes

a.      If you do not do this step, you will get the error “Commodity Type does not exist” when you attempt to import your orders
b.      The commodity type is similar to an Item Class that contains UofM, Weight and Volume defaults depending on the item class
c.      We will need to create a separate product class for every single item
d.      Open Roadshow
e.      Click View>Data Manager
f.       Click Products on the bottom bar of buttons
g.      Click Edit>New Record
h.      Create an item with a default Commodity Class and Commodity Type of “C”

3.    Create Items

a.      Items can be imported from the orders from the order item number field, however the actual weights and volumes for the item have to be set by assigning the item to a commodity code, then defining weight and volume modifiers for each item if it does not conform to a commodity type
b.      These additional modifiers cannot be imported

4.    Setup Route Services

a.      Routes>Services>Route Set Services
b.      Select Route Set>Global Rename

5.    Create Vehicle Type

a.      View>Data Manager>Vehicle Type
b.      Edit>New

6.    Create Vehicle

a.      View>Data Manager>Vehicle
b.      Edit>New
c.      Fill in route name and tracking number with actual route it will be servicing

7.    Create Depot

a.      Tools>Network\Customers>Depot
b.      Network>New
c.      Click on map to set depot location
d.      Select depot
e.      Click Network>connect
f.       Connect the depot to the route by clicking on the yellow customer site on the route
g.      Connecting the depot will enable the “process” button

8.    Setup Imports

a.      Launch Roadshow enterprise
b.      Click on File, Import (this can be automated, update this doc)
c.      In the drop down menu at the bottom, select the type of import you want to setup
d.      Create a new connection
e.      Select odbc
f.       Select the odbc to connect to BWPL
g.      Enter the SQL statement to grab the data you need
i.       This must be done for Customers and Orders
j.       OrderHdrandLine Import
      'E' as [import command]


      ,[DeliveryDate],[deliverydate] as Date2,0000 as opentime, 2359 as closetime, Itemnumber, 1 as Multiday,'xxxxxx ' as ServiceDays
  FROM [BWPL].[dbo].[BW_ROImport_Orders]

9.    Get customers to a valid location on the map if the Geocode is wrong

a.      View>Network>Stops
b.      Double click on a stop
c.      Click “Clear Location” to remove current location
d.      Move the map to the location you want
e.      Double click on the stop to open the stop properties window for that stop
f.       Click on the map to set the location
g.      Click Apply to save the location

10.                   Setup Active time Windows

a.      Double click on Customer
b.      Customer Details>time windows
c.      Enter time windows>click Update

11.                   Connect new Customers to Network

a.      View>Network
b.      Tools>Autoconnect

12.                   Setup Load Splitting

a.      Tools>Routing Parameters
b.      BW>Order Splitting>Split Load Routing
c.      Setup options as required
d.      To set if loads are split based on volume or weight, go to
                                                    i.     Tools>Routing Parameters>Route Limits>Capacity Limitation>Both


13.                   Process Orders

a.      The routing date controls how orders are grouped together for processing sets
b.      Use the priority field to control which orders are loaded first
c.      Tools>Process Orders
d.      Click Process
e.      Select Depot - OG
f.       Select Parameter Set - BW
g.      Click service time formulas, select BW
h.      Setup customer types and different times each customer takes to service
i.       Apply and save
j.       Set split options here, tick split load routing

14.                   Auto-Route

a.      Go to View>Route Planner
b.      Go to tools>Auto-route
b.      Tools>Autoroute>Vehicle Manager>Add vehicles to depot
                                                    i.     Drag vehicles from one route to another
c.      Select parameter set “BW”
d.      Click Route
e.      This will generate the route
f.       Click Show to get a route list
g.      Click View>Route Planner>Press f4 to see the route
h.      Right click on red line>show path to show actual path for driver
i.       Click save to save the route
j.       This can then be exported to a flat file, or connect to the database directly via views to pull the data

15.                   Assign Vehicles and Drivers

k.      Click 2nd icon to open route planner
l.       Click Routes>Services>Route Services
m.    Select Route
n.      Scroll to the right, enter vehicle type, vehicle and driver
                                                    i.     Or click Assign Drivers and Assign vehicle
o.     Click dispatch
p.      It will renumber the route, and dispatch it
q.      Save and close RS
r.       Export the file, and it will populate all the tables

16.                   Export Format

b.      route: dispatch date
c.      route: name
d.      stop: sequence number in route
e.      order: order number
f.       order: order size
g.      order: volume
h.      We are now reading directly from the BW_RO_OrderExport View.
                                                    i.     Any null route_indexes means that line items for that order was not routed

17.                   Geocode Conversion

a.      BW is recording geocodes in the following format
                                                    i.     Lat: N39.637 Long W23.526
                                                   ii.     These can be converted to Degrees,Minutes,Seconds using the following formula
1.      N = 10 Degrees, W = -61 Degrees
2.      Minutes = digits before the decimal
3.      Seconds = 60 x digits after the decimal
                                                  iii.     Conversion example
1.      N = 10, W = 61
2.      39.637 = 39 Minutes, 23.526 = 23 Minutes
3.      .637 = 60x0.637 = 38.22 Seconds, .526 = 60x0.626=31.56 Seconds
4.      N39.637 = 10d 39’ 38.22” , W23.526 = -61d 23’ 31.56”
                                                  iv.     To further convert to decimals
1.      Absolute values are used, and then converted to East or West
2.      East = 1, West = -1
3.      (Degrees + Minutes / 60 + seconds / 3600) * (E or W)

18.                   Command Sets

a.      Command sets stop running if there is an error
                                                    i.     We need to process each routing class, and export each set to a separate truck
                                                   ii.     We need
1.      Process C, process H, process K
2.      Export all as one large file with all routes and stops for each truck

19.                   Questions for Roadshow

a.      What is the correct format to import the customer’s geocodes?
                                                    i.     Lat – 5 digit decimal – decimal degrees
                                                   ii.     Long – 5 digit decimal – decimal degrees
b.      Item weights and volumes in extender?
                                                    i.     Extender – build weights, L inches, W inches, H inches
c.      How can I set it up so that if a route cannot deliver to all customers within the service times, it creates another route?
                                                    i.     This depends on how many vehicles are setup, and the service times and drive time parameters
d.      If I have 2 depots. In a single route, It loads from 1 in the morning, and the other in the afternoon, how do I configure this?
                                                    i.     This would have to be done manually as two processes. Each would begin at a diff depot.
e.      Can I set the default route numbering sequence to 201 and have it increment automatically?
                                                    i.     No, must have 4 digits minimum
f.       How do I default the order processing to use a specific Depot(OG) and Parameter set (BW)?
                                                    i.     Will only need 1 depot, do not setup others
g.      How do I default the parameter set used in the Auto-Route(BW)?
                                                    i.     *SESS* means the last parameter set you used, so if you used BW, it will continue to use BW
h.      Are we automating any of the tasks? What is being automated, and what is being done manually?
                                                    i.     TBD
i.       How do  I automate
                                                    i.     Imports – yes, by name
                                                   ii.     Order processing – yes, by date
                                                  iii.     Auto Connect New customers to network - yes
                                                  iv.     Auto Routing – can be done, but will most likely remain manual
                                                   v.     Exports – yes, by name
                                                  vi.     User guide has scripts

Import Source Script

      'E' as [import command]
      ,[DeliveryDate],[deliverydate] as Date2,0000 as opentime, 2359 as closetime, Itemnumber, 1 as Multiday,'xxxxxx ' as ServiceDays
  FROM [BWPL].[dbo].[BW_ROImport_Orders]

Process Orders Options
Aggregate orders: select to aggregate orders during processing. Aggregating combines orders that share the same routing date, class, set, vehicle stop, commodity class, and order type, and that have compatible delivery time windows.
Tip— Aggregating generally produces the best routing results, because an aggregate order cannot be split over multiple routes (unless split-load routing is used).
Establish service time via formulas: when selected, Route Planner RS processes the order set using the standard service time formulas that have been defined on the Service Time Formulas tab; when not selected, Route Planner RS assigns each order a service time based on service times in the product database and customer/stop service times shown in the Network/Customer module.
Note— You can apply service time formulas to a processed order set at any time, even if it was not processed using this option. (See Applying a Set of Service Time Formulas).
Note— During processing, Route Planner RS calculates the service time for aggregated orders based on whichever is greater: the number entered in the Min. aggregate order service time (min) field, or the combination of the order's per unit service time and customer and stop service times. To ensure a certain minimum service time for aggregated orders, enter a number of minutes here.
List un-connected stops (for use with Standard Road Networks only): Route Planner RS lists stops in the order set that have temporary connections. During order processing, Route Planner RS creates temporary network connections (either stop-to-stop or stop-to-underlying network point) for any stops in the order set that were not connected in the Network/Customer module. These temporary connections let the stops be routed.
Remove 1-way streets: when selected, Route Planner RS routes as though all streets are two-way; useful for routing sales people in dense areas where most travel between stops is done by walking.
Source/follow-up order naming convention: used to identify pairs of orders that must be serviced on the same route in a particular sequence. This is normally used to represent a pick-up at a stop followed by a delivery at a second stop. It may also be used to represent two visits to the same stop on the same route that must be done in a particular sequence. When this option is selected, Route Planner RS looks for pairs of orders whose order numbers use the follow-up/source order naming convention, in which a source order and its follow-up order are given numbers that are identical except for the last character (S for source, F for follow-up). When it is not selected, Route Planner RS uses the source-order customer's identification on the follow-up order to identify the corresponding source order.
Split load routing: Use this option when two vehicles may be needed to service one aggregate order in cases where it would improve loading efficiency or where an order is larger than a truckload. Route Planner RS creates a ‘dummy’ duplicate aggregate order for each aggregate order that allows the order to be split into two parts of different sizes where each part may be serviced by a different route.
Sum sales values during aggregation: determines aggregate order sales value; default aggregate order value takes first sales value imported among aggregate orders.
Track unprocessed orders: select first time you process an order set if it is likely more orders will be added to the database after the order set is processed; allows you to find out later whether orders have been added to the set. Use this option only if your orders come in gradually. If you import all of your orders into Route Planner RS at the same time, it is not necessary to track which have not been processed.
Vehicle type/region restrictions (for use with standard road networks only): prevents vehicles of certain types from being routed into particular regions; you must first restrict vehicle types in the Data Manager module; then restrict regions in the Network/Customer module. When this option is selected, Route Planner RS does not route a vehicle that is of a restricted type into a restricted region. If this option is not selected, vehicles of all types are routed into all regions.
Windows spanning start of day adjustment: adjusts time windows for orders in set so deliveries can begin on either of two consecutive days. When it is not selected, Route Planner RS checks the customer/stop time windows on either side of the start-of-day time and routes the order on the day that has the larger time window. When it is selected, Route Planner RS
• Turns each stop's existing hard window into a soft window.
• Gives every order in the set a new hard window of 00:00 to 23:59.
• Uses the new soft window times to permit deliveries to begin on either of two consecutive days.
Note— To use this option, you must also set a high soft-window penalty cost in the parameter set you are using for routing. In the parameter set, go to the Route costs group, then to the Soft window penalty per hour field.
Deadline arrival time based on service time: Route Planner RS can adjust customer/stop time windows in the processed order set to ensure that orders arrive in enough time for service to be completed before the stop closes. Under Deadline arrival time based on service time, apply one of the following options to be applied to the entire processed order set:
Hard windows only: Route Planner RS adjusts closing times of all stops in the processed order set by subtracting service time from customer/stop hard window end time. For example, if a stop closes at 11:00, but takes 30 minutes to service, the delivery must arrive at 10:30 in order for service to be completed. Route Planner RS adjusts the stop's close time accordingly.
Hard and soft windows: Route Planner RS adjusts both soft window open times and hard window close times.
None: Route Planner RS assumes that if a driver arrives at stop before the time window ends, service can be completed; time windows are not adjusted.
Note— Window times are adjusted only within the processed order set, not in the database. The adjustments are shown in the Route Planner window spreadsheet for the processed order set.
Use historical data for: Route Planner RS can use actual historical data stored in the feedback tables to calculate on-site service times and stop-to-stop driving times when it is processing an order set. (If there are no historical data available, Route Planner RS uses the network and standard service time computations.)
Tip— This feature is particularly useful for integrating on-board data. Select Service times, Driving times, or both; then specify how many day's worth of actual information should be considered.
Note— For information about deleting or updating the historical data that is used during processing, see Appendix E: Maintaining Historical Data.
Road network type:
Standard: uses the standard Route Planner RS network.
None: does not use a network when processing - fast way to process orders with estimated travel times/distances.
External: uses external network for paths, travel time and distance calculations. External Network parameters on the Auto-Route window are used to adjust travel speeds for different road classifications in an external network. See the section External Network Parameters for details.
Accelerated Standard: uses the standard Route Planner RS network, but does not load any vehicle stop links from the database. Use in-memory auto-connect to create the vehicle stop-network connections.
Note— If you are using a large Route Planner RS network (which presumably is very detailed), this option significantly improves the speed of Order Processing with little loss in accuracy if you have stored network connections in the database.