This page describes the database schemas and components that make up the MRM BI solution.
Databases and Schemas
The MRM BI solution is comprised of two or more databases: the "Dispatch System" database associated with a Descartes routing product (e.g. Descartes Route Planner, Descartes Route Planner On-demand), and the "MRM BI" database where reporting data is stored. The databases are divided into several schemas for organizational and security purposes.
Dispatching Database
The solution adds an additional schema and associated functions/routines required to extract data from the dispatching system.
BIStaging: This schema contains all elements required to support the MRM BI system. They are stored in a separate schema to avoid conflicting with the dispatching system and can be used to restrict access to the accounts used for any transfers.
DBO, etc.: These schemas are used by the dispatching system and are not modified by MRM BI.
MRM BI Database
The MRM BI database is the warehouse where information is stored for reporting purposes. The staging tables are associated with separate file groups to allow for data storage optimization. It is composed of the following schemas:
Config: Stores all tables and functions related to the Configuration for the MRM BI database.
Staging: Used for staging tables and associated procedures/functions. Can be used to restrict access to the accounts used for staging data for reporting.
Internal: Contains reporting data broken into one or more tables and unified through a DBO schema view.
DBO: Contains the public-facing tables and views used for reporting purposes.
DataSource: Contains the pre-packaged views used to extract information from the reporting database for use in Descartes Reporting Services.
Formatters (Staging, Loading)
The formatters are a series of inline TVF functions (see appendix for details) used to format data prior to transferring it from the dispatching system into the staging tables/database, or from the staging tables into the reporting tables/database. These functions are designed to minimize workload on the dispatching system database and should not be directly modified, as they will be updated or replaced with future upgrades of the BI solution.