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 | ||
| hos_id | bigint | ||
| site_id | bigint | ||
| booked_at | datetime | ||
| confirmed_at | datetime | ||
| quoted_at | datetime | ||
| pre_quoted_at | datetime | ||
| user_id | bigint | ||
| group_type | varchar | ||
| booking_user_id | bigint | ||
| lost_reason | varchar | ||
| customer_id | bigint | ||
| main_carrier_id | bigint | ||
| source_created_at | timestamp | ||
| deleted_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.