BI (Business Intelligence) Database

Overview

The Transvirtual BI Database is an add-on feature that provides a dedicated, read-only replica of your operational data — purpose-built for reporting and analytics. It gives you direct SQL access to your consignment, pricing, invoicing, and operational data so you can build custom reports, dashboards, and integrations using your preferred BI tools (e.g. Power BI, Tableau, Looker, Excel).

Because the BI Database is separate from your live production environment, running complex queries or large data exports will never impact your day-to-day operations.

Getting Started

To enable the BI Database on your account, please raise a request via support@transvirtual.com. Once activated, you'll receive connection credentials and can begin querying your data immediately.

Costs

1. An initial once off setup cost.

2. Ongoing weekly hosting and maintenance charge.

3. There is no ongoing obligation or contract required. At any time, if this option isn't working for you, let us know and we'll shut it down and stop charging you.

Available Data Tables

Consignment Data

Table Name	Description
ConsignmentBi	Core consignment data — the central table referenced by pricing, agent, and customer tables.
ConsignmentItemBi	Individual items within a consignment.
ConsignmentRowSummaryBi	Summary data for each consignment item row.
ConsignmentCommentBi	All comments associated with a consignment.
ConsignmentPODBi	Proof of Delivery (POD) data.
ConsignmentReportableCostBi	Reportable costs for consignments.
ScanBi	Scan event data for consignments.

Customer Data

Table Name	Description
CustomerBi	Customer records including IDs and names.
ConsignmentCustomerPriceBi	High-level customer pricing data for consignments.
ConsignmentCustomerPriceDetailBi	Line-item breakdown of customer consignment pricing.
CustomerInvoiceBi	Approved and locked customer invoices.
CustomerInvoiceDetailBi	Mapping between customer invoices, consignments, and pricing.

Agent Data

Table Name	Description
AgentBi	Agent records including IDs and names.
ConsignmentAgentPriceBi	High-level agent pricing data for consignments.
ConsignmentAgentPriceDetailBi	Line-item breakdown of agent consignment pricing.
AgentInvoiceBi	Approved and locked agent invoices.
AgentInvoiceDetailBi	Mapping between agent invoices, consignments, and pricing.

Manifest Data

Table Name	Description
ManifestCustomerBi	Customer manifests.
ManifestCustomerConsignmentBi	Mapping between customer manifests and consignments.
ManifestAgentBi	Agent manifests.
ManifestAgentConsignmentBi	Mapping between agent manifests and consignments.
ManifestRunsheetBi	Runsheet manifests.
ManifestRunsheetConsignmentBi	Mapping between runsheet manifests and consignments.
ManifestLinehaulBi	Linehaul manifests.
ManifestLinehaulConsignmentBi	Mapping between linehaul manifests and consignments.

Quotes

Table Name	Description
QuoteBi	Quote records.
QuoteRowSummaryBi	Item-level data for quotes.

Operations & Fleet

Table Name	Description
DriverDailySummaryBi	Daily summary of driver activity — drops, start/end times, locations, etc.
VehicleBi	Vehicle information.
VehicleMaintenanceBi	Vehicle maintenance records.
VehicleMaintenancePartsBi	Parts used in vehicle maintenance.
VehicleAlertsBi	Vehicle alerts.
FormCompleteBi	Forms completed by drivers (e.g. incident reports, inspections).

Equipment

Table Name	Description
EquipmentTypeBi	Equipment type definitions.
EquipmentTransactionBi	Equipment transactions.
EquipmentTransactionConsignmentBi	Mapping between equipment transactions and consignments.

Reference & System

Table Name	Description
SuburbBi	Suburb reference data (used by other tables).
UserBi	User login information.
TicketBi	Customer service tickets.

Data Sync & Freshness

Your BI Database is kept in sync with your live Transvirtual environment through automated sync processes. Different tables use different sync strategies depending on data volume and how frequently the data changes.

Sync Methods

CRC-based incremental sync — Detects changes in the source data and syncs only what has changed. Efficient for smaller, frequently updated tables.
Date-partitioned sync — Syncs data based on date ranges to optimise performance and reduce load. Used for high-volume, time-series data.
Daily full sync — A complete refresh of the table, run once per day.

Sync Schedule by Table

Table	Sync Method	Standard Run	Extended Daily Run
SuburbBi	Daily full sync	N/A	Full data (once daily)
CustomerBi	CRC-based incremental	Full data & CRC checked	N/A
AgentBi	CRC-based incremental	Full data & CRC checked	N/A
UserBi	CRC-based incremental	Full data & CRC checked	N/A
EquipmentTypeBi	CRC-based incremental	Full data & CRC checked	N/A
VehicleBi	CRC-based incremental	Full data	N/A
VehicleMaintenanceBi	CRC-based incremental	Full data	N/A
VehicleMaintenancePartsBi	CRC-based incremental	Full data	N/A
VehicleAlertsBi	CRC-based incremental	Full data	N/A
ConsignmentBi	CRC + Date-partitioned	Last 7 days per run	Last 6 months (once daily)
ConsignmentCustomerPriceBi	CRC + Date-partitioned	Last 7 days per run	Last 6 months (once daily)
ConsignmentAgentPriceBi	CRC + Date-partitioned	Last 7 days per run	Last 90 days (once daily)
ConsignmentPODBi	CRC-based incremental	Last 7 days per run	Last 30 days (once daily)
ScanBi	CRC + Date-partitioned	Last 7 days per run	Last 30 days (once daily)
QuoteBi	CRC + Date-partitioned	Last 7 days per run	Last 6 months (once daily)
CustomerInvoiceBi	CRC + Date-partitioned	Last 14 days per run	Last 90 days (once daily)
AgentInvoiceBi	CRC + Date-partitioned	Last 14 days per run	Last 90 days (once daily)
ConsignmentReportableCostBi	CRC + Date-partitioned	Last 14 days per run	Last 90 days (once daily)
TicketBi	CRC + Date-partitioned	Last 14 days per run	Last 90 days (once daily)
ManifestCustomerBi	CRC + Date-partitioned	Last 3 days per run	Last 7 days (once daily)
ManifestAgentBi	CRC + Date-partitioned	Last 3 days per run	Last 7 days (once daily)
ManifestRunsheetBi	CRC + Date-partitioned	Last 3 days per run	Last 7 days (once daily)
ManifestLinehaulBi	CRC + Date-partitioned	Last 3 days per run	Last 7 days (once daily)
DriverDailySummaryBi	CRC + Date-partitioned	Last 3 days per run	N/A
EquipmentTransactionBi	CRC + Date-partitioned	Last 3 days per run	Last 30 days (once daily)
ConsignmentCommentBi	CRC + Date-partitioned	Last 3 days per run	Last 30 days (once daily)

Standard Run syncs execute on a regular schedule (e.g. every 30 minutes). Extended Daily Run syncs execute once every 24 hours to catch any older data changes.

Sample Reports

The BI Database enables a wide range of reporting. Below are some common reports that can be built using the available data:

Report	Description	Visualisation Examples
Scanning Compliance	Percentage of freight scanned at every point of the consignment journey.	Scan summary table, headport scan % by week (table & line graph)
Forms	Summary of forms completed, filterable by form type (e.g. damaged freight, incident reports).	Forms completed by week and headport
Customer Service Tickets	Summary of tickets with filters by ticket type and headport.	Tickets logged by week and headport
Revenue	Revenue broken down by depot completing the delivery, with weekly trend visualisation.	Revenue by headport by week (table & line graph)
Revenue & Margins	True job margins including customer charge, internal costs, and agent costs.	Consignment margins table, average margin % by week
Driver Delivery Efficiency	Margin and revenue per delivery run by depot.	Runsheet revenue, cost & profit by headport/week (table & line graph)
Agent Delivery Efficiency	Margin and revenue per agent run by depot.	Agent runsheet summary by headport/week (table & line graph)
Revenue Change	Customer revenue trends and growth tracking over time.	Top/bottom accounts by growth, total revenue by headport/week (line graph)

These reports can be customised and extended to suit your specific business needs.