Reporting DB

Raw Tables, Columns and Suggested Modelling

To access the reporting database, you will need an account creating. Please follow the correct process and raise a ticket via the helpdesk. They will provide you account, address and access.

The information below has the assumption that you understand databases, and have knowledge in PBI development and data management.

Currently we have only added the tables / columns for the information we know is required.

Every Table

Within every table there is 2 consistent columns, to save repeating data - these columns will be shared here.

Columns

Column Name	Description	Notes
id	Database id
portal	Which QMS instance the data is from.	The portal is the instance of the QMS system, not the entity, that is the site.

Table: dim_quotations

Columns:

Column Name	Type	Description	Notes
quotation_id	bigint	quotation id within qms
hos_id	bigint	hos id associated with the job
site_id	bigint	the site (entity) that the job is saved for
booked_at	datetime	the date / time the job was booked
confirmed_at	datetime	the date / time the job was confirmed with airline
quoted_at	datetime	the date / time the job was quoted
pre_quoted_at	datetime	the date / time the job was pre_quoted
user_id	bigint	the user who created the job
group_type	varchar	the group type of shipment
booking_user_id	bigint	the id of the user who confirmed the job
lost_reason	varchar	the reason the job was lost
customer_id	bigint
main_carrier_id	bigint
source_created_at	timestamp
deleted_at	timestamp
shipper_status	varchar
lost_at	timestamp
delete_reasons	text
chargeable_weight	decimal
weight_unit	varchar
gross_weight	decimal
spotrate	tinyint
type	varchar
schedule_auto_update	tinyint
automation_created_count	int
schedule_auto_amount	decimal
status	varchar
booking_sync_state	varchar
cargo_type	json
house_airwaybill	varchar
product	varchar
origin	varchar
destination	varchar
master_airwaybill	varchar
hazardous_goods	tinytint
commodity_description	text
cargo_only	tinyint
depart_date	datetime
created_at	timestamp
updated_at	timestamp

Table: dim_cargo_type

Columns:

Column Name	Type	Description	Notes
cargo_type_id	bigint	cargo type id	used when referenced in other tables.
description	varchar	description
tariff_service_code	json	service code that can be linked to the cargo type

Table: dim_carriers

Columns:

Column Name	Type	Description	Notes
carrier_id	bigint	id for the carrier
name	varchar	name of the carrier / supplier
site_id	bigint	the site (entity) the carrier / supplier is on
is_gsa	tinyint	the carrier / supplier is gsa
parent_id	bigint	the parent carrier / supplier for this carrier	this will be null if the carrier is a parent.
carrier_class	tinyint	supplier class	airline, truck, sea freight etc..
carrier_subclass	tinytint	subclass for the carrier
created_at	timestamp	when the carrier was created
updated_at	timestamp	the last time the carrier was updated

Table: dim_customers

Columns:

Column Name	Type	Description	Notes
customer_id	bigint	id for the customer
name	varchar	name of the customer
hos_code	varchar	hos id for the customer if they have one
account_number	varchar	account number for the customer
site_id	bigint	the site (entity) the customer is on
created_at	timestamp	when the customer was created
updated_at	timestamp	the last time the carrier was updated

Table: dim_sites

Columns:

Column Name	Type	Description	Notes
site_id	bigint	id for the site (entity)
name	varchar	name of the site (entity)
created_at	timestamp	when the site (entity) was created
updated_at	timestamp	when the site (entity) was updated last

Coming Soon

quotation_totals

Allow for a breakdown of cost and sell in customer invoice currency, entity currency and all exchange rates to EU at point of quote, book and confirmation for exact conversion numbers into EU.

Helpers

N/A - Will update as questions come through.