Summary

Descartes Mobile Resource Management Business Intelligence™ (MRM BI) is a unified reporting database that represents routing information from various Descartes routing products in a single unified structure. At this time, the two products supported are Descartes Route Planner™ and Descartes Route Planner On Demand.

Data is extracted from the various routing systems through a series of procedures as described in the workflow below. This data is available for presentation through Descartes Reporting Services or can be used by other custom reporting packages. A set of standard Descartes Reporting Services reports and SQL views are available as a template or framework to meet business objectives.

Terminology

Entities

The reporting database is grouped into several logical entities similar to the Descartes wGLN™ model of a route.

Routing data

Route: Represents the logical representation of a series of tasks performed by a truck and/or driver.
Stop: Represents a collection of activities performed at a place.
Job: Represents an individual task consisting of a service or items (e.g. pickup, delivery, break, rest, etc.).
Location: Represents the place where a stop or job can occur. Can also be the domicile location of a route/asset or a place where a route can go to load or unload items.

Asset data

Drivers: Crewmembers that can be associated with a route; one is designated the Lead (Driver). Only the lead is tracked within the MRM BI database, however a count of crewmembers is available at the route level.
Trucks: A tractor or straight truck for picking up or delivering goods that can be associated with a route.
Trailers: A trailer (usually in combination with a truck/tractor) that can be associated with a route.

Organizational data

Partitions: A unique way of identifying where the data came from, e.g. which database, which product, etc.
Org Units: A way to subdivide the data within a partition, e.g. a Schedule for Descartes Route Planner or a Location for Descartes Route Planner On-demand.
Partition/OrgUnit Access: An extract of the dispatching system security configuration from which data is extracted, e.g. Schedule versus User Group associations for Descartes Route Planner or Location/Region associations for Descartes Route Planner On-demand.

Workflow

The system uses a multi-step process to efficiently extract data from the dispatching system and format it into a standardized structure for reporting. The following diagram illustrates this process:

Identifying Data to Extract

First, the system identifies which key records should be extracted for reporting (Route, Order). The records are retrieved along with all related entities by type (Schedule, Order, Resource, Truck, Driver, or Trailer) and the associated ID. Several parameters control the extraction of data:

Filtered by Location (On Demand) or Schedule (Route Planner)
Filtered by time period (e.g. older than a week, last week, last few days, etc.)

The process then identifies all the core reporting elements associated with the entities.

Data Extraction for Reporting

Once identified, the data summary is extracted into a staging table in the reporting database. The destination database or server is parameterized. If this database is not on the SQL Server used by Descartes Route Planner, a linked server connection to the reporting database server is required.

Loading the Data for Reporting

Once the data has been staged from Descartes Route Planner, the system loads the data into the final reporting schema tables and transitions it into time period tables.

Reporting

Descartes Reporting Services

For customers using Descartes Reporting Services, a set of pre-packaged Data Sources, Caches, and Reports are available for use. These should be used as templates if modifications are required; future upgrades may update these items, removing any customizations.

Please see the Mobile Resource Management BI Database — Descartes Reporting Services Overview document for more details on these reports.

Reporting without Descartes Reporting Services

For customers using a product other than Descartes Reporting Services, a set of Data Sources are available. These Data Sources are contained in the Data Source schema of the MRM BI database. They can be used as a template for customization but should not be directly modified, as they may be updated (replaced) by future upgrades.

These data sources are:

DataSource.BIAssetLastKnownLocation: Provides a per-asset (Truck, Trailer) dataset including key attributes as well as the last position and timestamp generated via mobile messaging.
DataSource.BIRouteSummary: Provides a per-route dataset summarizing key planned, projected, and actual metrics with one row per route.
DataSource.BIRouteDetail: Provides a per-job dataset summarizing key planned, projected, and actual metrics for both the route and job with one row per job.
DataSource.BIOrderLookup: Provides a per-order dataset summarizing key properties of an order and its associated jobs with one row per job.

Solution Overview