This page describes the configuration tables, staging procedures, and loading procedures used to manage data extraction and reporting in MRM BI.

Dispatch System Database Configuration

Dispatch System Database Configuration refers to the database of the product used to dispatch routes (Descartes Route Planner, Descartes Route Planner On-demand). The MRM BI database provides several tables to control the behavior of the system. All of these tables belong to the schema BIStaging. When querying these tables, the schema prefix must be provided.

BIStaging.BISysValue

This table contains the System Values configuration settings.

Setting	Default Value	Description
LoopbackServerName	BILoopback	Name of the linked server to use for logging. Specified during the installation process.
StagingDatabase	MRM_BI	Name of the database to which extracted data is staged. Usually the same database used for reporting.
StagingServerName	NULL	Name of the linked server to use for connecting to the Staging database. If null, the system assumes the local server (Default).
StagingCleanupProc	BIStaging_RPCleanStagedData	When performing an "update" the system looks for overlapping data. This process will differ according to the dispatching system and should not be manually updated.
StagingValidateSchemaTableName	BIStaging_RPOrder	This table is used for the validation check when connecting to the staging database. This process will differ according to the dispatching system and should not be manually updated.

BIStaging.FWBIUdfExtractionList (Descartes Route Planner Only)

This table contains the list of user-defined fields (UDFs) that should be extracted. By default, all UDFs are disabled. Only UDFs relevant to reporting should be extracted to reduce the size of the reporting database and minimize data transmitted via linked server.

DocumentType: Which entity for which the UDF is enabled/disabled.
UDFString[1-9]: A BIT — when 1 the UDF will be extracted; when 0 it will not be extracted (default).
UDFInteger[1-3]: A BIT — when 1 the UDF will be extracted; when 0 it will not be extracted (default).
UDFNumber[1-3]: A BIT — when 1 the UDF will be extracted; when 0 it will not be extracted (default).
UDFDateTime[1-3]: A BIT — when 1 the UDF will be extracted; when 0 it will not be extracted (default).
UDFURL[1-3]: A BIT — when 1 the UDF will be extracted; when 0 it will not be extracted (default).

BIStaging.BITimePeriods

This table defines how data should be chunked for extraction. Older time periods should be extracted less frequently; more current time periods can be extracted more frequently but should cover a smaller range.

TimePeriod	FirstDayOffset	LastDayOffset	Description
-3	-180	-31	Data older than 30 days, up to 6 months. Should not be extracted more than once a week; often only done once or following an upgrade.
-2	-30	-8	Data older than 1 week. Should not be extracted more than daily.
-1	-7	-2	Data older than the previous day. Should not be extracted more than 2-3x daily.
0	-1	3	Data from previous to next few days. Typical is daily to hourly.
1	4	7	Data more than a few days out. Typical is daily to hourly.
2	8	60	Data more than a week out. Typical is daily.

Note: Each time period defined requires a separate extraction job.

Customizable Columns

Certain columns in the application are customizable after deployment. These modifications are performed by altering the appropriate inline Table-Valued Function (TVF). The Filter Date values referred to below are used to determine the internal table to which data will be saved.

Business Order Key: The business order key provides a unique key that is meaningful to the business. By default, the business order key is the RootOrderKey for Descartes Route Planner and the Custom field for Descartes Route Planner On-demand. This setting can be controlled by modifying the [BIStaging].[BIGetBusinessOrderKey_Inline] TVF in the dispatching system database.

Route "Filter Date" (Descartes Route Planner only): The route date determines the date of the route and all its stops for filtering purposes. By default, the route date is based on the EarliestStartDate. This setting can be controlled by modifying the [BIStaging].[BIGetRouteTimePeriodDate_Inline] TVF in the dispatching system database.

Unrouted Order "Filter Date" (Descartes Route Planner only): The order date determines the date of the order when unassigned for filtering purposes. By default, the order date is based on the CloseDateTime if provided, otherwise it uses LatestDate. This setting can be controlled by modifying the [BIStaging].[BIGetOrderTimePeriodDate_Inline] TVF in the dispatching system database.

Staging Procedure

The extraction of data is managed by the staging wrapper procedure, [BIStaging].[BIStagingTaskWrapper]. As described above, data can be scheduled for extraction in multiple time slices controlled by the [BIStaging].[BITimePeriods] table. Each time slice can be scheduled separately or called sequentially in a single script by specifying the @LastTimePeriod parameter.

Parameter	Required?	Description
@TimePeriod	Required	Which time slice of data to extract as defined by [BIStaging].[BITimePeriods].
@LastTimePeriod	Optional, Default null	If specified, the procedure will loop through @TimePeriod and @LastTimePeriod performing each extract in sequence. Note: All time periods will have the same SessionID.
@SyncFilter	Optional, Default null	When provided, filters Org Unit data to be extracted. Uses standard "LIKE" criteria (e.g. %, _, etc.). Route Planner: filters by ScheduleKey. OnDemand: filters by Location (OD Location, not MRM BI location).
@LastSync	Optional, Default null	When not null, extracts only entities modified since the @LastSync datetime provided. Note: This feature is experimental and should not be configured in production. Note: Not supported for OnDemand.
@TargetServer	Optional, Default null	If provided, overrides the StagingServerName defined in BIStaging.BISysValue.
@TargetDB	Optional, Default null	If provided, overrides the StagingDatabase defined in BIStaging.BISysValue.
@ReturnRecords	Optional, Default 0	If set to 1, the IDs of the entities being extracted will be output to the caller to aid in troubleshooting.

Reporting Database Configuration Tables

Config.BIProducts: This table lists products that the system is aware of. This list was initially provided at the time of installation and determines which staging tables can be loaded into the reporting tables.

Column	Description
Product	Product to load for reporting.
IsOwning	Determines whether the system can "own" the entities imported. Only owning systems can delete records.
DoImport	Determines whether staged data for the product can be loaded into the reporting tables.

Config.BIProductsConfig

This table lists product-specific configuration parameters and determines the formatting of data being loaded into the reporting database.

Product	ConfigKey	Values
RoutePlanner	WglnAggregationMode	-1 (Default) — Use NewStop to determine "Stop" 0 — No aggregation; each job is a "Stop" 1 — Aggregate by geostop+locationkey, similar to wGLN
OnDemand	TimeWindowJeopardyThresholdMin	15 (Default) — Number of minutes prior to close of the window considered a "warning".
OnDemand	TimeWindowOnTimeMode	1 (Default) — Job must both arrive and complete within the time window to be on-time. 2 — Job must finish within the window but can start prior to it. 3 — Job must finish before the close of the window; does not violate if prior to time window start.
OnDemand	CheckCapacityViolation	0 (Default) — Does not check for capacity violations. Required for customers not tracking capacity. 1 — Checks if onboard capacity exceeds vehicle capacity (Weight, Volume, Item).

Config.BISysValue

This table contains the list of global configuration values for the MRM BI reporting database.

Keyword	Type & Default Value	Description
MinPeriodToKeep	INT: -12	Maximum number of months to retain data (negative = past).
MaxPeriodToKeep	INT: 3	Maximum number of months to retain data (positive = future).
SetIOITableName	INT: 0	When 1, the internal table rows are updated with the table name to aid with debugging.
LoopbackServerName	STR: BILoopback	When logging, a loopback linked server is required. This can be shared between multiple instances of MRMBI. Provided at install time.
LoadingServerName	STR: NULL	Default server to load data into (beta). It should point to itself.
LoadingDatabaseName	STR: NULL	Default database to load data into (beta). It should point to itself.

Loading Procedure

The load of data into the reporting database is managed by the loading wrapper procedure, [BIStaging].[BILoadingTaskWrapper]. It can process a single Extract Session/TimePeriod or can be configured to process all unloaded sessions.

Parameter	Required?	Description
@ExtractSession	Optional, Default null	If specified, will process the provided session only (and all its associated time periods). If null, will loop through all unprocessed sessions.
@ForceMode	Optional, Default 0	Controls behavior if the session has already been loaded. 0 — Don't allow reprocess 1 — Allow reprocess, skip delete 2 — Reprocess & delete
@MaxSessionsToProcess	Optional, Default 0	When > 0 and there are multiple time periods for the session or the session is not specified, will loop through only the specified number of sessions.
@SkipInternalSchemaCheck	Optional, Default 0	When 0, evaluates the Min/MaxTimePeriodToKeep and creates/drops internal tables as required. When 1, this step is skipped to accelerate loading time. Note: If skipped, the Schema check needs to be scheduled separately.
@BuildTableDebugLevel	Optional, Default 1	If provided, overrides the @DebugLevel when calling the Internal Schema Check. Only applies when @SkipInternalSchemaCheck is 0.
@TargetServer	Optional, Default null	If provided, overrides the LoadingServerName defined in BIStaging.BISysValue.
@TargetDB	Optional, Default null	If provided, overrides the LoadingDatabase defined in BIStaging.BISysValue.
@ReturnRecords	Optional, Default 0	If set to 1, the IDs of the entities being extracted will be output to the caller to aid in troubleshooting.
@AutoCleanupStaging	Optional, Default 0	If specified > 0, automatically cleans up old sessions after loading. Determines the @CleanupLevel when calling the purging procedure.
@AutoCleanupDaysToKeep	Optional, Default 30	If @AutoCleanupStaging is 1, determines the @DaysToKeep when calling the purging procedure.

Configuration & Maintenance