TrackTik's Data Warehouse's tables and fields are directly correlated and populated from its API. This means that you can infer the meaning and relations of most tables and fields based on the <TODO link to API "Extended Documentation">API documentation referenced elsewhere in the support system.</todo>
Example: Employees table
A typical Employees table in your DWH solution will be defined like this:
CREATE TABLE `abc_employees` (
`id` int NOT NULL,
`customId` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
`firstName` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
`lastName` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
`jobTitle` varchar(255) DEFAULT NULL,
`email` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
`status` enum('INACTIVE','ACTIVE','TERMINATED','BANNED') CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
`region` int DEFAULT NULL,
`password` varchar(255) DEFAULT NULL,
`employmentProfile` int DEFAULT NULL,
`gender` enum('M','F','B') DEFAULT NULL,
`age` int DEFAULT NULL,
`birthday` datetime DEFAULT NULL,
`ageGroup` varchar(255) DEFAULT NULL,
`address` int DEFAULT NULL,
`tags` varchar(255) DEFAULT NULL,
`notificationSettings` int DEFAULT NULL,
`language` enum('EN_US','FR','RO','ES','DE','NL','PT','SV','ZH-HANS','TH') DEFAULT NULL,
`name` varchar(255) DEFAULT NULL,
`primaryPhone` varchar(255) DEFAULT NULL,
`secondaryPhone` varchar(255) DEFAULT NULL,
`username` varchar(255) DEFAULT NULL,
`lastLoggedInOn` datetime DEFAULT NULL,
`avatar` text,
`createdBy` int DEFAULT NULL,
`createdOn` datetime DEFAULT NULL,
`updatedBy` int DEFAULT NULL,
`updatedOn` datetime DEFAULT NULL,
`_update_time` int DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `region` (`region`),
KEY `employmentProfile` (`employmentProfile`),
KEY `gender` (`gender`),
KEY `address` (`address`),
KEY `notificationSettings` (`notificationSettings`),
KEY `language` (`language`),
KEY `status` (`status`),
KEY `createdBy` (`createdBy`),
KEY `updatedBy` (`updatedBy`),
KEY `updateTime` (`_update_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
In the above definition, you'll notice there are many integers for single worded field names. This is a typical pattern of "this is a relation to self or another table/API entity":
- region : refers to the abc_regions primary key [id]
- createdBy : self reference to another abc_employees record [id]
In the TrackTik API documentation, endpoint /employees, it is generally the same (the below is a bit different because the API was recently updated to have additional fields, and the API can return 1:many arrays in JSON format that the DWH SQL/table paradigm cannot per row of data in a recordset).
Source: https://partnerships.staffr.net/rest/v1/2020-01-01/core/entities#tag/employees
It to shows integers for region and createdBy too. Regions has its own entity/endpoint:
Regions
https://partnerships.staffr.net/rest/v1/2020-01-01/core/entities#tag/regions/operation/listRegions
This means that when seeking to explore what data entity a field is pointing to, in order to perform a JOIN, you can refer to the API documentation.
Full List of Entities
The full list of Data Replication entities is organized into groups, as listed below:
Asset Management
asset_categories
asset_contacts
asset_custom_attributes
asset_custom_fields
asset_fieldset_fields
asset_fieldsets
asset_files
asset_manufacturers
asset_persons
asset_reservations
asset_suppliers
asset_transaction_items
asset_transactions
asset_type_custom_attributes
asset_type_files
asset_type_to_report_template_assignment
asset_types
assets
BOSS HR+Scheduling
break_rule_compliance_logs
break_rule_conditions
break_rule_exceptions
break_rule_relations
break_rules
break_sessions
calendar_exceptions
shifts
shift_logs
shift_notes
shift_proposal_queue_items
shift_template_settings
shift_templates
entity_label_availability_types
entity_labels
open_shift_requests
overtime_rule_items
overtime_rules
lone_worker_check_in_logs
lone_worker_check_ins
lone_worker_phone_calls
lone_worker_phones
lone_worker_schedule_check_ins
lone_worker_time_keeping_settings
phone_call_logs
schedule_off_periods
sms_logs
work_sessions
BOSS Payroll
payroll_payruns
payroll_schedules
payroll_payrun_items
payroll_adhocs
payroll_deductions
payroll_codes
payroll_schedules
employee_bill_pay_matrix_rate_settings
employee_class_pay_settings
employee_classes
employee_pay_settings
employee_premium_assignments
position_bill_settings
position_billing_settings
position_expense_reimbursements
position_pay_settings
position_payroll_settings
position_premium_assignments
position_premiums
position_price_matrix_rules
BOSS Contracts, Invoicing, Payroll
back_office_pay_settings
back_office_settings
client_billing_settings
client_invoice_split_settings
contracts
contract_service_models
contract_occurrence_logs
contract_recurrent_fixed_services
contract_service_models
billing_addresses
billable_items
bill_items
billing_adhoc_items
invoices
invoice_line_taxes
invoice_lines
payment_methods
payments
task_price_tiers
tax_class_items
tax_classes
tax_location_override_groups
Core (Guarding Suite)
accounts
addresses
app_licenses
audit_logs
clients
client_users
checkpoint_question_answers
checkpoint_scans
checkpoint_tour_assignments
checkpoint_tour_schedules
checkpoint_tour_session_comments
checkpoint_tour_sessions
checkpoint_tours
checkpoints
comments
contacts
departments
emergency_contacts
emergency_contact_account_assignments
employee_account_assignment_pay_settings
employee_account_assignments
employee_account_bans
employee_availabilities
employee_extra_regions
employees
employee_skills
employee_seniorities
employee_external_id_fields
employee_external_ids
employee_extra_regions
employment_profiles
employment_termination_reasons
event_activities
events_subscription_logs
events_subscriptions
filter_rule_set_assets
filter_rule_set_reports
filter_rule_sets
geo_countries
geo_country_subdivisions
geo_fences
geo_locations
holiday_groups
holidays
message_board_post_acknowledgements
message_board_post_recipients
message_board_post_categories
message_board_posts
mobile_device_geo_locations
mobile_devices
model_translations
position_skills
positions
post_order_acknowledgements
post_order_subjects
post_orders
positions
regions
report_flag_categories
reports
report_fields
report_template_fields
report_templates
report_flags
report_flag_group_assignments
report_flag_groups
report_flag_template_fields
report_flag_templates
roles
role_permissions
site_locations
vehicles
skills
skill_categories
system_usage_logs
tenants
units
user_login_logs
user_roles
user_notification_settings
zone_clients
zones
Leave Management
leave_accruals
leave_policies
leave_policy_balances
leave_policy_carry_over_logs
leave_policy_items
leave_request_logs
leave_request_vacated_shifts
leave_requests
leave_types
Mobile Dispatch
alarms
alarm_connections
alarm organizations
alarm_types
dispatch_custom_locations
dispatch_slas
dispatch_sla_timer_settings
dispatch_tasks
dispatch_task_logs
dispatch_tasks_notes
dispatch_slas
task_type_skills
task_site_instructions
task_site_settings
workflow_instance_logs
workflow_instances
workflows
workflow_status_actions
workflow_status_transition_forms
workflow_status_transitions
workflow_statuses
workflow_timers
Mobile Patrol
mobile_runsheet_sessions
mobile_schedule_occurrence_logs
mobile_runsheet_sessions_logs
mobile_schedules
mobile_runsheets
mobile_schedule_occurrence_logs_notes
mobile_schedule_occurrence_reassignments
system_exception_ticket_instructions
system_exception_tickets
task_types
Site Tasks
site_task_occurrence_sessions_logs
site_task_occurrence_sessions
site_task_schedule_exception_types
site_task_schedules