Reporting¶

Reporting consists of two main parts:

• Reporting Data Generator — this is part of the Schedule Archiving Service. Its function is to wait for Scheduling snapshots to occur, process and optimise the snapshot data and store it in the Reporting Tables.
• Reporting Tables — is a set of tables that contain data that are derived from scheduling data snapshots. The purpose of these tables is to present the data in a way that may be easily analysed and queried on. The data is structured in an OLAP (hyper)cube, where each separate way of breaking down the data is represented as a different dimension. The tables are structured in a 'star schema', i.e. there is a set of 'fact' tables and a set of 'dimension' tables linked to each fact table.

As an example, Activity data may be broken down into several dimensions, such as:

• activity type
• time of day scheduled
• whether it missed its SLA or not
• the resource that undertook the activity, etc.

Each of these are a logical dimension in a hypercube. By making each of these a column on an activity 'fact' table (i.e. denormalised data about activities), the data can be easily filtered and grouped on any of these dimensions.

Fact Tables¶

There are 3 core fact table types:

• periodic snapshot fact, where data is simply appended to with the details of the current snapshot.
• accumulating snapshot fact, which provides a 'history' and 'state' of the data the fact is representing; in these type of facts, each existing record gets updated, rather than appended for each snapshot. New records are appended only.
• transaction fact, where records get appended if they don't exist only.
• instant fact, which provides a view of the current state of the data; in these type of facts, each existing record gets updated. Old records are removed.

The fact tables included in the archive database:

• ARC_Activity — an accumulating fact table providing the details of the lifecycle of each activity. It records various significant dates related to the activity, the final state of the activity, and expected and actual durations. The data can be filtered in various ways via links to dimension tables. Only CALL class activities are recorded.
• ARC_Resource — an instant fact table whose data represents the current state of a resource.
• ARC_Resource_Snapshot — a periodic snapshot fact table whose data represents the current state of ARC_Resource at the time a snapshot was taken.
• ARC_Allocation — an instant fact table whose data represents the current state of each allocation. This is activity-centric, so the linked dimension tables (regions, skills, etc.) are all based on the activity data. However a user wishing to filter on resource based data could do this via the ARC_Resource dimension table.
• ARC_Allocation_Snapshot — a periodic snapshot fact table whose data represents the current state of ARC_Allocation at the time a snapshot was taken.
• ARC_Plan — an instant fact table whose data represents the current overall state of the plan.
• ARC_Plan_Snapshot — a periodic snapshot fact table whose data represents the current state of ARC_Plan at the time a snapshot was taken. Note that the only filtering associated with this table is by day or snapshot. All resource and/or activity based filters should use the resource or allocation snapshot tables.
• ARC_Travel — a transaction fact table which records details of each completed journey made by each resource in the schedule along with the information on the vehicle which the resource was using (if known).

Dimension Tables¶

There are three basic types of dimension tables:

• schedule dimensions, where data is derived directly from the schedule data
• static dimensions, containing static lookup data
• bridge dimensions, used for skills and regions to cope with the many-to-many relationships and hierarchies that occur here

Continuous Reporting¶

The Schedule Archiving Service will continuously update the reporting dimension and fact data as the scheduling datasets are updated. By default the reporting data is updated every 5 minutes (configurable by the CheckForChangesSeconds system parameter).

OData APIs¶

The data in the fact and dimension tables is accessible by the reporting OData APIs in the RESTful Gateway. Open Data Protocol (OData) is a standard that allows accessing data via REST APIs. For an introduction to OData see https://www.odata.org/getting-started/basic-tutorial/. These APIs can be called directly or linked up with external tools (e.g. Power BI Desktop) to generate reports.

Analysis Tables¶

The following analysis tables are included in the archive database:

• ARC_Activity_Duration_Estimate — an analysis of activity durations grouped by a combination of activity type, contract, customer or location. Includes an activity duration estimate with confidence level. See the Predictive Durations section for more information.

Views¶

A number of Views have been created to assist in querying the fact tables efficiently and easily. Namely, a view for each fact table is added by default in the Archive Database:

• V_ARC_Activity_Fact — a view derived from the ARC_Activity_Fact table. Its purpose is to join dimension tables to the fact table, e.g. selecting the resource id and first name from the Resource Dimension table (using the arc_resource_key).
• V_ARC_Allocation_Snapshot_Fact — a view derived from the ARC_Allocation_Snapshot_Fact table. Its purpose is to join dimension tables to the fact table, e.g. selecting the activity id from the Activity Name Dimension table (using the arc_activity_name_key).
• V_ARC_Plan_Snapshot_Fact — a view derived from the ARC_Plan_Snapshot_Fact table. Its purpose is to join dimension tables to the fact table, e.g. selecting the time zone from the Time Zone Dimension table (using the arc_time_zone_key).
• V_ARC_Resource_Snapshot_Fact — a view derived from the ARC_Resource_Snapshot_Fact table. Its purpose is to join dimension tables to the fact table, e.g. selecting the skill and region information from the Region and Skill related Dimension tables (using the arc_region_group_key and/or arc_skill_group_key).
• V_ARC_Travel_Fact — a view derived from the ARC_Travel_Fact table. Its purpose is to join dimension tables to the fact table, e.g. selecting the area information from the Area Dimension tables (using the arc_area_key_start and/or arc_area_key_end).

SELECT DISTINCT [arc_region_group_key]
  FROM [ARC_Region_Group_Item]
  WHERE [arc_region_key] IN
    (SELECT [arc_region_key_child] FROM [ARC_Region_Hierarchy]
      WHERE [arc_region_key_parent] =
        (SELECT [key] FROM [ARC_Region_Key]
          WHERE [id] = @inRegionId
          AND [organisation_id] = @inOrganisationId
          AND [dataset_id] = @inDatasetId))

SELECT DISTINCT [arc_skill_group_key]
  FROM [ARC_Skill_Group_Item]
  WHERE [arc_skill_key] IN
    (SELECT [arc_skill_key_child] FROM [ARC_Skill_Hierarchy]
      WHERE [arc_skill_key_parent] =
        (SELECT [key] FROM [ARC_Skill_Key]
          WHERE [id] = @inSkillId
          AND [organisation_id] = @inOrganisationId
          AND [dataset_id] = @inDatasetId))

SELECT DISTINCT "ARC_REGION_GROUP_KEY"
  FROM "ARC_REGION_GROUP_ITEM"
  WHERE "ARC_REGION_KEY" IN
    (SELECT "ARC_REGION_KEY_CHILD" FROM "ARC_REGION_HIERARCHY"
      WHERE "ARC_REGION_KEY_PARENT" =
        (SELECT "KEY" FROM "ARC_REGION_KEY"
          WHERE "ID" = inRegionId
          AND "ORGANISATION_ID" = inOrganisationId
          AND "DATASET_ID" = inDatasetId));

SELECT DISTINCT "ARC_SKILL_GROUP_KEY"
  FROM "ARC_SKILL_GROUP_ITEM"
  WHERE "ARC_SKILL_KEY" IN
    (SELECT "ARC_SKILL_KEY_CHILD" FROM "ARC_SKILL_HIERARCHY"
      WHERE "ARC_SKILL_KEY_PARENT" =
        (SELECT "KEY" FROM "ARC_SKILL_KEY"
          WHERE "ID" = inSkillId
          AND "ORGANISATION_ID" = inOrganisationId
          AND "DATASET_ID" = inDatasetId));

Vehicle Usage Reporting¶

The vehicle information stored in ARC_Travel fact table may be broken down into several dimensions using the arc_vehicle_key and arc_vehicle_model_key to generate a vehicle usage report, such as:

• Total distance travelled using a vehicle of a particular fuel type
• Vehicle models used by resource during the shift
• Amount of CO2 emissions made per vehicle model
• How much work carried out using electric vehicles vs petrol, etc

Example Queries¶

An example for regions would be that a user would like to find out which activities are in a particular region. 
To do this the user would have to write the following query:

In sql:

SELECT DISTINCT arc_region_group_key from ARC_Region_Group_Item
WHERE arc_region_key IN
    (SELECT arc_region_key_child FROM ARC_Region_Hierarchy
        WHERE arc_region_key_parent =
            (SELECT [key] FROM ARC_Region_Key
            WHERE
                [id] = 'England'
                AND
                [organisation_id] = 1
                AND
                [dataset_id] = 'Default'))

However, using the F_RegionGroupsForRegionId the query becomes: 

SELECT * FROM ARC_ACTIVITY_FACT AS ActivityFactRows
INNER JOIN F_RegionGroupsForRegionId('England', 'Default', 1)
as RegionGroupsForParent
ON RegionGroupsForParent.arc_region_group_key=ActivityFactRows.arc_region_group_key


In Oracle: 

SELECT DISTINCT "ARC_REGION_GROUP_KEY"
    BULK COLLECT INTO tResult
        FROM "ARC_REGION_GROUP_ITEM"
            WHERE "ARC_REGION_KEY" IN
            (SELECT "ARC_REGION_KEY_CHILD" FROM "ARC_REGION_HIERARCHY"
                WHERE "ARC_REGION_KEY_PARENT" =
                (SELECT "KEY" FROM "ARC_REGION_KEY"
                    WHERE
                        "ID" = 'England'
                        AND
                        "ORGANISATION_ID" = 1
                        AND <br>
                        "DATASET_ID" = 'Default'));
RETURN tResult;

However, using the F_REGIONGROUPSFORREGIONID the query becomes:

SELECT * FROM arc_activity_fact af
join table 
(f_REGIONGROUPSFORREGIONID('England' 1, 'Default')) 
regions on 
(regions.arc_region_group_key = af.arc_region_group_key)

An example to find total distance travelled using a vehicle of a particular fuel type in SQL:

SELECT
    atf.organisation_id AS 'Organisation',
    ds.dataset_id AS 'Dataset',
    ft.id AS 'Fuel Type',
    SUM(atf.distance_metres) AS 'Total Distance Travelled'
FROM [ARC_Travel_Fact] atf 
INNER JOIN [ARC_Dataset] ds ON 
    atf.arc_dataset_key = ds.[key]
INNER JOIN [ARC_Vehicle_Model] vm ON 
    atf.arc_vehicle_model_key = vm.[key]
INNER JOIN [ARC_Fuel_Type] ft ON 
    ft.[key] = vm.[arc_fuel_type_key]
WHERE 
    atf.arc_vehicle_key is not null AND
    atf.arc_vehicle_model_key is not null
GROUP BY ft.id,
    atf.organisation_id,
    ds.dataset_id