This page describes advanced configuration and administration topics for MRM BI, including partitioned views, logging, purging, indexing, and debugging.

Partitioned Views

All entities are exposed for reporting through views in the DBO schema. Entities that are date-driven (Route, Stop, Job, Order) use a special type of view called a partitioned view. The partitioning is done on a field called VC_FilterStartDate. This field allows SQL Server to identify which internal tables need to be referenced for a given request. For more information on partitioned views, refer to the Microsoft TechNet article on partitioned views.

Internal Tables and Schema Build/Checks

Each entity view is tied to one or more tables in the Internal schema. These tables are intended for internal use and should not be directly queried for reporting, as they are rolling. Over time, tables will be deprecated as new ones are created.

This process is managed by the [Config].[BIBuildAllPeriodTables] stored procedure, which is called automatically by BILoadingTaskWrapper unless the @SkipInternalSchemaCheck property is configured. If skipped, this stored procedure must be scheduled to run at least once a month, before the Loading Procedure is called, to avoid failures or incomplete loads.

Custom Reporting Guidelines

The MRM BI system is intended to provide a baseline of useful reports for various industries. However, it is likely that MRM BI will not meet every business requirement. If modifications to the data source views are required, they should be performed in copies of the sources rather than in the originals, to avoid having modifications overwritten by future upgrades. Adding new columns works the same as in other packages. However, when creating new views from scratch or joining data from other views, additional care must be taken to maintain performance and ensure data correctness.

All joins should be done using the Entities Key field. Columns with the "ID" from the originating dispatching system are not indexed and are intended for internal use only. Since there could be multiple sources of data, the PartitionID must be included. All indexes are based on both the PartitionID + the EntityKey. When joining between date-driven entities (Route, Stop, Job, Order), the VC_FilterStartDate column should be included to allow SQL Server to determine which tables are relevant — this is critical to performance.

Logging Tables

MRM BI has two logging tables. The first is its session processing log, which contains information on what data has been staged/loaded and its current state. The second is a debug "information log" where debugging statements are recorded when @DebugLevel > 0 or a failure occurs (Severity > 0 in the log).

Each session/step can have one of three results:

• 1ST — Starting

• 2OK — Step completed successfully

• 3FL — Step failed to complete

To tell the Loading wrapper to ignore a session by convention: SKP

The list of stages:

• 1 — Staging Started

• 2 — Staging in progress

• 3 — Staging Completed

• 4 — Loading Started

• 5 — Loading in progress

• 6 — Loading Completed

The BIStagingWrapper takes the session from stage 1→3 and the BILoadingWrapper takes the session from stage 3→6.

Staging/Import Session Logs

The staging/import session logs are accessible via two views:

[Staging].[ExtractSessionSummary]: Contains a summary of the sessions, with one row per session plus time period.

[Staging].[ExtractSessionDetail]: Provides further details of the sessions and the steps taken in processing each session.

In addition, a combined view is provided at [Staging].[ExtractSessionDetailWithLog], which joins the Staging/Import session log with the InfoLog to aid with investigation.

Information Log

When @DebugLevel is provided as a value greater than 0 in the stored procedures, execution steps are logged in the Information Log. Failures are also written to the Information Log automatically.

The information log can be queried at [Log].[BIInfoLog].

Purging Procedures

MRM BI has built-in cleanup mechanisms for both staging and reporting tables.

Staging Data

The staging data cleanup is performed automatically following a loading task (default) or can be scheduled independently. This process is controlled by the stored procedure [BIStaging].[BICleanupLog].

Reporting Data

The reporting data is automatically cleaned up based on the Config.BISysValue > MinDaysToKeep parameter. As each month passes, the next "internal" table is generated for each BI entity and the oldest table is automatically dropped. This process is triggered when the Loading Procedure is called and is managed by the [Config].[BIBuildPeriodTable] procedure.

Indexing "Internal" Tables

If additional indexes are required to support custom queries, they need to be added to the [Config].[BIPeriodTableDefParts] table. This table is used in the construction and maintenance of the Internal schema tables. Custom entries should be added with a "Seq" of 1000 or higher to avoid conflict with future core changes.

Debugging

All stored procedures support a @DebugLevel parameter. This parameter controls how much information will be displayed to the user and logged to the [Log].[BIInfoLog] table.

Debug Logging Levels

Custom Error Message IDs

Back to top