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	the id of the customer	what you use to merge with customer table
main_carrier_id	bigint	the id of the carrier for the job	what you use to merge with the carrier table
source_created_at	timestamp	if the job came from an other system
deleted_at	timestamp	the date the job was deleted	null if not deleted
shipper_status	varchar	n/a
lost_at	timestamp	the date the job was lost	null if not lost
delete_reasons	text	the reason for deletion
chargeable_weight	decimal	the chargeable weight for the job
weight_unit	varchar	the weight unit used	we support both kg and lb, so you need to convert the lb weights to kg. (looking into making it always converting into kg in the db)
gross_weight	decimal	the gross weight for the job
spotrate	tinyint	if the job is a spot rate or not
type	varchar	the type of job	standard, consol, brand masters, ground handling, import, customs etc.
schedule_auto_update	tinyint	the automation schedule updater ran on this job
automation_created_count	int	how many times the automation ran on the job to sync into a carrier system	used to show if it has ran at least once, and how many times after that
schedule_auto_amount	decimal	how many times the schedule automation ran on job
status	varchar	status of the job	need to provide list of status, but not needed for the reports by WFC
booking_sync_state	varchar	the status of automation, if there's been an error
cargo_type	json	the cargo type of the job	this can be multiple and links back to the cargo types table
house_airwaybill	varchar	the house airway bill on the job if there is one
product	varchar	the product of the job	airport to airport, door to airport, airport to door, door to door, first mile only, final mile only.
origin	varchar	origin airport
destination	varchar	destination airport
master_airwaybill	varchar	the master airway bill on the job if there is one
hazardous_goods	tinytint	if the job is marked as dangerous goods
commodity_description	text	the commodity
cargo_only	tinyint	if job is cargo aircraft only
depart_date	datetime	the depart date of the job
created_at	timestamp	when the job was created
updated_at	timestamp	last time the job was updated

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.