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_cargo_type

Columns:

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

Table: dim_carriers

Columns:

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

Table: dim_customers

Columns:

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

Table: dim_sites

Columns:

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

Helpers

N/A - Will update as questions come through.