Schema Entities¶

Entity: ARC_Activity_Class (Dimension)¶

A static dimension table that defines what sort of activity is represented. Activities can relate to some operational process, a resource rest period or a private activity where the resource is unavailable to be scheduled for work. Activity Class is a method of incorporating these different types of activities into the scheduling process.

There are five classes of activities :-

BREAK: which defines a period of time within a time window where the resource must stop servicing call class activities.

CALL: which defines an activity a resource must undertake at a location.

DEPOT: defines the location of a depot to be used for part replenishment.

PRIVATE: defines periods of time when the resource is not available to servi ce activities of any other class.

REPEATABLE: defines an activity that can be undertaken mutliple times.

Note

Activity Class values are setup internally and need not be provided by the EA. This table is populated using data from the Activity Class table in the Scheduling Database

Examples¶

<ARC_Activity_Class>
    <key>1</key>
    <description>An activity.</description>
    <message_id>1533</message_id>
    <id>CALL</id>
    <organisation_id>1</organisation_id>
</ARC_Activity_Class>

Attribute Name	Data Type	Description	PK	M/O
key	Integer	A unique integer key for the activity class (auto-increment).	PK	M
organisation_id	Integer	The organisation the row belongs to.		M
description	String(100)	Describes activity class purpose.		O
id	String(32)	A unique id for the activity class. Allowed Values: BREAK, CALL, DEPOT, PRIVATE, REPEATABLE Mapping: Activity_Class		O
message_id	Integer	The id of the message describing the activity class purpose.		O

Entity: ARC_Activity_Duration_Estimate (System)¶

A table to store duration estimates for an activity.

Each time a Scheduling snapshot is taken for reporting, an analysis of the reporting data is performed by the Archiving Service. During this analysis the Archiving Service will attempt to group activities by a combination of activity type, contract, customer or location and calculate an activity duration estimate and confidence level.

Note

For more information and examples of activity duration estimates see the section 'Predictive Durations' and 'Activity Duration Estimate Example' in this document.

Note

The duration estimate is only calculated if the number of durations for a particular group of activities is sufficient. This is configurable by the Archiving Service parameter MinimumNumberOfSamplesForActivityDurationEstimate.

Attribute Name	Data Type	Description	PK	M/O
id	String(32)	An identifier for a grouping of activities (a combination of activity type, customer or contract).	PK	M
organisation_id	Integer	The organisation the row belongs to.	PK	M
activity_type_id	String(32)	The activity type id for this estimate, derives from 'activity_type_id' on Activity or Activity_Type in the Scheduling Database.		O
confidence_level	Double	The confidence level for this estimate. Allowed Values: 0-100		O
contract_id	String(32)	The contract id for this estimate, derives from 'contract_id' on Activity or Activity_Type in the Scheduling Database.		O
customer_id	String(32)	The customer id for this estimate, derives from 'customer' on Activity or Activity_Type in the Scheduling Database.		O
dataset_id	String(32)	The dataset id this grouping of activities belongs to.		O
duration_estimate	Time Span	The estimated duration for this grouping of activities.		O
location_id	String(32)	The location id for this estimate, derives from 'location_id' on Activity in the Scheduling Database.		O
spatial_key	Integer	The spatial key of the location for this estimate, derives from 'latitude' and 'longitude' on Location in the Scheduling Database.		O

Entity: ARC_Activity_Fact (Fact)¶

The Activity Fact is an accumulating snapshot fact table that represents a scheduling activity of activity class 'CALL'. Each Activity Fact row corresponds to a scheduling activity row via its reference to the Activity Name dimension. Its purpose is to present the current 'state' of the activity (accumulated over all the snapshots in the past since its creation up to the current snapshot), plus its history over time, all in one row.

The Activity Fact contains a number of columns that provide useful information about the activity and its state at the current snapshot. For example, it contains a set of date columns such as date and time scheduled, committed, on-site, SLA deadline etc. Additionally, it contains information like SLA hours, missed SLA hours, plus actual and expected activity duration and travel duration.

The Activity Fact table is essentially a denormalised data table about activities that can be easily filtered and grouped on a number of dimensions. Namely, it could be grouped by activity-specific dimension rows to describe the scheduling activity, such as the Activity Name, Activity Type, Resource, Customer etc. Activity Fact data can also be filtered by Date dimensions to indicate the date created, scheduled, completed etc, or the Activity State dimension to group by 'state' (activity status, fixed to time or location, same or different location etc.). Also, it could be grouped by more configurable dimensions such as attributes (up to 4).

Warning

If an activity id is re-used in the system, the activity fact record corresponding to it in the database will be updated with the fields and stats of the new activity record. It is advised that activity ids are not used more than once in the system.

Note

Many of the date times and stats on the Allocation Snapshot Fact are sourced from the Activity, Allocation and Allocation_Data tables in the Scheduling Database.

Note

The datetime columns also have a corresponding utc column, e.g. actual_start_datetime and actual_start_datetime_utc. This is to make reporting on activities that take place in different time zones easier.

Attribute Name	Data Type	Description	PK	M/O
arc_activity_name_key	Int64	Used to filter by activity name, essentially by activity id. There should only be one activity name key per activity id in the system. Foreign Key: ARC_Activity_Name	PK	M
arc_dataset_key	Integer	Used to filter by dataset, i.e. by dataset id via the ARC_Dataset dimension table. Foreign Key: ARC_Dataset	PK	M
arc_activity_state_key	Integer	Used to filter by Activity State, e.g. to get all activity fact rows that have a status of 30, not fixed to a resource or time but at the same location (as the previous activity). Foreign Key: ARC_Activity_State		M
organisation_id	Integer	The organisation the Activity Fact belongs to. Foreign Key: ARC_Date		M
actual_duration_minutes	Double	The actual duration of the activity in minutes. This is only updated if the activity is completed (status of complete, i.e. 70). This is mapped - if it exists - from the expected_duration on the Allocation_Data table in the Scheduling Database. The expected_duration in this case is the actual duration of the activity.		O
actual_start_datetime	Date Time	The actual start date and time of the activity. This is only update if the activity is 'on site' (status of on site, i.e. 60 or above). This is mapped - if it exists - from the activity_start on the Allocation table in the Scheduling Database.		O
actual_start_datetime_utc	Date Time	Same as actual_start_datetime, stored in utc.		O
actual_travel_minutes	Double	The actual travel duration of the activity in minutes. This is only updated if the activity is on site (status of on site, i.e. 60 or above). This is mapped - if it exists - from subtracting the travel_start on the Allocation table from the activity_start on the Allocation table for this activity in the Scheduling Database.		O
arc_activity_type_key	Int64	Used to filter by Activity Type, e.g. to get all activity fact rows that have an activity type of 'Planned Maintenance'. Foreign Key: ARC_Activity_Type		O
arc_attribute_value_key1	Int64	Used to filter by Attribute Value, e.g. to get all activity fact rows that have an attribute of 'incoice code'. Which attributes from the Scheduling Database (Additional_Attribute) should be reported on for activities is set up on the 'Attributes' screen. See Attributes section for more detail. Foreign Key: ARC_Attribute_Value		O
arc_attribute_value_key2	Int64	Same as arc_attribute_value_key1, providing another field for an activity attribute to be reported on, so that activities can be grouped by a certain attribute. Foreign Key: ARC_Attribute_Value		O
arc_attribute_value_key3	Int64	Same as arc_attribute_value_key1, providing another field for an activity attribute to be reported on, so that activities can be grouped by a certain attribute. Foreign Key: ARC_Attribute_Value		O
arc_attribute_value_key4	Int64	Same as arc_attribute_value_key1, providing another field for an activity attribute to be reported on, so that activities can be grouped by a certain attribute. Foreign Key: ARC_Attribute_Value		O
arc_calendar_key	Int64	Used to filter by Calendar, e.g. to get all activity fact rows that have a '9-17' calendar. Calendars are timetables that can be set up to indicate when an activity can 'consume' its SLA. For more information on calendars please refer to the Scheduling Schema Guide (Calendar, Calendar_Item, Special_Day, Special_Day_Item tables). Foreign Key: ARC_Calendar		O
arc_contract_key	Int64	Used to filter by Contract, e.g. to get all activity fact rows that have a certain contract linked to it. For more information on contracts please refer to the Scheduling Schema Guide (Contract table). Foreign Key: ARC_Contract		O
arc_customer_key	Int64	Used to filter by Customer, e.g. to get all activity fact rows that have customer 'IFS' linked to it. For more information on customers please refer to the Scheduling Schema Guide (Customer table). Foreign Key: ARC_Customer		O
arc_date_key_completed	Integer	Used to filter by the date an activity is completed, e.g. to get all activity fact rows that represent activities that were completed yesterday. Foreign Key: ARC_Date		O
arc_date_key_created	Integer	Used to filter by the date an activity is created, e.g. to get all activity fact rows that represent activities that are created today. Foreign Key: ARC_Date		O
arc_date_key_schedule_time	Integer	Used to filter by the date an activity is scheduled, e.g. to get all activity fact rows that represent activities that are scheduled today. Foreign Key: ARC_Date		O
arc_location_key_end	Int64	Used to filter by the end location of an activity, e.g. to get all activity fact rows that end at a certain location. Foreign Key: ARC_Location		O
arc_location_key_start	Int64	Used to filter by the start location of an activity, e.g. to get all activity fact rows that start at a certain location Foreign Key: ARC_Location		O
arc_region_group_key	Int64	Used to group activity fact rows by one or more regions (a region group contains one or more regions). Foreign Key: ARC_Region_Group		O
arc_resource_key	Int64	Used to filter by the resource the activity is allocated to (if it is allocated), e.g. to get all activity fact rows that represent activities that have been allocated to resource 'James'. Foreign Key: ARC_Resource		O
arc_resource_type_key	Int64	Used to filter by the resource type the activity is allocated to (if it is allocated), e.g. to get all activity fact rows that represent activities that have been allocated to resources of type 'Driver'. Foreign Key: ARC_Resource_Type		O
arc_skill_group_key	Int64	Used to group activity fact rows by one or more skills (a skill group contains one or more skills). Foreign Key: ARC_Skill_Group		O
arc_time_zone_key	Integer	Used to group activity fact rows by time zone, e.g. get all activity fact rows that represent activities that are appear in time zone 'Pacific'. Foreign Key: ARC_Time_Zone		O
attribute_value1	Double	The value of the additional attribute for this activity (label_value), which corresponds to attribute_value_key1.		O
attribute_value2	Double	The value of the additional attribute for this activity (label_value), which corresponds to attribute_value_key2.		O
attribute_value3	Double	The value of the additional attribute for this activity (label_value), which corresponds to attribute_value_key3.		O
attribute_value4	Double	The value of the additional attribute for this activity (label_value), which corresponds to attribute_value_key4.		O
calendar_missed_sla_hours	Double	The duration of SLA time spent before the activity is scheduled - if the activity is allocated at all - in hours. This duration is restricted by the calendar it is linked to, namely the timetable, within which the activity 'spends' its SLA, i.e. the SLA applies. For more information on calendars please refer to the Scheduling Schema Guide (Calendar, Calendar_Item, Special_Day, Special_Day_Item tables).		O
calendar_sla_hours	Double	The duration of SLA time in hours. This duration is restricted by the calendar it is linked to, namely the timetable, within which the activity 'spends' its SLA, i.e. the SLA applies. For more information on calendars please refer to the Scheduling Schema Guide (Calendar, Calendar_Item, Special_Day, Special_Day_Item tables).		O
committed_datetime	Date Time	The committed date and time of the activity. This is only updated if the activity is committed (status of 'committed', i.e. 30 or above). This is mapped - if it exists - from the commit_datetime on the Allocation Data table in the Scheduling Database.		O
committed_datetime_utc	Date Time	Same as committed_datetime, stored in utc.		O
completed_datetime	Date Time	The completed date and time of the activity. This is only updated if the activity is completed (status of 'complete', i.e. 70). This is mapped - if it exists - from the activity_end on the Allocation table in the Scheduling Database.		O
completed_datetime_utc	Date Time	Same as completed_datetime, stored in utc.		O
created_datetime	Date Time	The date and time the activity was created. This is mapped - if it exists - from the created_datetime on the Activity table in the Scheduling Database.		O
created_datetime_utc	Date Time	Same as created_datetime, stored in utc.		O
expected_duration_minutes	Double	The expected (input) duration of the activity in minutes. The Activity auto_duration if specified and enabled. If not this is the Activity Status duration of the most recent Activity Status, if specified. If an Activity Status duration is not specified, then the value is taken from the Activity duration. If the Activity duration is not specified, then this is taken from the Activity Type duration.		O
expected_start_datetime	Date Time	The expected date and time start of the activity. This is mapped from the Allocation Data expected_start if it exists, otherwise the Allocation activity_start is used.		O
expected_start_datetime_utc	Date Time	Same as expected_start_datetime, stored in utc.		O
expected_travel_minutes	Double	The expected travel duration in minutes. This is mapped from any Plan Travel rows that are indicate travelling to the activity location.		O
input_duration_minutes	Double	The input duration of the activity in minutes. This is similar to expected_duration_minutes except auto_duration is ignored.		O
last_updated	Date Time	The date and time when the row was last updated.		O
missed_sla_hours	Double	The duration of SLA time 'spent' before the activity is scheduled - if the activity is allocated at all - in hours.		O
number_of_visits	Double	Number of visits that this activity has, e.g. if it is a split activity over a break, the visits will be two.		O
sla_deadline	Date Time	The date and time the activity's primary SLA ends. This is mapped - if it exists - from the sla_jeopardy_time on the Allocation Data table in the Scheduling Database. In the case where multiple visits exist for this activity (split activity), the primary SLA of the first visit is taken into consideration only.		O
sla_deadline_utc	Date Time	Same as sla_deadline, stored in utc.		O
sla_hours	Double	The duration of SLA time in hours.		O

Entity: ARC_Activity_Name (Dimension)¶

A dimension that derives from the id of an Activity in the Scheduling Database (Activity table).

Attribute Name	Data Type	Description	PK	M/O
key	Int64	A unique integer key for the activity name (auto-increment).	PK	M
id	String(32)	A unique id for a scheduling activity, derives from the id of the scheduling Activity in the Scheduling Database (Activity.id). Mapping: Activity.id		M
organisation_id	Integer	The organisation the row belongs to.		M
arc_dataset_key	Integer	The dataset the row belongs to. Foreign Key: ARC_Dataset		O
external_ref	String(40)	A string used by the EA to identify this activity.		O
last_updated	Date Time	The date and time when the row was last updated.		O
last_used	Date Time	The date and time when the row was last referenced on a fact table.		O

Entity: ARC_Activity_State (Dimension)¶

A static dimension that describes the 'state' of the activity. To get an idea of what 'state' the activity is in, a few attributes are taken into consideration:

status of the activity
activity is scheduled
activity is completed
SLA hit, i.e. whether the activity is scheduled within its primary SLA or not.

An example of an activity state is 'Allocated, SLA hit'

Examples¶

<ARC_Activity_State>
    <key>1</key>
    <label>Unallocated, SLA hit</label>
    <status_id>0</status_id>
    <status_message_id>829</status_message_id>
    <status_description>Unallocated</status_description>
    <completed>false</completed>
    <is_scheduled>false</is_scheduled>
    <hit_sla>false</hit_sla>
    <completed_label>Not Complete</completed_label>
    <is_scheduled_label>Unscheduled</is_scheduled_label>
    <hit_sla_label>Missed SLA</hit_sla_label>
    <organisation_id>1</organisation_id>
</ARC_Activity_State>

<ARC_Activity_State>
    <key>2</key>
    <label>Allocated, SLA hit</label>
    <status_id>10</status_id>
    <status_message_id>830</status_message_id>
    <status_description>Allocated</status_description>
    <completed>false</completed>
    <is_scheduled>true</is_scheduled>
    <hit_sla>false</hit_sla>
    <completed_label>Not Complete</completed_label>
    <is_scheduled_label>Scheduled</is_scheduled_label>
    <hit_sla_label>Missed SLA</hit_sla_label>
    <organisation_id>1</organisation_id>
</ARC_Activity_State>

Attribute Name	Data Type	Description	PK	M/O
key	Integer	A unique integer key for the activity state (auto-increment).	PK	M
completed	Boolean	Determines whether the activity is completed or not. For the activity to be completed, a status of 'Complete' needs to be linked to it (status 70)		M
completed_label	String(500)	The label used for the completed status.		M
hit_sla	Boolean	Determines whether the activity is scheduled within its primary SLA.		M
hit_sla_label	String(500)	The label used for to indicate if the SLA is hit or not.		M
is_scheduled	Boolean	Determines whether the activity is scheduled or not.		M
is_scheduled_label	String(500)	The label used for to indicate if the activity is scheduled.		M
label	String(256)	The label used for this activity state row.		M
organisation_id	Integer	The organisation the row belongs to.		M
status_description	String(100)	A description of the activity status this activity state has.		M
status_id	Integer	The activity status of this activity state. This maps to the Activity_Status.id in the Scheduling Database. Mapping: Activity_Status.id		M
status_message_id	Integer	The id of the message describing the activity status.		M

Entity: ARC_Activity_Type (Dimension)¶

A dimension that derives from the id of an Activity Type in the Scheduling Database (Activity_Type table).

Attribute Name	Data Type	Description	PK	M/O
key	Int64	A unique integer key for the activity type (auto-increment).	PK	M
id	String(32)	A unique id for a scheduling activity type, derives from the id of the scheduling Activity Type in the Scheduling Database. Mapping: Activity_Type.id		M
organisation_id	Integer	The organisation the row belongs to.		M
arc_dataset_key	Integer	The dataset the row belongs to. Foreign Key: ARC_Dataset		O
description	String(2000)	A description for a scheduling activity type, derives from the description of the scheduling Activity Type in the Scheduling Database (Activity_Type.description).		O
label	String(256)	A label created based on the parameter 'ArchiveStandardLabelFormat'. Defaults to {description}.		O
last_updated	Date Time	The date and time when the row was last updated.		O
last_used	Date Time	The date and time when the row was last referenced on a fact table.		O

Entity: ARC_Allocation_Detail (Dimension)¶

A static dimension that describes the 'allocation detail' of the activity. To get an idea of what 'allocation detail' the activity has, a few attributes are taken into consideration:

activity is fixed to resource or time
activity is scheduled at the same location
the source of the allocation

An example of an allocation detail is 'Fixed to resource, Different location, Automatic allocation, Not fixed to time'

Examples¶

<ARC_Allocation_Detail>
    <key>1</key>
    <fixed_resource>false</fixed_resource>
    <fixed_time>false</fixed_time>
    <same_location>false</same_location>
    <fixed_resource_label>Not fixed to resource</fixed_resource_label>
    <same_location_label>Different location</same_location_label>
    <source_label>Automatic allocation</source_label>
    <source_message_id>1524</source_message_id>
    <fixed_time_label>Not fixed to time</fixed_time_label>
    <organisation_id>1</organisation_id>
</ARC_Allocation_Detail>

<ARC_Allocation_Detail>
    <key>2</key>
    <fixed_resource>true</fixed_resource>
    <fixed_time>false</fixed_time>
    <same_location>false</same_location>
    <fixed_resource_label>Fixed to resource</fixed_resource_label>
    <same_location_label>Different location</same_location_label>
    <source_label>Automatic allocation</source_label>
    <source_message_id>1524</source_message_id>
    <fixed_time_label>Not fixed to time</fixed_time_label>
    <organisation_id>1</organisation_id>
</ARC_Allocation_Detail>

Attribute Name	Data Type	Description	PK	M/O
key	Integer	A unique integer key for the allocation detail.	PK	M
organisation_id	Integer	The organisation the row belongs to.		M
fixed_resource	Boolean	Whether the activity is fixed to a resource or not.		O
fixed_resource_label	String(500)	Label for fixed_resource boolean, values are 'Fixed to resource' or 'Not fixed to resource'.		O
fixed_time	Boolean	Whether the activity is fixed to a time or not.		O
fixed_time_label	String(500)	Label for fixed_time boolean, values are 'Fixed to time' or 'Not fixed to time'.		O
same_location	Boolean	Whether the activity is allocated at the same location as the previous allocation.		O
same_location_label	String(500)	Label for same_location boolean, values are 'Same Location' or 'Different Location'.		O
source_label	String(500)	Label for allocation source, derives from message text for allowed allocation source values.		O
source_message_id	Integer	Message id for allocation source.		O

Entity: ARC_Allocation_Fact (Fact)¶

The Allocation Fact is a fact table that represents an allocation of a scheduling activity. An allocation fact row is generated for each allocation in the Scheduling Database.

The Allocation Fact contains a number of columns that provide useful information about the activity and its state. For example, it contains a set of date columns such as date and time scheduled, committed, on-site, SLA deadline etc. Additionally, it contains information like SLA hours, missed SLA hours, total activity visits, plus actual and expected activity duration and travel duration.

The Allocation Fact table is a denormalised data table about activity allocations that can be easily filtered and grouped on a number of dimensions. Namely, it could be grouped by activity-specific dimension rows to describe the scheduling activity allocation, such as the Activity Name, Activity Class, Activity Type, Dataset, Resource, Customer, Calendar, Location, Skill, Region etc.

Allocation Fact data can optionally be filtered by Date dimensions to indicate the date created, scheduled, completed etc., the Activity State dimension to group by 'state' (activity status, fixed to time or location, same or different location etc.), or the Allocation Detail dimension to group by 'type of allocation' (fixed to time or location, same or different location etc.). Additionally, it could be grouped by more configurable dimensions such as attributes (up to 4).

Note

Many of the date times and stats on the Allocation Fact are sourced from the Activity, Allocation and Allocation_Data tables in the Scheduling Database.

Note

The datetime columns also have a corresponding utc column, e.g. shift_start_time and shift_start_time_utc. This is to make reporting on activities that take place in different time zones easier.

Attribute Name	Data Type	Description	PK	M/O
arc_activity_name_key	Int64	Used to filter by activity name, essentially by activity id. There should only be one activity name key per activity id in the system. Foreign Key: ARC_Activity_Name	PK	M
arc_dataset_key	Integer	Used to filter by dataset, i.e. by dataset id via the ARC_Dataset dimension table. Foreign Key: ARC_Dataset	PK	M
visit_id	Integer	The visit id of the scheduling activity allocation. The activity may have multiple visits due to splits (if it is splittable). If not, the visit_id should be 1.	PK	M
arc_activity_class_key	Integer	An integer key, referencing a key on the ARC_Activity_Class table. Used to filter by Activity Class, i.e. by allocation snapshot class id via the ARC_Activity_Class dimension table. Foreign Key: ARC_Activity_Class Allowed Values: BREAK, CALL, DEPOT, PRIVATE, REPEATABLE		M
arc_activity_state_key	Integer	Used to filter by Activity State, e.g. to get all allocation snapshot fact rows that have a status of 30, not fixed to a resource or time but at the same location (as the previous activity). Foreign Key: ARC_Activity_State		M
arc_allocation_detail_key	Integer	Used to filter by Allocation Getail, e.g. to get all allocation snapshot fact rows that are fixed to a resource and time and are scheduled at the same location (as the previous activity). Foreign Key: ARC_Allocation_Detail		M
organisation_id	Integer	The organisation the Allocation Snapshot Fact belongs to. Foreign Key: ARC_Date		M
total_activity_visits	Integer	Number of visits linked to the activity the allocation refers to. This can be used to figure out which visits - using visit_id column - out of the total visits this allocation snapshot fact row refers to.		M
allocation_source	Integer	The source of the allocation. This can be used to filter by source of allocations, e.g. to get all allocation snapshot fact rows that represent allocations of schedulable activities (scheduled by the DSE - AutomaticAllocation). Notes: For committed activities this maps to the source attribute on the latest Activity_Status. For schedulable activities this will be 0 to indicate an automatic allocation. Allowed Values: AutomaticAllocation=0, ManualAllocationSWB=1, SDSAllocation=2, ExternalAllocation=99		O
arc_activity_type_key	Int64	An integer key, referencing a key on the ARC_Activity_Type table. Used to filter by Activity Type, e.g. to get all allocation snapshot fact rows that have an activity type of 'Planned Maintenance'. Foreign Key: ARC_Activity_Type		O
arc_attention_key	Int64	Used to filter by activities that have been highlighted, i.e. the status of the activity in the Scheduling Database has the attribute 'highlight' set to true. For example, a user may want to get all allocation snapshot fact rows that have require attention, i.e. are marked as 'highlighted'. Foreign Key: ARC_Attention		O
arc_attribute_value_key1	Int64	Used to filter by Attribute Value, e.g. to get all allocation snapshot fact rows that have an attribute of 'incoice code'. Which attributes from the Scheduling Database (Additional_Attribute) should be reported on for activities is set up on the 'Attributes' screen. See Attributes section for more detail. Foreign Key: ARC_Attribute_Value		O
arc_attribute_value_key2	Int64	Same as arc_attribute_value_key1, providing another field for an activity attribute to be reported on, so that allocation snapshot fact rows can be grouped by a certain attribute. Foreign Key: ARC_Attribute_Value		O
arc_attribute_value_key3	Int64	Same as arc_attribute_value_key1, providing another field for an activity attribute to be reported on, so that allocation snapshot fact rows can be grouped by a certain attribute. Foreign Key: ARC_Attribute_Value		O
arc_attribute_value_key4	Int64	Same as arc_attribute_value_key1, providing another field for an activity attribute to be reported on, so that allocation snapshot fact rows can be grouped by a certain attribute. Foreign Key: ARC_Attribute_Value		O
arc_constraint_violation_key	Integer	Used to filter by allocations that have violated constraints, i.e. to get all allocation snapshot fact rows that have been fixed to an invalid resource. Foreign Key: ARC_Constraint_Violation Notes: This includes all exceptions that are in 'Externally Violated Constraints' or 'Catch All'. Please see 'Basic Scheduling Concepts Guide' for more detail. Allowed Values: 'Old activity ignored', 'Invalid activity location', 'Invalid or missing activity duration', 'Invalid or missing activity status', 'Activity fixed or committed to unknown resource', 'Invalid fixed time', Invalid fixed resource', 'Resource fixed at invalid time', 'Activity fixed or committed to resource without parts', 'Activity fixed or committed to resource exceeding max travel', 'Invalid travel time weighting will be ignored', 'Invalid activity separation'		O
arc_contract_key	Int64	Used to filter by Contract, e.g. to get all allocation snapshot fact rows that have a certain contract linked to it. For more information on contracts please refer to the Scheduling Schema Guide (Contract table). Foreign Key: ARC_Contract		O
arc_customer_key	Int64	Used to filter by Customer, e.g. to get all allocation snapshot fact rows that have customer 'IFS' linked to it. For more information on customers please refer to the Scheduling Schema Guide (Customer table). Foreign Key: ARC_Customer		O
arc_date_key_allocated_time	Integer	Used to filter by the date an activity is allocated, e.g. to get all allocation snapshot fact rows that represent activities that are scheduled today. Foreign Key: ARC_Date		O
arc_date_key_schedule_time	Integer	Used to filter by the date of the schedule the allocation is in, e.g. to get all allocation snapshot fact rows that are in the schedule yesterday or today. Foreign Key: ARC_Date		O
arc_date_key_sla_deadline	Integer	Used to filter by the date an activity's primary SLA ends, e.g. to get all allocation snapshot fact rows that represent activities that are scheduled today. Foreign Key: ARC_Date		O
arc_location_key_end	Int64	Used to filter by the end location of an activity, e.g. to get all allocation snapshot fact rows that end at a certain location. Foreign Key: ARC_Location		O
arc_location_key_start	Int64	Used to filter by the start location of an activity, e.g. to get all allocation snapshot fact rows that start at a certain location Foreign Key: ARC_Location		O
arc_region_group_key	Int64	Used to group allocation snapshot fact rows by one or more regions (a region group contains one or more regions). Foreign Key: ARC_Region_Group		O
arc_resource_key	Int64	Used to filter by the resource the allocation snapshot is allocated to (if it is allocated), e.g. to get all allocation snapshot fact rows that represent activities that have been allocated to resource 'James'. Foreign Key: ARC_Resource		O
arc_resource_type_key	Int64	Used to filter by the resource type the allocation snapshot is allocated to (if it is allocated), e.g. to get all allocation snapshot fact rows that represent activities that have been allocated to resources of type 'Driver'. Foreign Key: ARC_Resource_Type		O
arc_skill_group_key	Int64	Used to group allocation snapshot fact rows by one or more skills (a skill group contains one or more skills). Foreign Key: ARC_Skill_Group		O
arc_time_zone_key	Integer	Used to group allocation snapshot fact rows by time zone, e.g. get all allocation snapshot fact rows that represent activities that are appear in time zone 'Pacific'. Foreign Key: ARC_Time_Zone		O
attribute_value1	Double	The value of the additional attribute for the activity this allocation relates to (label_value), which corresponds to attribute_value_key1.		O
attribute_value2	Double	The value of the additional attribute for this allocation relates to (label_value), which corresponds to attribute_value_key2.		O
attribute_value3	Double	The value of the additional attribute for this allocation relates to (label_value), which corresponds to attribute_value_key3.		O
attribute_value4	Double	The value of the additional attribute for this allocation relates to (label_value), which corresponds to attribute_value_key4.		O
duration_minutes	Double	The duration of this allocation. This is taken from the Allocation duration. The Allocation duration is derived from the difference between the Activity Status time stamps detailing when the activity was started and when it was completed.		O
last_updated	Date Time	The date and time when the row was last updated.		O
shift_start_time	Date Time	The start time of the shift the allocation is in.		O
shift_start_time_utc	Date Time	Same as shift_start_time, stored in utc.		O
sla_deadline	Date Time	The date and time the activity's primary SLA ends. This is mapped - if it exists - from the sla_jeopardy_time on the Allocation Data table in the Scheduling Database. In the case where multiple visits exist for this activity (split activity), the primary SLA of the first visit is taken into consideration only.		O
sla_deadline_utc	Date Time	Same as sla_deadline, stored in utc.		O
start_time	Date Time	The start time of this allocation. This is mapped - if it exists - from the start time on the Allocation Data table in the Scheduling Database.		O
start_time_utc	Date Time	Same as start_time, stored in utc.		O
travel_hours	Double	The travel duration to this activity allocation. This is obtained - if it exists - by subtracting the Allocation Data trave_start from the Allocation activity start.		O
travel_start_time	Date Time	The travel start time to this activity allocation. This is mapped - if it exists - from the Allocation Data travel_start_time.		O
travel_start_time_utc	Date Time	Same as travel_start_time, stored in utc.		O

Entity: ARC_Allocation_Snapshot_Fact (Fact)¶

The Allocation Snapshot Fact is a periodic snapshot fact table that represents an allocation of a scheduling activity. Every time a snapshot occurs, an allocation snapshot fact row is appended for each allocation in the Allocation Fact table. Its purpose is to take a snapshot of all the activity allocations so that reports can be generated on the activities scheduled over time (via the snapshots).

For more information see the Allocation Fact entity.

Attribute Name	Data Type	Description	PK	M/O
arc_activity_name_key	Int64	Used to filter by activity name, essentially by activity id. There should only be one activity name key per activity id in the system. Foreign Key: ARC_Activity_Name	PK	M
arc_snapshot_detail_key	Int64	Used to group allocation snapshot fact rows by snapshot or to filter by a particular snapshot. Foreign Key: ARC_Snapshot_Detail	PK	M
visit_id	Integer	The visit id of the scheduling activity allocation. The activity may have multiple visits due to splits (if it is splittable). If not, the visit_id should be 1.	PK	M
arc_activity_class_key	Integer	An integer key, referencing a key on the ARC_Activity_Class table. Used to filter by Activity Class, i.e. by allocation snapshot class id via the ARC_Activity_Class dimension table. Foreign Key: ARC_Activity_Class Allowed Values: BREAK, CALL, DEPOT, PRIVATE, REPEATABLE		M
arc_activity_state_key	Integer	Used to filter by Activity State, e.g. to get all allocation snapshot fact rows that have a status of 30, not fixed to a resource or time but at the same location (as the previous activity). Foreign Key: ARC_Activity_State		M
arc_allocation_detail_key	Integer	Used to filter by Allocation Getail, e.g. to get all allocation snapshot fact rows that are fixed to a resource and time and are scheduled at the same location (as the previous activity). Foreign Key: ARC_Allocation_Detail		M
arc_dataset_key	Integer	Used to filter by dataset, i.e. by dataset id via the ARC_Dataset dimension table. Foreign Key: ARC_Dataset		M
organisation_id	Integer	The organisation the Allocation Snapshot Fact belongs to. Foreign Key: ARC_Date		M
total_activity_visits	Integer	Number of visits linked to the activity the allocation refers to. This can be used to figure out which visits - using visit_id column - out of the total visits this allocation snapshot fact row refers to.		M
allocation_source	Integer	The source of the allocation. This can be used to filter by source of allocations, e.g. to get all allocation snapshot fact rows that represent allocations of schedulable activities (scheduled by the DSE - AutomaticAllocation). Notes: For committed activities this maps to the source attribute on the latest Activity_Status. For schedulable activities this will be 0 to indicate an automatic allocation. Allowed Values: AutomaticAllocation=0, ManualAllocationSWB=1, SDSAllocation=2, ExternalAllocation=99		O
arc_activity_type_key	Int64	An integer key, referencing a key on the ARC_Activity_Type table. Used to filter by Activity Type, e.g. to get all allocation snapshot fact rows that have an activity type of 'Planned Maintenance'. Foreign Key: ARC_Activity_Type		O
arc_attention_key	Int64	Used to filter by activities that have been highlighted, i.e. the status of the activity in the Scheduling Database has the attribute 'highlight' set to true. For example, a user may want to get all allocation snapshot fact rows that have require attention, i.e. are marked as 'highlighted'. Foreign Key: ARC_Attention		O
arc_attribute_value_key1	Int64	Used to filter by Attribute Value, e.g. to get all allocation snapshot fact rows that have an attribute of 'incoice code'. Which attributes from the Scheduling Database (Additional_Attribute) should be reported on for activities is set up on the 'Attributes' screen. See Attributes section for more detail. Foreign Key: ARC_Attribute_Value		O
arc_attribute_value_key2	Int64	Same as arc_attribute_value_key1, providing another field for an activity attribute to be reported on, so that allocation snapshot fact rows can be grouped by a certain attribute. Foreign Key: ARC_Attribute_Value		O
arc_attribute_value_key3	Int64	Same as arc_attribute_value_key1, providing another field for an activity attribute to be reported on, so that allocation snapshot fact rows can be grouped by a certain attribute. Foreign Key: ARC_Attribute_Value		O
arc_attribute_value_key4	Int64	Same as arc_attribute_value_key1, providing another field for an activity attribute to be reported on, so that allocation snapshot fact rows can be grouped by a certain attribute. Foreign Key: ARC_Attribute_Value		O
arc_constraint_violation_key	Integer	Used to filter by allocations that have violated constraints, i.e. to get all allocation snapshot fact rows that have been fixed to an invalid resource. Foreign Key: ARC_Constraint_Violation Notes: This includes all exceptions that are in 'Externally Violated Constraints' or 'Catch All'. Please see 'Basic Scheduling Concepts Guide' for more detail. Allowed Values: 'Old activity ignored', 'Invalid activity location', 'Invalid or missing activity duration', 'Invalid or missing activity status', 'Activity fixed or committed to unknown resource', 'Invalid fixed time', Invalid fixed resource', 'Resource fixed at invalid time', 'Activity fixed or committed to resource without parts', 'Activity fixed or committed to resource exceeding max travel', 'Invalid travel time weighting will be ignored', 'Invalid activity separation'		O
arc_contract_key	Int64	Used to filter by Contract, e.g. to get all allocation snapshot fact rows that have a certain contract linked to it. For more information on contracts please refer to the Scheduling Schema Guide (Contract table). Foreign Key: ARC_Contract		O
arc_customer_key	Int64	Used to filter by Customer, e.g. to get all allocation snapshot fact rows that have customer 'IFS' linked to it. For more information on customers please refer to the Scheduling Schema Guide (Customer table). Foreign Key: ARC_Customer		O
arc_date_key_allocated_time	Integer	Used to filter by the date an activity is allocated, e.g. to get all allocation snapshot fact rows that represent activities that are scheduled today. Foreign Key: ARC_Date		O
arc_date_key_schedule_time	Integer	Used to filter by the date of the schedule the allocation is in, e.g. to get all allocation snapshot fact rows that are in the schedule yesterday or today. Foreign Key: ARC_Date		O
arc_date_key_sla_deadline	Integer	Used to filter by the date an activity's primary SLA ends, e.g. to get all allocation snapshot fact rows that represent activities that are scheduled today. Foreign Key: ARC_Date		O
arc_location_key_end	Int64	Used to filter by the end location of an activity, e.g. to get all allocation snapshot fact rows that end at a certain location. Foreign Key: ARC_Location		O
arc_location_key_start	Int64	Used to filter by the start location of an activity, e.g. to get all allocation snapshot fact rows that start at a certain location Foreign Key: ARC_Location		O
arc_region_group_key	Int64	Used to group allocation snapshot fact rows by one or more regions (a region group contains one or more regions). Foreign Key: ARC_Region_Group		O
arc_resource_key	Int64	Used to filter by the resource the allocation snapshot is allocated to (if it is allocated), e.g. to get all allocation snapshot fact rows that represent activities that have been allocated to resource 'James'. Foreign Key: ARC_Resource		O
arc_resource_type_key	Int64	Used to filter by the resource type the allocation snapshot is allocated to (if it is allocated), e.g. to get all allocation snapshot fact rows that represent activities that have been allocated to resources of type 'Driver'. Foreign Key: ARC_Resource_Type		O
arc_skill_group_key	Int64	Used to group allocation snapshot fact rows by one or more skills (a skill group contains one or more skills). Foreign Key: ARC_Skill_Group		O
arc_time_zone_key	Integer	Used to group allocation snapshot fact rows by time zone, e.g. get all allocation snapshot fact rows that represent activities that are appear in time zone 'Pacific'. Foreign Key: ARC_Time_Zone		O
attribute_value1	Double	The value of the additional attribute for the activity this allocation relates to (label_value), which corresponds to attribute_value_key1.		O
attribute_value2	Double	The value of the additional attribute for this allocation relates to (label_value), which corresponds to attribute_value_key2.		O
attribute_value3	Double	The value of the additional attribute for this allocation relates to (label_value), which corresponds to attribute_value_key3.		O
attribute_value4	Double	The value of the additional attribute for this allocation relates to (label_value), which corresponds to attribute_value_key4.		O
duration_minutes	Double	The duration of this allocation. This is taken from the Allocation duration. The Allocation duration is derived from the difference between the Activity Status time stamps detailing when the activity was started and when it was completed.		O
shift_start_time	Date Time	The start time of the shift the allocation is in.		O
shift_start_time_utc	Date Time	Same as shift_start_time, stored in utc.		O
sla_deadline	Date Time	The date and time the activity's primary SLA ends. This is mapped - if it exists - from the sla_jeopardy_time on the Allocation Data table in the Scheduling Database. In the case where multiple visits exist for this activity (split activity), the primary SLA of the first visit is taken into consideration only.		O
sla_deadline_utc	Date Time	Same as sla_deadline, stored in utc.		O
start_time	Date Time	The start time of this allocation. This is mapped - if it exists - from the start time on the Allocation Data table in the Scheduling Database.		O
start_time_utc	Date Time	Same as start_time, stored in utc.		O
travel_hours	Double	The travel duration to this activity allocation. This is obtained - if it exists - by subtracting the Allocation Data trave_start from the Allocation activity start.		O
travel_start_time	Date Time	The travel start time to this activity allocation. This is mapped - if it exists - from the Allocation Data travel_start_time.		O
travel_start_time_utc	Date Time	Same as travel_start_time, stored in utc.		O

Entity: ARC_Area (Dimension)¶

A dimension that derives from the id of a Polygon in the Scheduling System Database (Polygon table). Used to define a geographical area.

Attribute Name	Data Type	Description	PK	M/O
key	Int64	A unique integer key for the area (auto-increment).	PK	M
organisation_id	Integer	The organisation the row belongs to.		M
description	String(100)	A description of the area. Derives from the description of the Polygon table in the System Database.		O
is_current	Boolean	Defines whether the polygon exists in the Scheduling System Database or not. The Archiving Service will update this field if needed during each snapshot.		O
last_used	Date Time	The date and time when the row was last referenced on a fact table.		O
maximum_latitude	Double	Maximum latitude of the area. Mapping: Polygon.latitude_1 if it exists - otherwise, Polygon.latitude_2 is used		O
maximum_longitude	Double	Maximum longtitude of the area. Mapping: Polygon.longitude_3 if it exists - otherwise, Polygon.longitude_4 is used		O
minimum_latitude	Double	Minimum latitude of the area. Mapping: Polygon.latitude_3 if it exists - otherwise, Polygon.latitude_4 is used		O
minimum_longitude	Double	Minimum longtitude of the area. Mapping: Polygon.longitude_1 if it exists - otherwise, Polygon.longitude_2 is used		O
polygon_id	String(32)	A unique identifier for a polygon, derives from the id of the Polygon in the Scheduling System Database (Polygon.id).		O

Entity: ARC_Attention (Dimension)¶

A dimension that derives from the highlight field on Activity Status in the Scheduling Database (Activity_Status.highlight column).

Attribute Name	Data Type	Description	PK	M/O
key	Int64	A unique integer key for the attention (auto-increment).	PK	M
organisation_id	Integer	The organisation the row belongs to.		M
arc_dataset_key	Integer	The dataset the row belongs to. Foreign Key: ARC_Dataset		O
label	String(100)	A label created based on the parameter 'ArchiveStandardLabelFormat'. Defaults to {description}.		O
last_updated	Date Time	The date and time when the row was last updated.		O
last_used	Date Time	The date and time when the row was last referenced on a fact table.		O

Entity: ARC_Attribute (Data)¶

A data entry table in the Scheduling Workbench where a user can set up which attributes to be reported on. Up to four attributes can be set for activity and resource respectively. Resource Snapshot Fact, Activity Fact and Allocation Fact all allow to be grouped by up to four attributes each. See Attributes section in the User Interface section of this guide for more detail.

Attribute Name	Data Type	Description	PK	M/O
attribute_entity	String(32)	The entity this attribute is related to. Allowed Values: ACTIVITY, RESOURCE	PK	M
attribute_number	Integer	The sequence number of the attribute Allowed Values: 1, 2, 3 or 4	PK	M
attribute_source	String(32)	The source of the additional attribute. Allowed Values: ACTIVITY, RESOURCE		O
default_description	String(2000)	The default description to be used for this attribute.		O
label	String(100)	A label created based on the parameter 'ArchiveStandardLabelFormat'. Defaults to {description}.		O

Entity: ARC_Attribute_Description (Data)¶

A data entry table in the Scheduling Workbench where a user can set up attribute groups, providing the attribute value (Additional_Attribute.label_value in the Scheduling Databae) is a numerical value. See Attributes section in the User Interface section of this guide for more detail.

Attribute Name	Data Type	Description	PK	M/O
attribute_entity	String(32)	The entity this attribute is related to. Foreign Key: ARC_Attribute Allowed Values: ACTIVITY, RESOURCE	PK	M
attribute_number	Integer	The sequence number of the attribute Foreign Key: ARC_Attribute Allowed Values: 1, 2, 3 or 4	PK	M
minimum_value	Double	The minimum value for this grouping of the attributes. See Attributes section in the User Interface section of this guide for an example.	PK	M
description	String(2000)	The description to be used for this attribute description.		M

Entity: ARC_Attribute_Value (Dimension)¶

A dimension that derives from the Additional Attribute in the Scheduling Database (Additional_Attribute table).

Attribute Name	Data Type	Description	PK	M/O
key	Int64	A unique integer key for the attribute value (auto-increment).	PK	M
organisation_id	Integer	The organisation the row belongs to.		M
arc_dataset_key	Integer	The dataset the row belongs to. Foreign Key: ARC_Dataset		O
attribute_entity	String(32)	The entity this attribute is related to. Allowed Values: ACTIVITY, RESOURCE		O
description	String(2000)	Description for attribute value. Mapping: This value is derived from the default_description on the ARC_Attribute table if it exists. Otherwise, the label_value from the Additional Attribute in the Scheduling Database is used.		O
label	String(100)	A label created based on the parameter 'ArchiveStandardLabelFormat'. Defaults to {description}.		O
last_updated	Date Time	The date and time when the row was last updated.		O
last_used	Date Time	The date and time when the row was last referenced on a fact table.		O

Entity: ARC_Calendar (Dimension)¶

A dimension that derives from the Calendar in the Scheduling Database (Calendar table).

Attribute Name	Data Type	Description	PK	M/O
key	Int64	A unique integer key for the calendar(auto-increment).	PK	M
organisation_id	Integer	The organisation the row belo ngs to.		M
arc_dataset_key	Integer	The dataset the row belongs to. Foreign Key: ARC_Dataset		O
description	String(2000)	A description for the calendar.		O
id	String(32)	A unique id for the calendar. Mapping: Calendar.id		O
label	String(256)	A label created based on the parameter 'ArchiveStandardLabelFormat'. Defaults to {description}.		O
last_updated	Date Time	The date and time when the row was last updated.		O
last_used	Date Time	The date and time when the row was last referenced on a fact table.		O

Entity: ARC_Constraint_Violation (Dimension)¶

A static dimension that represents constraint violations after a 'base' date. This table is used to reference a date in the fact tables and make the comparison analysis easier.

This includes all exceptions that are in 'Externally Violated Constraints' or 'Catch All'. Please see 'Basic Scheduling Concepts Guide' for more detail of externally violated constraints. Allowed values are:

'Old activity ignored'
'Invalid activity location'
'Invalid or missing activity duration'
'Invalid or missing activity status'
'Activity fixed or committed to unknown resource'
'Invalid fixed time'
'Invalid fixed resource'
'Resource fixed at invalid time'
'Activity fixed or committed to resource without parts'
'Activity fixed or committed to resource exceeding max travel'
'Invalid travel time weighting will be ignored'
'Invalid activity separation'

Examples¶

<ARC_Constraint_Violation>
    <key>5</key>
    <label>Activity fixed or committed to unknown resource</label>
    <reason_type_id>580</reason_type_id>
    <source_message_id>1065</source_message_id>
    <organisation_id>1</organisation_id>
</ARC_Constraint_Violation>

<ARC_Constraint_Violation>
    <key>7</key>
    <label>Invalid fixed time</label>
    <reason_type_id>550</reason_type_id>
    <source_message_id>942</source_message_id>
    <organisation_id>1</organisation_id>
</ARC_Constraint_Violation>

Attribute Name	Data Type	Description	PK	M/O
key	Integer	A unique integer key for the constraint violation (auto-increment).	PK	M
organisation_id	Integer	The organisation the row belongs to.		M
label	String(256)	A label created based on the parameter 'ArchiveStandardLabelFormat'. Defaults to {description}.		O
reason_type_id	Integer	The reason type the externally violated constraint corresponds to. Mapping: Reason_Type.id (Scheduling System Database)		O
source_message_id	Integer	The message id that corresponds to the reason type id.		O

Entity: ARC_Contract (Dimension)¶

A dimension that derives from the Contract in the Scheduling Database (Calendar table).

Attribute Name	Data Type	Description	PK	M/O
key	Int64	A unique integer key for the contract(auto-increment).	PK	M
id	String(32)	A unique id for the contract. Mapping: Contract.id		M
arc_dataset_key	Integer	The dataset the row belongs to. Foreign Key: ARC_Dataset		O
description	String(2000)	A description for the contract.		O
label	String(256)	A label created based on the parameter 'ArchiveStandardLabelFormat'. Defaults to {description}.		O
last_updated	Date Time	The date and time when the row was last updated.		O
last_used	Date Time	The date and time when the row was last referenced on a fact table.		O
organisation_id	Integer	The organisation the row belongs to.		O

Entity: ARC_Customer (Dimension)¶

A dimension that derives from the Customer in the Scheduling Database (Customer table).

Attribute Name	Data Type	Description	PK	M/O
key	Int64	A unique integer key for the customer(auto-increment).	PK	M
id	String(100)	A unique id for the customer. Mapping: Customer.id		M
organisation_id	Integer	The organisation the row belongs to.		M
arc_dataset_key	Integer	The dataset the row belongs to. Foreign Key: ARC_Dataset		O
description	String(2000)	A description for the customer.		O
label	String(256)	A label created based on the parameter 'ArchiveStandardLabelFormat'. Defaults to {description}.		O
last_updated	Date Time	The date and time when the row was last updated.		O
last_used	Date Time	The date and time when the row was last referenced on a fact table.		O

Entity: ARC_Dataset (Dimension)¶

A dimension that derives from the Dataset in the Scheduling Database (Dataset table).

Attribute Name	Data Type	Description	PK	M/O
key	Integer	A unique integer key for the dataset (auto-increment).	PK	M
dataset_id	String(32)	A unique id for the dataset. Derives from the id on the Dataset.		M
organisation_id	Integer	The organisation the row belongs to.		M
description	String(2000)	A description for the dataset.		O
external_database_id	Integer	The external database id of the database the snapshot is taken from.		O
last_updated	Date Time	The date and time when the dataset was last updated.		O

Entity: ARC_Date (Dimension)¶

A static dimension that represents a date after a 'base' date. This table is used to reference a date in the fact tables and make the comparison analysis easier.

Unlike other dimension tables which have auto-generated keys, the key for the ARC_Date table measures the total number of days from a set time (suggest 1st Jan 2000). This ensures that the PK is always date ordered - note that the value could be negative if required. The ARC_Date contains various other atributes which are useful for reporting.

Examples¶

<ARC_Date>
    <key>1</key>
    <date>2000-01-02T00:00:00+00:00</date>
    <label>02/01/2000 00:00:00</label>
    <day_of_week>0</day_of_week>
    <day_of_week_label>Sunday</day_of_week_label>
    <day_of_month>2</day_of_month>
    <day_of_year>2</day_of_year>
    <month>1</month>
    <month_label>January</month_label>
    <year>2000</year>
    <week_in_year>52</week_in_year>
    <absolute_week_number>1</absolute_week_number>
    <absolute_month_number>1</absolute_month_number>
    <is_weekday>false</is_weekday>
    <weekday_label>Week Day</weekday_label>
    <organisation_id>1</organisation_id>
</ARC_Date>

Attribute Name	Data Type	Description	PK	M/O
key	Integer	A unique integer key for the date (auto-increment).	PK	M
organisation_id	Integer	The organisation the row belongs to.	PK	M
absolute_month_number	Integer	The absolute month number starting in January 2000. Allowed Values: 1 to 12		M
absolute_week_number	Integer	The absolute week number starting with the first week in January 2000.		M
date	Date Time	The date the row refers to (time will be 00:00:00 as the column is a datetime type)		M
day_of_month	Integer	The day sequence in the month of the date column value.		M
day_of_week	Integer	The day sequence in the week of the date column value Allowed Values: Sunday=0, Saturday=6		M
day_of_week_label	String(32)	A label for the day of week, e.g. 'Sunday'		M
day_of_year	Integer	The day sequence of the year.		M
is_weekday	Boolean	Whether the day of the date column value is a week day or not, i.e. Monday-Friday.		M
month	Integer	The month sequence of the date column value Allowed Values: 1-12		M
month_label	String(32)	A label for the month, e.g. 'January'		M
week_in_year	Integer	The week sequence in year. Notes: The first 3-4 days of the year may actually belong to week 52.		M
weekday_label	String(32)	A label for week day, e.g. 'Week Day'		M
year	Integer	The year of the date column value		M
label	String(256)	A label for the date column value, e.g. '02/01/2000 00:00:00'. This label is configurable via message texts.		O

Entity: ARC_Fuel_Type (Dimension)¶

Defines the type of fuel used in the vehicle.

Attribute Name	Data Type	Description	PK	M/O
key	Int64	A unique integer key for the fuel type (auto-increment)	PK	M
id	String(32)	A unique id for the fuel type		M
organisation_id	Integer	The organisation the row belongs to.		M
arc_dataset_key	Integer	The dataset the row belongs to. Foreign Key: ARC_Dataset		O
description	String(2000)	Description of the type of fuel used in the vehicle		O
last_updated	Date Time	The date and time when the row was last updated.		O
last_used	Date Time	The date and time when the row was last referenced on a fact table.		O

Entity: ARC_Location (Dimension)¶

A dimension that derives from the Location in the Scheduling Database (Location table).

Attribute Name	Data Type	Description	PK	M/O
key	Int64	A unique integer key for the location (auto-increment).	PK	M
id	String(32)	A unique id for the location. Derives from the id on the Location.		M
organisation_id	Integer	The organisation the row belongs to.		M
address_line1	String(128)	The location address line 1. Mapping: Location.address_line1		O
address_line2	String(128)	The location address line 2. Mapping: Location.address_line2		O
address_line3	String(128)	The location address line 3. Mapping: Location.address_line3		O
address_line4	String(128)	The location address line 4. Mapping: Location.address_line4		O
address_line5	String(128)	The location address line 5. Mapping: Location.address_line5		O
address_line6	String(128)	The location address line 6. Mapping: Location.address_line6		O
arc_dataset_key	Integer	The dataset the row belongs to. Foreign Key: ARC_Dataset		O
city	String(40)	The location city, derives from Location.city		O
description	String(2000)	The description of the location, derives from any combination of columns on the Location table. This is configurable via the parameter 'ResourceDescriptionFormat', which defaults to '{Location.name} [{Location.id}]'.		O
label	String(256)	A label created based on the parameter 'ArchiveStandardLabelFormat'. Defaults to {description}. Mapping: Location.description		O
last_updated	Date Time	The date and time when the row was last updated.		O
last_used	Date Time	The date and time when the row was last referenced on a fact table.		O
latitude	Double	The latitude of the location. Mapping: Location.latitude		O
locality	String(32)	The locality of the location. Mapping: Location.locality		O
location_type_description	String(2000)	The location type description. Mapping: Location_Type.description		O
location_type_id	String(32)	The location type id. Mapping: Location_Type.id		O
location_type_label	String(256)	A label created based on the parameter 'ArchiveStandardLabelFormat'. Defaults to {description}. Mapping: Location_Type.description		O
longitude	Double	The longitude of the location. Mapping: Location.longitude		O
name	String(64)	The location name. Mapping: Location.name		O
post_code_zip	String(20)	The location post code zip. Mapping: Location.post_code_zip		O
spatial_key	Integer	The spatial key of the location, derives from 'latitude' and 'longitude'.		O
state	String(40)	The location state. Mapping: Location.state		O

Entity: ARC_Plan_Fact (Fact)¶

The Plan Fact is a fact table that represents a schedule plan. There will be only one plan per combination of organisation and dataset id in the Scheduling Database.

The Plan Fact contains a number of columns that provide useful information and statistics that describe the schedule plan's performance. For example, there are a number of columns providing information related to a number of activities linked to each plan and what their status is, such as:

activities scheduled
activities completed
activities completed in SLA
activities completed late

Additionally, there are a set of columns that provide statistics on how time is spent in each schedule plan, such as:

total work time and work time today
total idle time and idle time today
total on site time and on site time today
total travel time and travel time today
etc.

The Plan Fact table is a denormalised data table about the schedule plan that can be easily filtered and grouped on a number of dimensions. The fact table contains a set of dimension columns that provide a context about the plan. Namely:

which dataset the plan is in (via ARC_Dataset)
which organisation the plan is in
the date of the plan (via arc_schedule_time key, ARC_Date)
etc.

This allows for the Plan Fact table to be filtered or grouped by any of these dimensions.

Note

Many of the date times and stats on the Plan Fact are sourced from the Plan, Plan_Resource, Plan_Travel and Plan_Route tables in the Scheduling Database.

Note

Many of the stats have a corresponding 'today' statistic. This is introduced so that it's easier to report on statistics that are specific to day the current schedule time belongs to. 'Today' refers to shifts that start by the end of the current schedule day. This means that any shifts that overrun past midnight do not contribute to any of the 'today' statistics.

Note

Many of the stats have a corresponding 'completed' statistic. This is introduced so that it's easier to report on statistics that are belong to the past, i.e. have been completed before the current schedule time. For example, 'completed_break_hours' refer to the total duration of the activities of class 'BREAK' that have been completed by the current schedule time.

Warning

A shift is regarded as being in the past if the overtime end time of the shift is before the current schedule time.

Note

Statistics that depend on Activity SLA in the Scheduling Database depend it is defined to be start based or not. If so, the statistic is calculated accordingly. For example, the integer field 'activities_completed_in_sla' is calculated by comparing the activity start time to the SLA end if the Activity SLA is start_based. Alternatively, if the Activity SLA is set to not be start based, the activity end time is compared to the SLA end instead. For more detail on the use of the start_based field please see Activity_SLA table in the Scheduling Schema Guide

Attribute Name	Data Type	Description	PK	M/O
arc_dataset_key	Integer	Used to filter by dataset, i.e. by dataset id via the ARC_Dataset dimension table. Foreign Key: ARC_Dataset	PK	M
organisation_id	Integer	The organisation the plan snapshot fact belongs to. Foreign Key: ARC_Date		M
activities_completed	Integer	Number of activities completed by the resource (of status 'Complete', i.e. 70).		O
activities_completed_early	Integer	Total number of activities completed early by resources (of status 'Complete', i.e. 70). Notes: 'Completed Early' is defined as the activities that start before their expected start, i.e. the Allocation 'activity_start' is before the 'expected_start' on the Allocation Data in the Scheduling Database, which once committed is the date and time that the resource expects to start the activity. Note that this statistic depends on whether the Activity SLA that is related to the Activity is defined to be start based or not. For more detail on this please see Activity_SLA table in the Scheduling Schema Guide.		O
activities_completed_in_sla	Integer	Total number of activities completed within the primary SLA resources (of status 'Complete', i.e. 70). Notes: 'Completed within the primary SLA' is defined as the activities that start before their primary SLA end, i.e. the Allocation 'activity_start' is before the 'datetime_end' on the Activity SLA or if that doesn't exist, the 'sla_jeopardy_time' on the Allocation Data in the Scheduling Database. Note that this statistic depends on whether the Activity SLA that is related to the Activity is defined to be start based or not. For more detail on this please see Activity_SLA table in the Scheduling Schema Guide.		O
activities_completed_late	Integer	Total number of activities completed late by resources (of status 'Complete', i.e. 70). Notes: 'Completed Late' is defined as the activities that start after their expected start, i.e. the Allocation 'activity_start' is after the 'expected_start' on the Allocation Data in the Scheduling Database, which once committed is the date and time that the resource expects to start the activity. Note that this statistic depends on whether the Activity SLA that is related to the Activity is defined to be start based or not. For more detail on this please see Activity_SLA table in the Scheduling Schema Guide.		O
activities_scheduled	Integer	Total number of activities scheduled.		O
activities_scheduled_today	Integer	Total number of activities scheduled today. Notes: The value for this is specified as all the allocations of visit id 1 (i.e. the first visit of a split activity if multiple visits exist) that start (activity_start on Allocation table in Scheduling Database) on the date of the current schedule time of the snapshot.		O
arc_date_key_schedule_time	Integer	An integer key, referencing a key on the ARC_Date table. Used to filter by the date of the schedule the plan is in, e.g. to get all plan snapshot fact rows that are in the schedule yesterday or today. Foreign Key: ARC_Date		O
arc_time_zone_key	Integer	An integer key, referencing a key on the ARC_Time_Zone table. Used to group plan snapshot fact rows by time zone, e.g. get all plan snapshot fact rows occur in time zone 'Pacific'. Foreign Key: ARC_Time_Zone		O
break_hours_today	Double	The total amount of time spent as breaks (activities of class 'BREAK') today. The value for this is mapped from the 'total_break_time' column on the Plan_Resource table in the Scheduling Database, for shifts that start on the current schedule day.		O
completed_break_hours	Double	The total amount of time spent as breaks (activities of class 'BREAK') that is in the past, i.e. before the current schedule time when the snapshot is taken. The value for this is obtained by summing up all the total break time of shifs (Plan_Route rows for this shift in the Scheduling Database) in the past. Notes: The value of 'break time' is obtained from 'total_break_time' column on Plan_Route table in the Scheduling Database.		O
completed_idle_hours	Double	The total amount of unused shift time that is in the past, i.e. before the current schedule time when the snapshot is taken. The value for this is obtained by summing up all the 'total_unutilised_time' of shifs (Plan_Route rows for this shift in the Scheduling Database) in the past. Notes: 'Idle' is defined as the total amount of shift time that does not have any travels or activities of class 'CALL' in it. The value of 'idle' is obtained from 'total_unutilised_time' column on Plan_Route table in the Scheduling Database.		O
completed_on_site_hours	Double	The total amount of time spent on-site doing activities that is in the past, i.e. before the current schedule time when the snapshot is taken. The value for this is obtained by summing up all the 'on site' time of shifts (Plan_Route rows for this shift) in the past. Notes: 'On Site' is defined as the total amount of shift time that corresponds to activities of class 'CALL'. The value of 'on site' is obtained from 'total_on_site_time' column on Plan_Route table in the Scheduling Database.		O
completed_overtime_hours	Double	The total amount of time spent in shifts' overtime that is in the past, i.e. before the current schedule time when the snapshot is taken. The value for this is obtained by summing up all the overtime of shifts (Plan_Route rows for this shift) in the past. Notes: 'Overtime' is calculated by subtracting the Plan_Route column 'shift_actual_end' from the 'shift_overtime_end' on the Plan_Route table in the Scheduling Database.		O
completed_travel_hours	Double	The total amount of time spent travelling in shifts that is in the past, i.e. before the current schedule time when the snapshot is taken. The value for this is obtained by summing up all the 'travel time' (Plan_Route rows for this shift) in the past. Notes: 'Travel time' is defined as the total travel time in shifts. The value of 'travel time' is obtained from the 'expected_travel_time' column on Plan_Travel table in the Scheduling Database for rows that have a shift start specified on them.		O
completed_work_hours	Double	The total amount of time spent 'workable' in shifts that is in the past, i.e. before the current schedule time when the snapshot is taken. The value for this is obtained by summing up all the 'workable' time in shifts (Plan_Route rows for this shift) in the past. Notes: 'Workable' is defined as the total time of shifts. The value of 'workable' is obtained by subtracting the 'shift_actual_start' or the 'shift_start_datetime' from the 'shift_overtime_end' column on Plan_Route table in the Scheduling Database.		O
expected_travel_hours	Double	The total amount of time predicted to spend travelling in shifts. The value for this is obtained by summing up all the 'expected travel time' (Plan_Travel rows). Notes: 'Expected travel time' is defined as the total extpected travel time of travels that start in the future, i.e. past the current schedule time when the snapshot is taken. The value of 'expected travel time' is obtained from the 'expected_travel_time' column on Plan_Travel table in the Scheduling Database for rows that have a shift start specified on them.		O
idle_hours_today	Double	The total amount of unused shift time today. The value for this is obtained by summing up all the 'total_unutilised_time' of shifs (Plan_Route rows for this shift in the Scheduling Database) for today. Notes: 'Idle' is defined as the total amount of shift time that does not have any travels or activities of class 'CALL' in it, for shifts that start on the current schedule day. The value of 'idle' is obtained from 'total_unutilised_time' column on Plan_Route table in the Scheduling Database.		O
last_updated	Date Time	The date and time when the row was last updated.		O
on_site_hours_today	Double	The total amount of 'on site' shift time today. The value for this is obtained by summing up all the 'total_on_site_time' of shifs (Plan_Route rows for this shift in the Scheduling Database) for today. Notes: 'On Site' is defined as the total amount of shift time that corresponds to activities of class 'CALL', for shifts that start on the current schedule day. The value of 'on site' is obtained from 'total_on_site_time' column on Plan_Route table in the Scheduling Database.		O
overtime_hours_today	Double	The total amount of 'overtime' today. The value for this is obtained by summing up all the overtime of shifts (Plan_Route rows for this shift) today. Notes: 'Overtime' is calculated by subtracting the Plan_Route column 'shift_actual_end' from the 'shift_overtime_end' on the Plan_Route table in the Scheduling Database, for shifts that start on the current schedule day.		O
plan_id	Int64	The plan id the fact relates to.		O
private_activity_hours_today	Double	The total amount of 'private time' spent in shifts today. The value for this is obtained by summing up all the 'private time' of shifts (Plan_Route rows for this shift) today. Notes: 'Private time' is obtained from 'total_private_time' column on Plan_Route table in the Scheduling Database, for shifts that start on the current schedule day.		O
total_active_resource_today	Integer	Total active resources today. Notes: 'Active resource' is defined as a resource that has at least one shift, which contains either at least one activity of class 'CALL' or a travel.		O
total_activities	Integer	Total number of activities of all values of activity class.		O
total_break_hours	Double	The total amount of time spent as breaks (activities of class 'BREAK'). Notes: The value of 'total break time' is obtained from 'total_break_time' column on Plan_Resource table in the Scheduling Database.		O
total_idle_hours	Double	The total amount of unused shift time. Notes: 'Idle' is defined as the total amount of shift time that does not have any travels or activities of class 'CALL' in it. The value of 'total idle time' is obtained from 'total_unutilised_time' column on Plan_Resource table in the Scheduling Database.		O
total_on_site_hours	Double	The total amount of time spent on-site doing activities. Notes: 'On Site' is defined as the total amount of shift time that corresponds to activities of class 'CALL'. The value of 'total on site' is obtained from 'total_on_site_time' column on Plan_Resource table in the Scheduling Database.		O
total_overtime_hours	Double	The total amount of time spent in shifts' overtime. The value for this is obtained by summing up all the overtime of shifts (Plan_Route rows for this shift). Notes: 'Overtime' is calculated by subtracting the Plan_Route column 'shift_actual_end' from the 'shift_overtime_end' on the Plan_Route table in the Scheduling Database.		O
total_private_activity_hours	Double	The total amount of 'private time' spent in shifts. Notes: 'Total private time' is obtained from 'total_private_time' column on Plan_Resource table in the Scheduling Database.		O
total_resources	Integer	Total number of resources.		O
total_travel_hours	Double	The total amount of time spent travelling in shifts. Notes: 'Total Travel time' is defined as the total travel time for the resource in shifts. The value of 'total travel time' is obtained from the 'expected_travel_time' column on Plan_Travel table in the Scheduling Database for rows that have a shift start specified on them.		O
total_work_hours	Double	The total amount of time spent 'workable' in shifts. The value for this is obtained by summing up all the 'workable' time in shifts (Plan_Route rows for this shift). Notes: 'Workable' is defined as the total time of shifts. The value of 'workable' is obtained by subtracting the 'shift_actual_start' or the 'shift_start_datetime' from the 'shift_overtime_end' column on Plan_Route table in the Scheduling Database.		O
travel_hours_today	Double	The total amount of time spent travelling in shifts today. Notes: 'Travel time today' is defined as the total travel time for the resource in shifts, for travels that start on the current schedule day. The value of 'total travel time' is obtained from the 'expected_travel_time' column on Plan_Travel table in the Scheduling Database for rows that have a shift start specified on them.		O
work_hours_today	Double	The total amount of time spent 'workable' in shifts today. The value for this is obtained by summing up all the 'workable' time in shifts (Plan_Route rows for this shift) today. Notes: 'Workable' is defined as the total time of shifts, for shifts that start on the current schedule day. The value of 'workable' is obtained by subtracting the 'shift_actual_start' or the 'shift_start_datetime' from the 'shift_overtime_end' column on Plan_Route table in the Scheduling Database.		O

Entity: ARC_Plan_Snapshot_Fact (Fact)¶

The Plan Snapshot Fact is a periodic snapshot fact table that represents a schedule plan. Every time a snapshot occurs, a plan snapshot fact row is appended for each plan in the Plan Fact table. Its purpose is to take a snapshot of the plan so that reports can be generated on the 'state' of the schedule plan over time (via the snapshots).

For more information see the Plan Fact entity.

Attribute Name	Data Type	Description	PK	M/O
arc_snapshot_detail_key	Int64	Used to group plan snapshot fact rows by snapshot or to filter by a particular snapshot. Foreign Key: ARC_Snapshot_Detail	PK	M
organisation_id	Integer	The organisation the plan snapshot fact belongs to. Foreign Key: ARC_Date		M
activities_completed	Integer	Number of activities completed by the resource (of status 'Complete', i.e. 70).		O
activities_completed_early	Integer	Total number of activities completed early by resources (of status 'Complete', i.e. 70). Notes: 'Completed Early' is defined as the activities that start before their expected start, i.e. the Allocation 'activity_start' is before the 'expected_start' on the Allocation Data in the Scheduling Database, which once committed is the date and time that the resource expects to start the activity. Note that this statistic depends on whether the Activity SLA that is related to the Activity is defined to be start based or not. For more detail on this please see Activity_SLA table in the Scheduling Schema Guide.		O
activities_completed_in_sla	Integer	Total number of activities completed within the primary SLA resources (of status 'Complete', i.e. 70). Notes: 'Completed within the primary SLA' is defined as the activities that start before their primary SLA end, i.e. the Allocation 'activity_start' is before the 'datetime_end' on the Activity SLA or if that doesn't exist, the 'sla_jeopardy_time' on the Allocation Data in the Scheduling Database. Note that this statistic depends on whether the Activity SLA that is related to the Activity is defined to be start based or not. For more detail on this please see Activity_SLA table in the Scheduling Schema Guide.		O
activities_completed_late	Integer	Total number of activities completed late by resources (of status 'Complete', i.e. 70). Notes: 'Completed Late' is defined as the activities that start after their expected start, i.e. the Allocation 'activity_start' is after the 'expected_start' on the Allocation Data in the Scheduling Database, which once committed is the date and time that the resource expects to start the activity. Note that this statistic depends on whether the Activity SLA that is related to the Activity is defined to be start based or not. For more detail on this please see Activity_SLA table in the Scheduling Schema Guide.		O
activities_scheduled	Integer	Total number of activities scheduled.		O
activities_scheduled_today	Integer	Total number of activities scheduled today. Notes: The value for this is specified as all the allocations of visit id 1 (i.e. the first visit of a split activity if multiple visits exist) that start (activity_start on Allocation table in Scheduling Database) on the date of the current schedule time of the snapshot.		O
arc_dataset_key	Integer	Used to filter by dataset, i.e. by dataset id via the ARC_Dataset dimension table. Foreign Key: ARC_Dataset		O
arc_date_key_schedule_time	Integer	An integer key, referencing a key on the ARC_Date table. Used to filter by the date of the schedule the plan is in, e.g. to get all plan snapshot fact rows that are in the schedule yesterday or today. Foreign Key: ARC_Date		O
arc_time_zone_key	Integer	An integer key, referencing a key on the ARC_Time_Zone table. Used to group plan snapshot fact rows by time zone, e.g. get all plan snapshot fact rows occur in time zone 'Pacific'. Foreign Key: ARC_Time_Zone		O
break_hours_today	Double	The total amount of time spent as breaks (activities of class 'BREAK') today. The value for this is mapped from the 'total_break_time' column on the Plan_Resource table in the Scheduling Database, for shifts that start on the current schedule day.		O
completed_break_hours	Double	The total amount of time spent as breaks (activities of class 'BREAK') that is in the past, i.e. before the current schedule time when the snapshot is taken. The value for this is obtained by summing up all the total break time of shifs (Plan_Route rows for this shift in the Scheduling Database) in the past. Notes: The value of 'break time' is obtained from 'total_break_time' column on Plan_Route table in the Scheduling Database.		O
completed_idle_hours	Double	The total amount of unused shift time that is in the past, i.e. before the current schedule time when the snapshot is taken. The value for this is obtained by summing up all the 'total_unutilised_time' of shifs (Plan_Route rows for this shift in the Scheduling Database) in the past. Notes: 'Idle' is defined as the total amount of shift time that does not have any travels or activities of class 'CALL' in it. The value of 'idle' is obtained from 'total_unutilised_time' column on Plan_Route table in the Scheduling Database.		O
completed_on_site_hours	Double	The total amount of time spent on-site doing activities that is in the past, i.e. before the current schedule time when the snapshot is taken. The value for this is obtained by summing up all the 'on site' time of shifts (Plan_Route rows for this shift) in the past. Notes: 'On Site' is defined as the total amount of shift time that corresponds to activities of class 'CALL'. The value of 'on site' is obtained from 'total_on_site_time' column on Plan_Route table in the Scheduling Database.		O
completed_overtime_hours	Double	The total amount of time spent in shifts' overtime that is in the past, i.e. before the current schedule time when the snapshot is taken. The value for this is obtained by summing up all the overtime of shifts (Plan_Route rows for this shift) in the past. Notes: 'Overtime' is calculated by subtracting the Plan_Route column 'shift_actual_end' from the 'shift_overtime_end' on the Plan_Route table in the Scheduling Database.		O
completed_travel_hours	Double	The total amount of time spent travelling in shifts that is in the past, i.e. before the current schedule time when the snapshot is taken. The value for this is obtained by summing up all the 'travel time' (Plan_Route rows for this shift) in the past. Notes: 'Travel time' is defined as the total travel time in shifts. The value of 'travel time' is obtained from the 'expected_travel_time' column on Plan_Travel table in the Scheduling Database for rows that have a shift start specified on them.		O
completed_work_hours	Double	The total amount of time spent 'workable' in shifts that is in the past, i.e. before the current schedule time when the snapshot is taken. The value for this is obtained by summing up all the 'workable' time in shifts (Plan_Route rows for this shift) in the past. Notes: 'Workable' is defined as the total time of shifts. The value of 'workable' is obtained by subtracting the 'shift_actual_start' or the 'shift_start_datetime' from the 'shift_overtime_end' column on Plan_Route table in the Scheduling Database.		O
expected_travel_hours	Double	The total amount of time predicted to spend travelling in shifts. The value for this is obtained by summing up all the 'expected travel time' (Plan_Travel rows). Notes: 'Expected travel time' is defined as the total extpected travel time of travels that start in the future, i.e. past the current schedule time when the snapshot is taken. The value of 'expected travel time' is obtained from the 'expected_travel_time' column on Plan_Travel table in the Scheduling Database for rows that have a shift start specified on them.		O
idle_hours_today	Double	The total amount of unused shift time today. The value for this is obtained by summing up all the 'total_unutilised_time' of shifs (Plan_Route rows for this shift in the Scheduling Database) for today. Notes: 'Idle' is defined as the total amount of shift time that does not have any travels or activities of class 'CALL' in it, for shifts that start on the current schedule day. The value of 'idle' is obtained from 'total_unutilised_time' column on Plan_Route table in the Scheduling Database.		O
on_site_hours_today	Double	The total amount of 'on site' shift time today. The value for this is obtained by summing up all the 'total_on_site_time' of shifs (Plan_Route rows for this shift in the Scheduling Database) for today. Notes: 'On Site' is defined as the total amount of shift time that corresponds to activities of class 'CALL', for shifts that start on the current schedule day. The value of 'on site' is obtained from 'total_on_site_time' column on Plan_Route table in the Scheduling Database.		O
overtime_hours_today	Double	The total amount of 'overtime' today. The value for this is obtained by summing up all the overtime of shifts (Plan_Route rows for this shift) today. Notes: 'Overtime' is calculated by subtracting the Plan_Route column 'shift_actual_end' from the 'shift_overtime_end' on the Plan_Route table in the Scheduling Database, for shifts that start on the current schedule day.		O
private_activity_hours_today	Double	The total amount of 'private time' spent in shifts today. The value for this is obtained by summing up all the 'private time' of shifts (Plan_Route rows for this shift) today. Notes: 'Private time' is obtained from 'total_private_time' column on Plan_Route table in the Scheduling Database, for shifts that start on the current schedule day.		O
total_active_resource_today	Integer	Total active resources today. Notes: 'Active resource' is defined as a resource that has at least one shift, which contains either at least one activity of class 'CALL' or a travel.		O
total_activities	Integer	Total number of activities of all values of activity class.		O
total_break_hours	Double	The total amount of time spent as breaks (activities of class 'BREAK'). Notes: The value of 'total break time' is obtained from 'total_break_time' column on Plan_Resource table in the Scheduling Database.		O
total_idle_hours	Double	The total amount of unused shift time. Notes: 'Idle' is defined as the total amount of shift time that does not have any travels or activities of class 'CALL' in it. The value of 'total idle time' is obtained from 'total_unutilised_time' column on Plan_Resource table in the Scheduling Database.		O
total_on_site_hours	Double	The total amount of time spent on-site doing activities. Notes: 'On Site' is defined as the total amount of shift time that corresponds to activities of class 'CALL'. The value of 'total on site' is obtained from 'total_on_site_time' column on Plan_Resource table in the Scheduling Database.		O
total_overtime_hours	Double	The total amount of time spent in shifts' overtime. The value for this is obtained by summing up all the overtime of shifts (Plan_Route rows for this shift). Notes: 'Overtime' is calculated by subtracting the Plan_Route column 'shift_actual_end' from the 'shift_overtime_end' on the Plan_Route table in the Scheduling Database.		O
total_private_activity_hours	Double	The total amount of 'private time' spent in shifts. Notes: 'Total private time' is obtained from 'total_private_time' column on Plan_Resource table in the Scheduling Database.		O
total_resources	Integer	Total number of resources.		O
total_travel_hours	Double	The total amount of time spent travelling in shifts. Notes: 'Total Travel time' is defined as the total travel time for the resource in shifts. The value of 'total travel time' is obtained from the 'expected_travel_time' column on Plan_Travel table in the Scheduling Database for rows that have a shift start specified on them.		O
total_work_hours	Double	The total amount of time spent 'workable' in shifts. The value for this is obtained by summing up all the 'workable' time in shifts (Plan_Route rows for this shift). Notes: 'Workable' is defined as the total time of shifts. The value of 'workable' is obtained by subtracting the 'shift_actual_start' or the 'shift_start_datetime' from the 'shift_overtime_end' column on Plan_Route table in the Scheduling Database.		O
travel_hours_today	Double	The total amount of time spent travelling in shifts today. Notes: 'Travel time today' is defined as the total travel time for the resource in shifts, for travels that start on the current schedule day. The value of 'total travel time' is obtained from the 'expected_travel_time' column on Plan_Travel table in the Scheduling Database for rows that have a shift start specified on them.		O
work_hours_today	Double	The total amount of time spent 'workable' in shifts today. The value for this is obtained by summing up all the 'workable' time in shifts (Plan_Route rows for this shift) today. Notes: 'Workable' is defined as the total time of shifts, for shifts that start on the current schedule day. The value of 'workable' is obtained by subtracting the 'shift_actual_start' or the 'shift_start_datetime' from the 'shift_overtime_end' column on Plan_Route table in the Scheduling Database.		O

Entity: ARC_Region (Dimension)¶

A dimension that derives from Region in the Scheduling Database (_Region table).

Examples¶

<ARC_Region>
    <key>1</key>
    <id>United Kingdom</id>
    <label>United Kingdom Branch</label>
    <description>United Kingdom Branch</description>
    <arc_dataset_key>1</arc_dataset_key>
    <last_updated>2008-09-22T08:00:00+01:00</last_updated>
    <organisation_id>1</organisation_id>
</ARC_Region>

<ARC_Region>
    <key>2</key>
    <id>England</id>
    <label>England Branch</label>
    <description>England Branch</description>
    <arc_dataset_key>1</arc_dataset_key>
    <last_updated>2008-09-22T08:00:00+01:00</last_updated>
    <organisation_id>1</organisation_id>
</ARC_Region>

<ARC_Region>
    <key>3</key>
    <id>Scotland</id>
    <label>Scotland Branch</label>
    <description>Scotland Branch</description>
    <arc_dataset_key>1</arc_dataset_key>
    <last_updated>2008-09-22T08:00:00+01:00</last_updated>
    <organisation_id>1</organisation_id>
</ARC_Region>

Attribute Name	Data Type	Description	PK	M/O
key	Int64	A unique integer key for the region (auto-increment).	PK	M
id	String(32)	A unique id for the region. Mapping: Region.id		M
organisation_id	Integer	The organisation the row belongs to.		M
arc_dataset_key	Integer	The dataset the row belongs to. Foreign Key: ARC_Dataset		O
description	String(2000)	A description for the region. Mapping: Region.description		O
label	String(256)	A label created based on the parameter 'ArchiveStandardLabelFormat'. Defaults to {description}.		O
last_updated	Date Time	The date and time when the row was last updated.		O
last_used	Date Time	The date and time when the row was last referenced on a fact table.		O

Entity: ARC_Region_Group (Dimension)¶

Each activity and/or resource may be linked to any number of regions. A region group dimension is created for each combination used, with the individual regions contained in the group specified in the ARC_Region_Group_Item bridge table.

For example, a resource may be linked to a region called 'United Kingdom', which has two child regions: 'England' and 'Scotland'. For each individual region an ARC_Region row will be created:

'United Kingdom', key=1
'Scotland', key=2, parent: United Kingdom
'England', key=3, parent: United Kingdom

Based on this hierarchy of regions the following Region_Group row would be created:

Region Group with single region for 'United Kingdom', key=1
Region Group with single region for 'Scotland', key=2
Region Group with single region for 'England', key=3
Region Group 2 regions for the combination of 'England', 'Scotland', key=4

Examples¶

<ARC_Region_Group>
    <key>1</key>
    <number_of_regions>1</number_of_regions>
    <arc_region_key_single>1</arc_region_key_single>
    <arc_dataset_key>1</arc_dataset_key>
    <organisation_id>1</organisation_id>
</ARC_Region_Group>

<ARC_Region_Group>
    <key>2</key>
    <number_of_regions>1</number_of_regions>
    <arc_region_key_single>2</arc_region_key_single>
    <arc_dataset_key>1</arc_dataset_key>
    <organisation_id>1</organisation_id>
</ARC_Region_Group>

<ARC_Region_Group>
    <key>3</key>
    <number_of_regions>1</number_of_regions>
    <arc_region_key_single>3</arc_region_key_single>
    <arc_dataset_key>1</arc_dataset_key>
    <organisation_id>1</organisation_id>
</ARC_Region_Group>

<ARC_Region_Group>
    <key>4</key>
    <number_of_regions>2</number_of_regions>
    <arc_dataset_key>1</arc_dataset_key>
    <organisation_id>1</organisation_id>
</ARC_Region_Group>

Attribute Name	Data Type	Description	PK	M/O
key	Int64	A unique integer key for the region group (auto-increment).	PK	M
organisation_id	Integer	The organisation the row belongs to.		M
arc_dataset_key	Integer	The dataset the row belongs to. Foreign Key: ARC_Dataset		O
arc_region_key_single	Int64	If number_of_regions is 1, this column will be set with the value of the single region this region group contains.		O
last_updated	Date Time	The date and time when the row was last updated.		O
last_used	Date Time	The date and time when the row was last referenced on a fact table.		O
number_of_regions	Integer	The number of regions the region group contains.		O

Entity: ARC_Region_Group_Item (Dimension)¶

A dimension 'bridge' table used to store the region group individual regions belong to. This is introduced to make queries easier. See section 'Views & Functions' and 'Example Queries' in the 'Reporting' section for the 'Overview' in this guide.

Attribute Name	Data Type	Description	PK	M/O
arc_region_group_key	Int64	An integer key for the region group this region group item refers to. Foreign Key: ARC_Region_Group	PK	M
arc_region_key	Int64	An integer key for the individual region. Foreign Key: ARC_Region	PK	M
organisation_id	Integer	The organisation the row belongs to.		M

Entity: ARC_Region_Hierarchy (Dimension)¶

A dimension table used to define the hierarchy between regions. Essentially, the region hierarchy is denormalised into the ARC_Region_Hierarchy 'bridge' table.

Examples¶

<ARC_Region_Hierarchy>
    <arc_region_key_parent>1</arc_region_key_parent>
    <arc_region_key_child>1</arc_region_key_child>
    <levels_between>0</levels_between>
    <is_top>false</is_top>
    <is_bottom>true</is_bottom>
    <arc_dataset_key>1</arc_dataset_key>
    <organisation_id>1</organisation_id>
</ARC_Region_Hierarchy>
<ARC_Region_Hierarchy>
    <arc_region_key_parent>2</arc_region_key_parent>
    <arc_region_key_child>1</arc_region_key_child>
    <levels_between>1</levels_between>
    <is_top>true</is_top>
    <is_bottom>true</is_bottom>
    <arc_dataset_key>1</arc_dataset_key>
    <organisation_id>1</organisation_id>
</ARC_Region_Hierarchy>

Attribute Name	Data Type	Description	PK	M/O
arc_region_key_child	Int64	An integer key for the child region.	PK	M
arc_region_key_parent	Int64	An integer key for the parent region.	PK	M
arc_dataset_key	Integer	The dataset the row belongs to.		M
is_bottom	Boolean	Defines whether this hierarchy pair is at the bottom of the entire region hierarchy or not.		M
is_top	Boolean	Defines whether this hierarchy pair is at the bottom of the entire region hierarchy or not.		M
levels_between	Integer	The hierarchy levels between the child and parent region.		M
organisation_id	Integer	The organisation the row belongs to.		M

Entity: ARC_Resource (Dimension)¶

A dimension that derives from Resource in the Scheduling Database (Resources table).

Attribute Name	Data Type	Description	PK	M/O
key	Int64	A unique integer key for the resource (auto-increment).	PK	M
id	String(32)	A unique id for the resource. Derives from the id on the Resources table.		M
organisation_id	Integer	The organisation the row belongs to.		M
arc_attribute_value_key1	Int64	Used to filter or group by attribute value. i.e. an additional attribute set up to be reported for this resource. Foreign Key: ARC_Attribute_Value		O
arc_attribute_value_key2	Int64	Used to filter or group by attribute value. i.e. an additional attribute set up to be reported for this resource. Foreign Key: ARC_Attribute_Value		O
arc_attribute_value_key3	Int64	Used to filter or group by attribute value. i.e. an additional attribute set up to be reported for this resource. Foreign Key: ARC_Attribute_Value		O
arc_attribute_value_key4	Int64	Used to filter or group by attribute value. i.e. an additional attribute set up to be reported for this resource. Foreign Key: ARC_Attribute_Value		O
arc_dataset_key	Integer	The dataset the row belongs to. Foreign Key: ARC_Dataset		O
arc_region_group_key	Int64	The region group the resource is linked to, derives from the Resource Regions the resource is linked to (Resource_Region in the Scheduling Database). Foreign Key: ARC_Region_Group		O
arc_resource_type_key	Int64	The type of the resources, derives from Resource.resource_type_id. Foreign Key: ARC_Resource_Type		O
arc_skill_group_key	Int64	The skill group the resource is linked to, derives from the Resource Skills and Resource Type Skills the resource is linked to (Resource_Skill and Resource_Type_Skill in the Scheduling Database). Foreign Key: ARC_Skill_Group		O
arc_time_zone_key	Integer	The time zone the resource is linked to. The value for this is derived by Resources.time_zone if it exists. Foreign Key: ARC_Time_Zone		O
attribute_value1	Double	The attribute value corresponding to the attribute value key 1 specified.		O
attribute_value2	Double	The attribute value corresponding to the attribute value key 2 specified.		O
attribute_value3	Double	The attribute value corresponding to the attribute value key 3 specified.		O
attribute_value4	Double	The attribute value corresponding to the attribute value key 4 specified.		O
description	String(2000)	The description of the resource, derives from any combination of columns on the Resources table. This is configurable via the parameter 'ResourceDescriptionFormat', which defaults to '{Resources.first_name} {Resources.surname} [{Resources.id}]'.		O
first_name	String(100)	The first name of the resource, derived from Resources.first_name		O
label	String(256)	A label created based on the parameter 'ArchiveStandardLabelFormat', derives from Skill.description. Defaults to {description}.		O
last_updated	Date Time	The date and time when the row was last updated.		O
last_used	Date Time	The date and time when the row was last referenced on a fact table.		O
surname	String(100)	The surname of the resource, derived from Resources.surname		O

Entity: ARC_Resource_Fact (Fact)¶

The Resource Fact is a fact table that represents a scheduling resource. A resource fact row is generated for each resource in the Scheduling Database.

The Resource Fact contains a number of columns that provide useful information and statistics that describe the scheduling resource's performance. For example, there are a number of columns providing information related to a number of activities linked to each resource and what their status is, such as:

activities scheduled
activities completed
activities completed in SLA
activities completed late

Additionally, there are a set of columns that provide statistics on how time is spent for each resource, such as:

total work time and work time today
total idle time and idle time today
total on site time and on site time today
total travel time and travel time today
etc.

The Resource Fact table is a denormalised data table about resources that can be easily filtered and grouped on a number of dimensions. The fact table contains a set of dimension columns that provide a context about the resource. Namely:

which dataset the resource is in (via ARC_Dataset)
which organisation the resource is in
etc.

This allows for the Resource Fact table to be filtered or grouped by any of these dimensions.

Additionally, the Resource Fact rows could be grouped by resource-specific dimension rows like Resource, Resource Type, Calendar, Location, Skill, Region etc.

Resource Fact data can optionally be grouped by more configurable dimensions such as resource attributes (up to 4).

Note

Many of the date times and stats on the Resource Fact are sourced from the Plan, Plan_Resource, Plan_Travel and Plan_Route tables in the Scheduling Database.

Note

Many of the stats have a corresponding 'today' statistic. This is introduced so that it's easier to report on statistics that are specific to the day the current schedule time belongs to. 'Today' refers to shifts that start by the end of the current schedule day. This means that any shifts that have overrun past midnight from the previous day do not contribute to any of the ‘today’ statistics.

Note

Many of the stats have a corresponding 'completed' statistic. This is introduced so that it's easier to report on statistics that belong to the past, i.e. have been completed before the current schedule time. For example, 'completed_break_hours' refer to the total duration of the activities of class 'BREAK' that have been completed by the current schedule time.

Warning

A shift is regarded as being in the past if the overtime end time of the shift is before the current schedule time.

Note

Statistics that depend on Activity SLA in the Scheduling Database depend on whether it is defined to be start based or not. If so, the statistic is calculated accordingly. For example, the integer field 'activities_completed_in_sla' is calculated by comparing the activity start time to the SLA end if the Activity SLA is start_based. Alternatively, if the Activity SLA is set to not be start based, the activity end time is compared to the SLA end instead. For more detail on the use of the start_based field please see Activity_SLA table in the Scheduling Schema Guide

Attribute Name	Data Type	Description	PK	M/O
arc_dataset_key	Integer	Used to filter by dataset, i.e. by dataset id via the ARC_Dataset dimension table. Foreign Key: ARC_Dataset	PK	M
arc_resource_key	Int64	Used to filter by the resource the resource snapshot is linked to, e.g. to get all resource snapshot fact rows for resource 'James'. Foreign Key: ARC_Resource	PK	M
organisation_id	Integer	The organisation the Resource Snapshot Fact belongs to. Foreign Key: ARC_Date		M
activities_completed	Integer	Number of activities completed by the resource (of status 'Complete', i.e. 70).		O
activities_completed_early	Integer	Total number of activities completed early by the resource (of status 'Complete', i.e. 70). Notes: 'Completed Early' is defined as the activities that start before their expected start, i.e. the Allocation 'activity_start' is before the 'expected_start' on the Allocation Data in the Scheduling Database, which once committed is the date and time that the resource expects to start the activity. Note that this statistic depends on whether the Activity SLA that is related to the Activity is defined to be start based or not. For more detail on this please see Activity_SLA table in the Scheduling Schema Guide.		O
activities_completed_in_sla	Integer	Total number of activities completed within the primary SLA by the resource (of status 'Complete', i.e. 70). Notes: 'Completed within the primary SLA' is defined as the activities that start before their primary SLA end, i.e. the Allocation 'activity_start' is before the 'datetime_end' on the Activity SLA or if that doesn't exist, the 'sla_jeopardy_time' on the Allocation Data in the Scheduling Database. Note that this statistic depends on whether the Activity SLA that is related to the Activity is defined to be start based or not. For more detail on this please see Activity_SLA table in the Scheduling Schema Guide.		O
activities_completed_late	Integer	Total number of activities completed late by the resource (of status 'Complete', i.e. 70). Notes: 'Completed Late' is defined as the activities that start after their expected start, i.e. the Allocation 'activity_start' is after the 'expected_start' on the Allocation Data in the Scheduling Database, which once committed is the date and time that the resource expects to start the activity. Note that this statistic depends on whether the Activity SLA that is related to the Activity is defined to be start based or not. For more detail on this please see Activity_SLA table in the Scheduling Schema Guide.		O
activities_scheduled	Integer	Total number of activities scheduled to the resource.		O
activities_scheduled_today	Integer	Total number of activities scheduled to the resource today. Notes: The value for this is specified as all the allocations of visit id 1 (i.e. the first visit of a split activity if multiple visits exist) that start (activity_start on Allocation table in Scheduling Database) on the date of the current schedule time of the snapshot.		O
arc_attribute_value_key1	Int64	An integer key, referencing a key on the ARC_Attribute_Value table. Used to filter by Attribute Value, e.g. to get all resource snapshot fact rows that have an attribute of 'cell phone'. Which attributes from the Scheduling Database (Additional_Attribute) should be reported on for resources is set up on the 'Attributes' screen. See Attributes section for more detail. Foreign Key: ARC_Attribute_Value		O
arc_attribute_value_key2	Int64	Same as arc_attribute_value_key1, providing another field for a rsource attribute to be reported on, so that resource snapshot fact rows can be grouped by a certain attribute. Foreign Key: ARC_Attribute_Value		O
arc_attribute_value_key3	Int64	Same as arc_attribute_value_key1, providing another field for a resource attribute to be reported on, so that resource snapshot fact rows can be grouped by a certain attribute. Foreign Key: ARC_Attribute_Value		O
arc_attribute_value_key4	Int64	Same as arc_attribute_value_key1, providing another field for a resource attribute to be reported on, so that resource snapshot fact rows can be grouped by a certain attribute. Foreign Key: ARC_Attribute_Value		O
arc_date_key_schedule_time	Integer	An integer key, referencing a key on the ARC_Date table. Used to filter by the date of the schedule the resource is in, e.g. to get all resource snapshot fact rows that are in the schedule yesterday or today. Foreign Key: ARC_Date		O
arc_region_group_key	Int64	An integer key, referencing a key on the ARC_Region_Group table. Used to group resource snapshot fact rows by one or more regions (a region group contains one or more regions). Foreign Key: ARC_Region_Group		O
arc_resource_type_key	Int64	An integer key, referencing a key on the ARC_Resource table. Used to filter by the resource type, e.g. to get all resource snapshot fact rows of type 'Driver'. Foreign Key: ARC_Resource_Type		O
arc_skill_group_key	Int64	An integer key, referencing a key on the ARC_Skill_Group table. Used to group resource snapshot fact rows by one or more skills (a skill group contains one or more skills). Foreign Key: ARC_Skill_Group		O
arc_time_zone_key	Integer	An integer key, referencing a key on the ARC_Time_Zone table. Used to group resource snapshot fact rows by time zone, e.g. get all resource snapshot fact rows that represent activities that are appear in time zone 'Pacific'. Foreign Key: ARC_Time_Zone		O
attribute_value1	Double	The value of the additional attribute for the resource (label_value), which corresponds to attribute_value_key1.		O
attribute_value2	Double	The value of the additional attribute for the resource (label_value), which corresponds to attribute_value_key2.		O
attribute_value3	Double	The value of the additional attribute for the resource (label_value), which corresponds to attribute_value_key3.		O
attribute_value4	Double	The value of the additional attribute for the resource (label_value), which corresponds to attribute_value_key4.		O
break_hours_today	Double	The total amount of time spent as breaks (activities of class 'BREAK') today. The value for this is mapped from the 'total_break_time' column on the Plan_Resource table in the Scheduling Database, for shifts that start on the current schedule day.		O
completed_break_hours	Double	The total amount of time spent as breaks (activities of class 'BREAK') that is in the past, i.e. before the current schedule time when the snapshot is taken. The value for this is obtained by summing up all the total break time of shifs (Plan_Route rows for this shift in the Scheduling Database) in the past. Notes: The value of 'break time' is obtained from 'total_break_time' column on Plan_Route table in the Scheduling Database.		O
completed_idle_hours	Double	The total amount of unused shift time that is in the past, i.e. before the current schedule time when the snapshot is taken. The value for this is obtained by summing up all the 'total_unutilised_time' of shifs (Plan_Route rows for this shift in the Scheduling Database) in the past. Notes: 'Idle' is defined as the total amount of shift time that does not have any travels or activities of class 'CALL' in it. The value of 'idle' is obtained from 'total_unutilised_time' column on Plan_Route table in the Scheduling Database.		O
completed_on_site_hours	Double	The total amount of time spent on-site doing activities that is in the past, i.e. before the current schedule time when the snapshot is taken. The value for this is obtained by summing up all the 'on site' time of shifts (Plan_Route rows for this shift) in the past. Notes: 'On Site' is defined as the total amount of shift time that corresponds to activities of class 'CALL'. The value of 'on site' is obtained from 'total_on_site_time' column on Plan_Route table in the Scheduling Database.		O
completed_overtime_hours	Double	The total amount of time spent in shifts' overtime that is in the past, i.e. before the current schedule time when the snapshot is taken. The value for this is obtained by summing up all the overtime of shifts (Plan_Route rows for this shift) in the past. Notes: 'Overtime' is calculated by subtracting the Plan_Route column 'shift_actual_end' from the 'shift_overtime_end' on the Plan_Route table in the Scheduling Database.		O
completed_travel_hours	Double	The total amount of time spent travelling in shifts that is in the past, i.e. before the current schedule time when the snapshot is taken. The value for this is obtained by summing up all the 'travel time' (Plan_Route rows for this shift) in the past. Notes: 'Travel time' is defined as the total travel time in shifts. The value of 'travel time' is obtained from the 'expected_travel_time' column on Plan_Travel table in the Scheduling Database for rows that have a shift start specified on them.		O
completed_work_hours	Double	The total amount of time spent 'workable' in shifts that is in the past, i.e. before the current schedule time when the snapshot is taken. The value for this is obtained by summing up all the 'workable' time in shifts (Plan_Route rows for this shift) in the past. Notes: 'Workable' is defined as the total time of shifts. The value of 'workable' is obtained by subtracting the 'shift_actual_start' or the 'shift_start_datetime' from the 'shift_overtime_end' column on Plan_Route table in the Scheduling Database.		O
expected_travel_hours	Double	The total amount of time predicted to spend travelling in shifts. The value for this is obtained by summing up all the 'expected travel time' (Plan_Travel rows). Notes: 'Expected travel time' is defined as the total extpected travel time of travels that start in the future, i.e. past the current schedule time when the snapshot is taken. The value of 'expected travel time' is obtained from the 'expected_travel_time' column on Plan_Travel table in the Scheduling Database for rows that have a shift start specified on them.		O
idle_hours_today	Double	The total amount of unused shift time today. The value for this is obtained by summing up all the 'total_unutilised_time' of shifs (Plan_Route rows for this shift in the Scheduling Database) for today. Notes: 'Idle' is defined as the total amount of shift time that does not have any travels or activities of class 'CALL' in it, for shifts that start on the current schedule day. The value of 'idle' is obtained from 'total_unutilised_time' column on Plan_Route table in the Scheduling Database.		O
last_updated	Date Time	The date and time when the row was last updated.		O
logoff_difference_hours	Double	This column records the total time that the resource has logged off differently to what was expected or planned. The value for this is derived by taking the difference between the shift_end and the shift_actual_end (from the Shift table and the Plan_Route table in the Scheduling Database respectively). Notes: The value will be positive if the resource is overall 'late' in logging off and negative if the resource is overall 'early' in logging off.		O
logon_difference_hours	Double	This column records the total time that the resource has logged on differently to what was expected or planned. The value for this is derived by taking the difference between the shift_start and the shift_actual_start (from the Shift table and the Plan_Route table in the Scheduling Database respectively). Notes: The value will be positive if the resource is overall 'late' in logging on and negative if the resource is overall 'early' in logging on.		O
on_site_hours_today	Double	The total amount of 'on site' shift time today. The value for this is obtained by summing up all the 'total_on_site_time' of shifs (Plan_Route rows for this shift in the Scheduling Database) for today. Notes: 'On Site' is defined as the total amount of shift time that corresponds to activities of class 'CALL', for shifts that start on the current schedule day. The value of 'on site' is obtained from 'total_on_site_time' column on Plan_Route table in the Scheduling Database.		O
overtime_hours_today	Double	The total amount of 'overtime' today. The value for this is obtained by summing up all the overtime of shifts (Plan_Route rows for this shift) today. Notes: 'Overtime' is calculated by subtracting the Plan_Route column 'shift_actual_end' from the 'shift_overtime_end' on the Plan_Route table in the Scheduling Database, for shifts that start on the current schedule day.		O
private_activity_hours_today	Double	The total amount of 'private time' spent in shifts today. The value for this is obtained by summing up all the 'private time' of shifts (Plan_Route rows for this shift) today. Notes: 'Private time' is obtained from 'total_private_time' column on Plan_Route table in the Scheduling Database, for shifts that start on the current schedule day.		O
total_break_hours	Double	The total amount of time spent as breaks (activities of class 'BREAK'). Notes: The value of 'total break time' is obtained from 'total_break_time' column on Plan_Resource table in the Scheduling Database.		O
total_idle_hours	Double	The total amount of unused shift time. Notes: 'Idle' is defined as the total amount of shift time that does not have any travels or activities of class 'CALL' in it. The value of 'total idle time' is obtained from 'total_unutilised_time' column on Plan_Resource table in the Scheduling Database.		O
total_on_site_hours	Double	The total amount of time spent on-site doing activities. Notes: 'On Site' is defined as the total amount of shift time that corresponds to activities of class 'CALL'. The value of 'total on site' is obtained from 'total_on_site_time' column on Plan_Resource table in the Scheduling Database.		O
total_overtime_hours	Double	The total amount of time spent in shifts' overtime. The value for this is obtained by summing up all the overtime of shifts (Plan_Route rows for this shift). Notes: 'Overtime' is calculated by subtracting the Plan_Route column 'shift_actual_end' from the 'shift_overtime_end' on the Plan_Route table in the Scheduling Database.		O
total_private_activity_hours	Double	The total amount of 'private time' spent in shifts. Notes: 'Total private time' is obtained from 'total_private_time' column on Plan_Resource table in the Scheduling Database.		O
total_travel_hours	Double	The total amount of time spent travelling in shifts. Notes: 'Total Travel time' is defined as the total travel time for the resource in shifts. The value of 'total travel time' is obtained from the 'expected_travel_time' column on Plan_Travel table in the Scheduling Database for rows that have a shift start specified on them.		O
total_work_hours	Double	The total amount of time spent 'workable' in shifts. The value for this is obtained by summing up all the 'workable' time in shifts (Plan_Route rows for this shift). Notes: 'Workable' is defined as the total time of shifts. The value of 'workable' is obtained by subtracting the 'shift_actual_start' or the 'shift_start_datetime' from the 'shift_overtime_end' column on Plan_Route table in the Scheduling Database.		O
travel_hours_today	Double	The total amount of time spent travelling in shifts today. Notes: 'Travel time today' is defined as the total travel time for the resource in shifts, for travels that start on the current schedule day. The value of 'total travel time' is obtained from the 'expected_travel_time' column on Plan_Travel table in the Scheduling Database for rows that have a shift start specified on them.		O
work_hours_today	Double	The total amount of time spent 'workable' in shifts today. The value for this is obtained by summing up all the 'workable' time in shifts (Plan_Route rows for this shift) today. Notes: 'Workable' is defined as the total time of shifts, for shifts that start on the current schedule day. The value of 'workable' is obtained by subtracting the 'shift_actual_start' or the 'shift_start_datetime' from the 'shift_overtime_end' column on Plan_Route table in the Scheduling Database.		O

Entity: ARC_Resource_Snapshot_Fact (Fact)¶

The Resource Snapshot Fact is a periodic snapshot fact table that represents a scheduling resource. Every time a snapshot occurs, a resource snapshot fact row is appended for each resource in the Resource Fact table. Its purpose is to take a snapshot of all the resources so that reports can be generated on the 'state' of the resources over time (via the snapshots).

For more information see the Resource Fact entity.

Attribute Name	Data Type	Description	PK	M/O
arc_resource_key	Int64	Used to filter by the resource the resource snapshot is linked to, e.g. to get all resource snapshot fact rows for resource 'James'. Foreign Key: ARC_Resource	PK	M
arc_snapshot_detail_key	Int64	Used to group resource snapshot fact rows by snapshot or to filter by a particular snapshot. Foreign Key: ARC_Snapshot_Detail	PK	M
arc_dataset_key	Integer	Used to filter by dataset, i.e. by dataset id via the ARC_Dataset dimension table. Foreign Key: ARC_Dataset		M
organisation_id	Integer	The organisation the Resource Snapshot Fact belongs to. Foreign Key: ARC_Date		M
activities_completed	Integer	Number of activities completed by the resource (of status 'Complete', i.e. 70).		O
activities_completed_early	Integer	Total number of activities completed early by the resource (of status 'Complete', i.e. 70). Notes: 'Completed Early' is defined as the activities that start before their expected start, i.e. the Allocation 'activity_start' is before the 'expected_start' on the Allocation Data in the Scheduling Database, which once committed is the date and time that the resource expects to start the activity. Note that this statistic depends on whether the Activity SLA that is related to the Activity is defined to be start based or not. For more detail on this please see Activity_SLA table in the Scheduling Schema Guide.		O
activities_completed_in_sla	Integer	Total number of activities completed within the primary SLA by the resource (of status 'Complete', i.e. 70). Notes: 'Completed within the primary SLA' is defined as the activities that start before their primary SLA end, i.e. the Allocation 'activity_start' is before the 'datetime_end' on the Activity SLA or if that doesn't exist, the 'sla_jeopardy_time' on the Allocation Data in the Scheduling Database. Note that this statistic depends on whether the Activity SLA that is related to the Activity is defined to be start based or not. For more detail on this please see Activity_SLA table in the Scheduling Schema Guide.		O
activities_completed_late	Integer	Total number of activities completed late by the resource (of status 'Complete', i.e. 70). Notes: 'Completed Late' is defined as the activities that start after their expected start, i.e. the Allocation 'activity_start' is after the 'expected_start' on the Allocation Data in the Scheduling Database, which once committed is the date and time that the resource expects to start the activity. Note that this statistic depends on whether the Activity SLA that is related to the Activity is defined to be start based or not. For more detail on this please see Activity_SLA table in the Scheduling Schema Guide.		O
activities_scheduled	Integer	Total number of activities scheduled to the resource.		O
activities_scheduled_today	Integer	Total number of activities scheduled to the resource today. Notes: The value for this is specified as all the allocations of visit id 1 (i.e. the first visit of a split activity if multiple visits exist) that start (activity_start on Allocation table in Scheduling Database) on the date of the current schedule time of the snapshot.		O
arc_attribute_value_key1	Int64	An integer key, referencing a key on the ARC_Attribute_Value table. Used to filter by Attribute Value, e.g. to get all resource snapshot fact rows that have an attribute of 'cell phone'. Which attributes from the Scheduling Database (Additional_Attribute) should be reported on for resources is set up on the 'Attributes' screen. See Attributes section for more detail. Foreign Key: ARC_Attribute_Value		O
arc_attribute_value_key2	Int64	Same as arc_attribute_value_key1, providing another field for a rsource attribute to be reported on, so that resource snapshot fact rows can be grouped by a certain attribute. Foreign Key: ARC_Attribute_Value		O
arc_attribute_value_key3	Int64	Same as arc_attribute_value_key1, providing another field for a resource attribute to be reported on, so that resource snapshot fact rows can be grouped by a certain attribute. Foreign Key: ARC_Attribute_Value		O
arc_attribute_value_key4	Int64	Same as arc_attribute_value_key1, providing another field for a resource attribute to be reported on, so that resource snapshot fact rows can be grouped by a certain attribute. Foreign Key: ARC_Attribute_Value		O
arc_date_key_schedule_time	Integer	An integer key, referencing a key on the ARC_Date table. Used to filter by the date of the schedule the resource is in, e.g. to get all resource snapshot fact rows that are in the schedule yesterday or today. Foreign Key: ARC_Date		O
arc_region_group_key	Int64	An integer key, referencing a key on the ARC_Region_Group table. Used to group resource snapshot fact rows by one or more regions (a region group contains one or more regions). Foreign Key: ARC_Region_Group		O
arc_resource_type_key	Int64	An integer key, referencing a key on the ARC_Resource table. Used to filter by the resource type, e.g. to get all resource snapshot fact rows of type 'Driver'. Foreign Key: ARC_Resource_Type		O
arc_skill_group_key	Int64	An integer key, referencing a key on the ARC_Skill_Group table. Used to group resource snapshot fact rows by one or more skills (a skill group contains one or more skills). Foreign Key: ARC_Skill_Group		O
arc_time_zone_key	Integer	An integer key, referencing a key on the ARC_Time_Zone table. Used to group resource snapshot fact rows by time zone, e.g. get all resource snapshot fact rows that represent activities that are appear in time zone 'Pacific'. Foreign Key: ARC_Time_Zone		O
attribute_value1	Double	The value of the additional attribute for the resource (label_value), which corresponds to attribute_value_key1.		O
attribute_value2	Double	The value of the additional attribute for the resource (label_value), which corresponds to attribute_value_key2.		O
attribute_value3	Double	The value of the additional attribute for the resource (label_value), which corresponds to attribute_value_key3.		O
attribute_value4	Double	The value of the additional attribute for the resource (label_value), which corresponds to attribute_value_key4.		O
break_hours_today	Double	The total amount of time spent as breaks (activities of class 'BREAK') today. The value for this is mapped from the 'total_break_time' column on the Plan_Resource table in the Scheduling Database, for shifts that start on the current schedule day.		O
completed_break_hours	Double	The total amount of time spent as breaks (activities of class 'BREAK') that is in the past, i.e. before the current schedule time when the snapshot is taken. The value for this is obtained by summing up all the total break time of shifs (Plan_Route rows for this shift in the Scheduling Database) in the past. Notes: The value of 'break time' is obtained from 'total_break_time' column on Plan_Route table in the Scheduling Database.		O
completed_idle_hours	Double	The total amount of unused shift time that is in the past, i.e. before the current schedule time when the snapshot is taken. The value for this is obtained by summing up all the 'total_unutilised_time' of shifs (Plan_Route rows for this shift in the Scheduling Database) in the past. Notes: 'Idle' is defined as the total amount of shift time that does not have any travels or activities of class 'CALL' in it. The value of 'idle' is obtained from 'total_unutilised_time' column on Plan_Route table in the Scheduling Database.		O
completed_on_site_hours	Double	The total amount of time spent on-site doing activities that is in the past, i.e. before the current schedule time when the snapshot is taken. The value for this is obtained by summing up all the 'on site' time of shifts (Plan_Route rows for this shift) in the past. Notes: 'On Site' is defined as the total amount of shift time that corresponds to activities of class 'CALL'. The value of 'on site' is obtained from 'total_on_site_time' column on Plan_Route table in the Scheduling Database.		O
completed_overtime_hours	Double	The total amount of time spent in shifts' overtime that is in the past, i.e. before the current schedule time when the snapshot is taken. The value for this is obtained by summing up all the overtime of shifts (Plan_Route rows for this shift) in the past. Notes: 'Overtime' is calculated by subtracting the Plan_Route column 'shift_actual_end' from the 'shift_overtime_end' on the Plan_Route table in the Scheduling Database.		O
completed_travel_hours	Double	The total amount of time spent travelling in shifts that is in the past, i.e. before the current schedule time when the snapshot is taken. The value for this is obtained by summing up all the 'travel time' (Plan_Route rows for this shift) in the past. Notes: 'Travel time' is defined as the total travel time in shifts. The value of 'travel time' is obtained from the 'expected_travel_time' column on Plan_Travel table in the Scheduling Database for rows that have a shift start specified on them.		O
completed_work_hours	Double	The total amount of time spent 'workable' in shifts that is in the past, i.e. before the current schedule time when the snapshot is taken. The value for this is obtained by summing up all the 'workable' time in shifts (Plan_Route rows for this shift) in the past. Notes: 'Workable' is defined as the total time of shifts. The value of 'workable' is obtained by subtracting the 'shift_actual_start' or the 'shift_start_datetime' from the 'shift_overtime_end' column on Plan_Route table in the Scheduling Database.		O
expected_travel_hours	Double	The total amount of time predicted to spend travelling in shifts. The value for this is obtained by summing up all the 'expected travel time' (Plan_Travel rows). Notes: 'Expected travel time' is defined as the total extpected travel time of travels that start in the future, i.e. past the current schedule time when the snapshot is taken. The value of 'expected travel time' is obtained from the 'expected_travel_time' column on Plan_Travel table in the Scheduling Database for rows that have a shift start specified on them.		O
idle_hours_today	Double	The total amount of unused shift time today. The value for this is obtained by summing up all the 'total_unutilised_time' of shifs (Plan_Route rows for this shift in the Scheduling Database) for today. Notes: 'Idle' is defined as the total amount of shift time that does not have any travels or activities of class 'CALL' in it, for shifts that start on the current schedule day. The value of 'idle' is obtained from 'total_unutilised_time' column on Plan_Route table in the Scheduling Database.		O
logoff_difference_hours	Double	This column records the total time that the resource has logged off differently to what was expected or planned. The value for this is derived by taking the difference between the shift_end and the shift_actual_end (from the Shift table and the Plan_Route table in the Scheduling Database respectively). Notes: The value will be positive if the resource is overall 'late' in logging off and negative if the resource is overall 'early' in logging off.		O
logon_difference_hours	Double	This column records the total time that the resource has logged on differently to what was expected or planned. The value for this is derived by taking the difference between the shift_start and the shift_actual_start (from the Shift table and the Plan_Route table in the Scheduling Database respectively). Notes: The value will be positive if the resource is overall 'late' in logging on and negative if the resource is overall 'early' in logging on.		O
on_site_hours_today	Double	The total amount of 'on site' shift time today. The value for this is obtained by summing up all the 'total_on_site_time' of shifs (Plan_Route rows for this shift in the Scheduling Database) for today. Notes: 'On Site' is defined as the total amount of shift time that corresponds to activities of class 'CALL', for shifts that start on the current schedule day. The value of 'on site' is obtained from 'total_on_site_time' column on Plan_Route table in the Scheduling Database.		O
overtime_hours_today	Double	The total amount of 'overtime' today. The value for this is obtained by summing up all the overtime of shifts (Plan_Route rows for this shift) today. Notes: 'Overtime' is calculated by subtracting the Plan_Route column 'shift_actual_end' from the 'shift_overtime_end' on the Plan_Route table in the Scheduling Database, for shifts that start on the current schedule day.		O
private_activity_hours_today	Double	The total amount of 'private time' spent in shifts today. The value for this is obtained by summing up all the 'private time' of shifts (Plan_Route rows for this shift) today. Notes: 'Private time' is obtained from 'total_private_time' column on Plan_Route table in the Scheduling Database, for shifts that start on the current schedule day.		O
total_break_hours	Double	The total amount of time spent as breaks (activities of class 'BREAK'). Notes: The value of 'total break time' is obtained from 'total_break_time' column on Plan_Resource table in the Scheduling Database.		O
total_idle_hours	Double	The total amount of unused shift time. Notes: 'Idle' is defined as the total amount of shift time that does not have any travels or activities of class 'CALL' in it. The value of 'total idle time' is obtained from 'total_unutilised_time' column on Plan_Resource table in the Scheduling Database.		O
total_on_site_hours	Double	The total amount of time spent on-site doing activities. Notes: 'On Site' is defined as the total amount of shift time that corresponds to activities of class 'CALL'. The value of 'total on site' is obtained from 'total_on_site_time' column on Plan_Resource table in the Scheduling Database.		O
total_overtime_hours	Double	The total amount of time spent in shifts' overtime. The value for this is obtained by summing up all the overtime of shifts (Plan_Route rows for this shift). Notes: 'Overtime' is calculated by subtracting the Plan_Route column 'shift_actual_end' from the 'shift_overtime_end' on the Plan_Route table in the Scheduling Database.		O
total_private_activity_hours	Double	The total amount of 'private time' spent in shifts. Notes: 'Total private time' is obtained from 'total_private_time' column on Plan_Resource table in the Scheduling Database.		O
total_travel_hours	Double	The total amount of time spent travelling in shifts. Notes: 'Total Travel time' is defined as the total travel time for the resource in shifts. The value of 'total travel time' is obtained from the 'expected_travel_time' column on Plan_Travel table in the Scheduling Database for rows that have a shift start specified on them.		O
total_work_hours	Double	The total amount of time spent 'workable' in shifts. The value for this is obtained by summing up all the 'workable' time in shifts (Plan_Route rows for this shift). Notes: 'Workable' is defined as the total time of shifts. The value of 'workable' is obtained by subtracting the 'shift_actual_start' or the 'shift_start_datetime' from the 'shift_overtime_end' column on Plan_Route table in the Scheduling Database.		O
travel_hours_today	Double	The total amount of time spent travelling in shifts today. Notes: 'Travel time today' is defined as the total travel time for the resource in shifts, for travels that start on the current schedule day. The value of 'total travel time' is obtained from the 'expected_travel_time' column on Plan_Travel table in the Scheduling Database for rows that have a shift start specified on them.		O
work_hours_today	Double	The total amount of time spent 'workable' in shifts today. The value for this is obtained by summing up all the 'workable' time in shifts (Plan_Route rows for this shift) today. Notes: 'Workable' is defined as the total time of shifts, for shifts that start on the current schedule day. The value of 'workable' is obtained by subtracting the 'shift_actual_start' or the 'shift_start_datetime' from the 'shift_overtime_end' column on Plan_Route table in the Scheduling Database.		O

Entity: ARC_Resource_Type (Dimension)¶

A dimension that derives from Resource Type in the Scheduling Database (Resource Type table).

Attribute Name	Data Type	Description	PK	M/O
key	Int64	A unique integer key for the resource type (auto-increment).	PK	M
id	String(32)	A unique id for the resource type. Derives from the id on the Resource_Type table.		M
organisation_id	Integer	The organisation the row belongs to.		M
arc_dataset_key	Integer	The dataset the row belongs to. Foreign Key: ARC_Dataset		O
description	String(2000)	A description for the resource type.		O
label	String(256)	A label created based on the parameter 'ArchiveStandardLabelFormat', derives from Resource_Type.description. Defaults to {description}.		O
last_updated	Date Time	The date and time when the row was last updated.		O
last_used	Date Time	The date and time when the row was last referenced on a fact table.		O

Entity: ARC_Skill (Dimension)¶

A dimension that derives from Skill in the Scheduling Database (_Skill table).

Examples¶

<ARC_Skill>
    <key>1</key>
    <id>AIR</id>
    <label>Air Conditioning</label>
    <description>Air Conditioning</description>
    <skill_type_id>Tech</skill_type_id>
    <skill_type_description>Technical skill</skill_type_description>
    <skill_type_label>Technical skill</skill_type_label>
    <arc_dataset_key>1</arc_dataset_key>
    <last_updated>2008-09-22T08:00:00+01:00</last_updated>
    <organisation_id>1</organisation_id>
</ARC_Skill>

<ARC_Skill>
    <key>2</key>
    <id>CRB</id>
    <label>CRB cleared</label>
    <description>CRB cleared</description>
    <skill_type_id>Non Tech</skill_type_id>
    <skill_type_description>Non technical skill</skill_type_description>
    <skill_type_label>Non technical skill</skill_type_label>
    <arc_dataset_key>1</arc_dataset_key>
    <last_updated>2008-09-22T08:00:00+01:00</last_updated>
    <organisation_id>1</organisation_id>
</ARC_Skill>

Attribute Name	Data Type	Description	PK	M/O
key	Int64	A unique integer key for the skill (auto-increment).	PK	M
id	String(32)	A unique id for the skill. Derives from the id on the Skill table.		M
organisation_id	Integer	The organisation the row belongs to.		M
arc_dataset_key	Integer	The dataset the row belongs to. Foreign Key: ARC_Dataset		O
description	String(2000)	A description for the skill.		O
label	String(256)	A label created based on the parameter 'ArchiveStandardLabelFormat', derives from Skill.description. Defaults to {description}.		O
last_updated	Date Time	The date and time when the row was last updated.		O
last_used	Date Time	The date and time when the row was last referenced on a fact table.		O
skill_type_description	String(2000)	The skill description, derives from Skill.description		O
skill_type_id	String(32)	The skill type, derives from Skill.skill_type_id		O
skill_type_label	String(256)	A label created based on the parameter 'ArchiveStandardLabelFormat', derives from Skill_Type.description. Defaults to {description}.		O

Entity: ARC_Skill_Group (Dimension)¶

Each activity and/or resource may be linked to any number of skills. A skill group dimension is created for each combination used, with the individual skill contained in the group specified in the ARC_Skill_Group_Item bridge table.

Examples¶

<ARC_Skill_Group>
    <key>1</key>
    <number_of_skills>1</number_of_skills>
    <arc_skill_key_single>1</arc_skill_key_single>
    <arc_dataset_key>1</arc_dataset_key>
    <organisation_id>1</organisation_id>
</ARC_Skill_Group>
<ARC_Skill_Group>
    <key>2</key>
    <number_of_skills>1</number_of_skills>
    <arc_skill_key_single>2</arc_skill_key_single>
    <arc_dataset_key>1</arc_dataset_key>
    <organisation_id>1</organisation_id>
</ARC_Skill_Group>

Attribute Name	Data Type	Description	PK	M/O
key	Int64	A unique integer key for the skill group (auto-increment).	PK	M
organisation_id	Integer	The organisation the row belongs to.		M
arc_dataset_key	Integer	The dataset the row belongs to. Foreign Key: ARC_Dataset		O
arc_skill_key_single	Int64	If skills is 1, this column will be set with the value of the single skill this skill group contains.		O
last_updated	Date Time	The date and time when the row was last updated.		O
last_used	Date Time	The date and time when the row was last referenced on a fact table.		O
number_of_skills	Integer	The number of skills the skill group contains.		O
skill_set_description	String(256)	The description for the skill set this skill is in, derived from the description column on Skill_Set in Scheduling Database.		O
skill_set_id	String(32)	The skill set the skill is in, derived from the id column on Skill_Set in Scheduling Database.		O
skill_set_label	String(256)	The skill set the skill is in, derived from the id column on Skill_Set in Scheduling Database.		O

Entity: ARC_Skill_Group_Item (Dimension)¶

A dimension 'bridge' table used to store the skill group individual skills belong to. This is introduced to make queries easier. See section 'Views, Functions and Example Queries'.

Attribute Name	Data Type	Description	PK	M/O
arc_skill_group_key	Int64	An integer key for the skill group this skill group item refers to. Foreign Key: ARC_Skill_Group	PK	M
arc_skill_key	Int64	An integer key for the individual skill. Foreign Key: ARC_Skill	PK	M
organisation_id	Integer	The organisation the row belongs to.		M

Entity: ARC_Skill_Hierarchy (Dimension)¶

A dimension table used to define the hierarchy between skills. Essentially, the skill hierarchy is denormalised into the ARC_Skill_Hierarchy 'bridge' table.

Examples¶

<ARC_Skill_Hierarchy>
    <arc_skill_key_parent>1</arc_skill_key_parent>
    <arc_skill_key_child>1</arc_skill_key_child>
    <levels_between>0</levels_between>
    <is_top>true</is_top>
    <is_bottom>true</is_bottom>
    <arc_dataset_key>1</arc_dataset_key>
    <organisation_id>1</organisation_id>
</ARC_Skill_Hierarchy>
<ARC_Skill_Hierarchy>
    <arc_skill_key_parent>2</arc_skill_key_parent>
    <arc_skill_key_child>2</arc_skill_key_child>
    <levels_between>0</levels_between>
    <is_top>true</is_top>
    <is_bottom>true</is_bottom>
    <arc_dataset_key>1</arc_dataset_key>
    <organisation_id>1</organisation_id>
</ARC_Skill_Hierarchy>

Attribute Name	Data Type	Description	PK	M/O
arc_skill_key_child	Int64	An integer key for the child skill.	PK	M
arc_skill_key_parent	Int64	An integer key for the parent skill. Foreign Key: ARC_Skill	PK	M
arc_dataset_key	Integer	The dataset the row belongs to.		M
organisation_id	Integer	The organisation the row belongs to.		M
is_bottom	Boolean	Defines whether this hierarchy pair is at the bottom of the entire skill hierarchy or not.		O
is_top	Boolean	Defines whether this hierarchy pair is at the bottom of the entire skill hierarchy or not.		O
levels_between	Integer	The hierarchy levels between the child and parent skill.		O

Entity: ARC_Snapshot_Detail (System)¶

A dimension table to record useful information about the each snapshot.

Examples¶

<ARC_Snapshot_Detail>
    <key>1</key>
    <schedule_datetime>2008-09-22T08:00:00+01:00</schedule_datetime>
    <timestamp>2016-09-13T19:01:33+01:00</timestamp>
    <organisation_id>1</organisation_id>
    <dataset_id>Default</dataset_id>
    <arc_dataset_key>1</arc_dataset_key>
    <input_reference_internal_id>13</input_reference_internal_id>
    <input_reference_internal_id_load>13</input_reference_internal_id_load>
    <plan_id>337</plan_id>
    <language_id>en-GB</language_id>
    <version>5.8.0.22</version>
    <arc_snapshot_id>1</arc_snapshot_id>
    <day_sequence>0</day_sequence>
    <load_sequence>0</load_sequence>
    <timestamp_utc>2016-09-13T18:01:33+01:00</timestamp_utc>
</ARC_Snapshot_Detail>

Attribute Name	Data Type	Description	PK	M/O
key	Int64	A unique integer key for the snapshot detail (auto-increment).	PK	M
organisation_id	Integer	The organisation the row belongs to.		M
arc_dataset_key	Integer	The dataset the row belongs to.		O
arc_snapshot_id	Int64	The snapshot id the row corresponds to.		O
dataset_id	String(32)	The dataset id the snapshot is for.		O
day_sequence	Integer	The day sequence of the snapshot. The first time a snapshot is taken the day_sequence is 0.		O
input_reference_internal_id	Int64	The input_reference_internal_id the snapshot data relates to. This is obtained from from the Plan table.		O
input_reference_internal_id_load	Int64	The last LOAD input_reference_internal_id the snapshot scheduling data relates to.		O
language_id	String(32)	The language of the snapshot data.		O
load_sequence	Integer	The load sequence of the snapshot. The first time a snapshot is taken the load_sequence is 0 and the value of the load_sequence will be incremented by 1 each time a snapshot is taken. Until the next snapshot for a LOAD occurs, in which case the load_sequence is reset to 1.		O
plan_id	Int64	The plan id the snapshot data relates to.		O
schedule_datetime	Date Time	The schedule date and time of the snapshot data.		O
timestamp	Date Time	The time the snapshot was taken.		O
timestamp_utc	Date Time	The same as timestamp, in utc.		O
version	String(32)	The version of the software installed in the system when the snapshot was taken.		O

Entity: ARC_Time_Zone (Dimension)¶

A static dimension table that stores all possible time zones and allows to filter various facts by specific time zones, e.g. America/New_York. The table includes columns for both IANA time zone id and Windows time zone id. The 'id' column is set to the IANA id where this is available.

Note that there are often multiple IANA time zones that correspond to the same Windows time zone. In this case the Windows time zone will be shown against the IANA time zone that it is mapped to by default.

Attribute Name	Data Type	Description	PK	M/O
key	Integer	A unique integer key for the time zone.	PK	M
id	String(32)	A unique id for a time zone code (this will usually match the IANA id).		M
label	String(256)	The label used for this time zone row.		M
organisation_id	Integer	The organisation the row belongs to.		M
iana_id	String(32)	The IANA id for this time zone code.		O
windows_id	String(32)	The Windows id for this time zone code.		O

Entity: ARC_Travel_Fact (Fact)¶

The Travel Snapshot Fact is a transaction fact table representing a scheduling travel to an activity or a resource's home or end locaiton. Every time a snapshot of the Scheduling Database occurs, a travel row is appended for each travel in the Scheduling Database that doesn't already exist in the Archive Database. Its purpose is to keep a record of all the travels used in the schedule.

The Travel Fact contains a number of columns that provide useful information and statistics for travels. For example, there a number of columns providing information such as:

start and end time
expected and actual travel time
travel distance

The fact table contains a set of dimension columns that provide a context about the travel. Namely:

which dataset the travel is in (via ARC_Dataset)
which organisation the travel is in
which snapshot a row relates to (via ARC_Snapshot_Detail)
etc.

This allows for the Travel Fact table to be filtered or grouped by any of these dimensions.

Additionally, the Travel Fact rows could be grouped by rows like Resource, Location, Skill, Region etc.

Travel Fact data can optionally be grouped by more configurable dimensions such as resource attributes (up to 4).

Note

Many of the date times and statistics on the Travel Fact are sourced from the Plan_Travel tables in the Scheduling Database.

Attribute Name	Data Type	Description	PK	M/O
arc_dataset_key	Integer	Used to filter by dataset, i.e. by dataset id via the ARC_Dataset dimension table. Foreign Key: ARC_Dataset	PK	M
id	Int64	Unique integer id for each travel (auto-increment).	PK	M
arc_date_key_schedule_time	Integer	Used to filter by the date of the schedule the travel is in, e.g. to get all travel fact rows that are in the schedule yesterday or today. Foreign Key: ARC_Date		M
arc_resource_key	Int64	Used to filter by the resource the travel fact is linked to, e.g. to get all travel fact rows for resource 'James'. Foreign Key: ARC_Resource		M
distance_metres	Double	The distance between start and end location in metres. Notes: This is obtained from the 'distance_metres' in Plan_Travel in the Scheduling Database.		M
end_time	Date Time	The end time of the travel. Notes: This is obtained from the 'end_time' on Plan_Travel in the Scheduling Database.		M
end_time_utc	Date Time	Same as end_time, stored in utc.		M
organisation_id	Integer	The organisation the Travel Fact belongs to. Foreign Key: ARC_Date		M
start_time	Date Time	The start time of the travel. Notes: This is obtained from the 'start_time' on Plan_Travel in the Scheduling Database.		M
start_time_utc	Date Time	Same as start_time, stored in utc.		M
actual_minutes	Double	The actual duration of the travel in minutes. Notes: This is obtained by subtracting 'start_time' from 'end_time' on Plan_Travel in the Scheduling Database.		O
arc_activity_name_key_next	Int64	An integer key, referencing a key on the ARC_Activity_Name table. Refer to the next activity. Foreign Key: ARC_Activity_Name		O
arc_activity_name_key_previous	Int64	An integer key, referencing a key on the ARC_Activity_Name table. Refer to the previous activity. Foreign Key: ARC_Activity_Name		O
arc_area_key_end	Int64	An integer key, referencing a key on the ARC_Area table. Used to group by the area the travel ends in. Foreign Key: ARC_Area		O
arc_area_key_start	Int64	An integer key, referencing a key on the ARC_Area table. Used to group by the area the travel starts in. Foreign Key: ARC_Area		O
arc_date_key_travel_start	Integer	An integer key, referencing a key on the ARC_Date table. Used to group by the date the travel starts in. Foreign Key: ARC_Date		O
arc_location_key_end	Int64	An integer key, referencing a key on the ARC_Location table. Used to group by the end location of the travel. Foreign Key: ARC_Location		O
arc_location_key_start	Int64	An integer key, referencing a key on the ARC_Location table. Used to group by the start location of the travel. Foreign Key: ARC_Location		O
arc_region_group_key_end	Int64	An integer key, referencing a key on the ARC_Region_Group table. Used to group by the region group the travel ends in. Foreign Key: ARC_Region_Group		O
arc_region_group_key_start	Int64	An integer key, referencing a key on the ARC_Region_Group table. Used to group by the region group the travel starts in. Foreign Key: ARC_Region_Group		O
arc_time_zone_key	Integer	An integer key, referencing a key on the ARC_Time_Zone table. Used to group travel fact rows by time zone, e.g. get all travel fact rows that take place in time zone 'Pacific'. Foreign Key: ARC_Time_Zone		O
arc_vehicle_key	Int64	An integer key, referencing a key on the ARC_Vehicle table. Used to group by the vehicle used during the shift. Foreign Key: ARC_Vehicle		O
arc_vehicle_model_key	Int64	An integer key, referencing a key on the ARC_Vehicle_Model table. Used to group by the vehicle model used during the shift. Foreign Key: ARC_Vehicle_Model		O
expected_minutes	Double	The expected duration of the travel in minutes. Notes: This is obtained from 'expected_travel_time' on Plan_Travel in the Scheduling Database. This is not always different to the 'actual_minutes'.		O
last_updated	Date Time	The date and time when the row was last updated.		O
visit_id_next	Integer	An integer key. Refer to the visit ID of the next activity.		O
visit_id_previous	Integer	An integer key. Refer to the visit ID of the previous activity.		O

Entity: ARC_Vehicle (Dimension)¶

Defines a vehicle which may be used by a resource during a shift.

Attribute Name	Data Type	Description	PK	M/O
key	Int64	A unique integer key for the vehicle (auto-increment)	PK	M
id	String(32)	A unique id for the vehicle		M
organisation_id	Integer	The organisation the row belongs to.		M
arc_dataset_key	Integer	The dataset the row belongs to. Foreign Key: ARC_Dataset		O
arc_vehicle_model_key	Int64	Model id of the vehicle Foreign Key: ARC_Vehicle_Model		O
co2_emission_rate	Double	Carbon dioxide emission rate of the vehicle per km		O
description	String(2000)	Provides description of the vehicle		O
efficiency_rate	Double	Efficiency rate of the vehicle		O
last_updated	Date Time	The date and time when the row was last updated.		O
last_used	Date Time	The date and time when the row was last referenced on a fact table.		O
manufacture_date	Date Time	Manufacture date of the vehicle		O
maximum_range_km	Double	Distance in Km a vehicle can travel on a full tank or on a single charge		O
n2o_emission_rate	Double	Nitrous Oxide emission rate of the vehicle per km		O
registration_code	String(64)	Code where vehicle was registered		O

Entity: ARC_Vehicle_Class (Dimension)¶

Defines a class assigned to each vehicle.

Attribute Name	Data Type	Description	PK	M/O
key	Int64	A unique integer key for the vehicle class(auto-increment)	PK	M
id	String(32)	A unique id for the vehicle class		M
organisation_id	Integer	The organisation the row belongs to.		M
arc_dataset_key	Integer	The dataset the row belongs to. Foreign Key: ARC_Dataset		O
description	String(2000)	Description of the class assigned to each vehicle		O
last_updated	Date Time	The date and time when the row was last updated.		O
last_used	Date Time	The date and time when the row was last referenced on a fact table.		O

Entity: ARC_Vehicle_Model (Dimension)¶

Defines details of a vehicle model.

Attribute Name	Data Type	Description	PK	M/O
key	Int64	A unique integer key for the vehicle model (auto-increment)	PK	M
id	String(32)	A unique id for the vehicle model		M
organisation_id	Integer	The organisation the row belongs to.		M
arc_dataset_key	Integer	The dataset the row belongs to. Foreign Key: ARC_Dataset		O
arc_fuel_type_key	Int64	Type of fuel used by the vehicle Foreign Key: ARC_Fuel_Type		O
arc_vehicle_class_key	Int64	Defines category of the vehicle Foreign Key: ARC_Vehicle_Class		O
arc_vehicle_ownership_type_key	Int64	Type of vehicle ownership Foreign Key: ARC_Vehicle_Ownership_Type		O
axel_count	Integer	Count of axels in the vehicle		O
co2_emission_rate	Double	Carbon dioxide emission rate of the vehicle per km		O
description	String(2000)	Provides description of the vehicle model		O
efficiency_rate	Double	Efficiency rate of the vehicle		O
height_metres	Double	Height of the vehicle in meters		O
last_updated	Date Time	The date and time when the row was last updated.		O
last_used	Date Time	The date and time when the row was last referenced on a fact table.		O
length_metres	Double	Length of the vehicle in meters		O
make	String(100)	Defines the brand of the vehicle		O
maximum_range_km	Double	Distance in Km a vehicle can travel on a full tank or on a single charge		O
model	String(100)	Defines the model of the vehicle		O
n2o_emission_rate	Double	Nitrous Oxide emission rate of the vehicle per km		O
weight_kg	Double	Weight of the vehicle in kgs		O
wheel_count	Integer	Count of wheels in the vehicle		O
width_metres	Double	Width of the vehicle in meters		O

Entity: ARC_Vehicle_Ownership_Type (Dimension)¶

Defines ownership type of the vehicle.

Attribute Name	Data Type	Description	PK	M/O
key	Int64	A unique integer key for the vehicle ownership type (auto-increment)	PK	M
id	String(32)	A unique id for the vehicle ownership type		M
organisation_id	Integer	The organisation the row belongs to.		M
arc_dataset_key	Integer	The dataset the row belongs to. Foreign Key: ARC_Dataset		O
description	String(2000)	Description of ownership type of the vehicle		O
last_updated	Date Time	The date and time when the row was last updated.		O
last_used	Date Time	The date and time when the row was last referenced on a fact table.		O