Integration Overview

References

Supporting Documentation

• External execution tool configuration and synchronisation
• Creating a dataflow and external system sync
• Configuring Project Online project and task sync
• Installing a second project and task sync
• Create Plugin SDK steps
• Reset task sync data
• Plugin Functions

Code Base

• SenseiProjectIndependent-DataIngestionAssembly

Design Overview

High level overview of data flowing from various external systems into Altus.

Note

The Planner Integration method is unique to the other integration methods used to sync data with other external systems such as Project Online, Preject For The Web, Jiro and Azure DevOps. This document outlines the later integration types which utilises scheduled Dataflows and Plugin triggers.

The integration implementation methods outlined here utilise a number of dataflows scheduled to regularily refresh to pull the data from external systems using an OData feed into supporting entities defined in Altus. As data is imported into entities a number of Plugins within Altus are triggered to collate, compare and sync data between the external environment and Altus.

Example of a Project Online Data Sync Overview:

Integration Components

Outlined below is a description of the various components that comprise the integration solution and how they work together.

PowerApps - Dataflows

The sync process is initiated by utilising 2 dataflows:

• Project Online - Project Sync - Run first before Task Sync
• Project Online - Task Sync (2 Types - Last Modified Data or Hashing)

Both flows are responsible for updating the Delta table to identify Projects and Tasks to be Created, Updated or Deleted in the supporting entity tables.

There are 2 dataflows available for performing the Task sync operation.

1. Modified Date - This version works by doing a comparison on the Modified Date coming from Project Online and will only mark it for update if it's within 12 hours of that time (presumably to only care about changes since the last Dataflow run [which seems to assume 12 hourly]. This can result in an issue if there is a timezone difference between the Altus environment and the external Project Online environment.
2. Hashing - This version works by creating a hash of all the changes and doing a comprison. Please note that this dataflow does take longer to run - but it should yield more accurate results. Also it is not susceptable to time zone diferences between the Altus and Project Online environments
   • Bottom of page - Configuring Project Online project and task sync | Altus Docs
   • Ref: Bug 19716: Task Sync: POL Dataflow perf improvement - Boards (azure.com)

Overview of Task Dataflows implementation:

The dataflow compares the Task Sync table (in this case sensei_projectonline_task) to the incoming data retrieved from Project Online. The comparison between that data then results in whether the delta sync table gets a CREATE (new task found in Project Online which isn’t in the task sync table), DELETE (task found in task sync table but isn’t present in Project Online) or UPDATE (task found in both and does not match in that at least one value is different (Hashing) or modified date is different (Modifie Date), or assignments have changed).

Once the task sync table is updated then the data will flow through to External Task table and Sensei Task table (where relevant).

Entities

The following diagram offers an overview of the core entities that support the External Integration capability of Altus.

In this case we are viewing the entities related to a Project Online integration. All of the tables used in different integration scenarios (JIRA, Azure Dev Ops etc.) all use the same tables except sensei_projectonline_task, sensei_projectonline_task_delta where each integration type have their own dedicated tables.

The table “sensei_projectonline_task” is a copy of all of the tasks currently imported into the system. This table is then combined with the ODATA that the flow retrieves, and a delta is generated in memory. That delta is written to the table “sensei_projectonline_task_delta” with a column “Update Type” that dictates what the system should do, “Create, Update, Delete” with the item. This table gets overwritten every time the flow runs.

As that table is being written out, this is triggering the task sync plugins which look at the data and the “Update Type” and then, in the case of an import, decides to create a new task. First it creates a record in the sensei_external_task table and links it with an existing sensei_external_project, or else the operation is skipped. Once that’s done, it creates a sensei_task and then links it to the sensei_external_task table.

When deleting, the operation basically happens in reverse

Further Implementation Notes:

• The OData feed performs an in-memory comparison of what was previously synched (sensei_projectonline_task), generating a delta records in sensei_projectonline_task_data.
• A number of plugin handlers then trigger on changes to
  • Sensei_projectonline_task_delta - applies changes to sensei_project_online_task
  • Sensei_projectonline_task - applies changes to sensei_external_task
  • Sensei_external_task - applied changes to sensei_task
• There is a similar set of supporting plugin handlers for project related changes
• If a task is removed from sensei_task then Timesheets retain an independent reference to project and task to allow tose entities to be removed
• Resource assignments are handled separately and first before other handlers. A resource assignment can change for a task without any changes to the task itself.

Design Decisions

The external project management sync process has been implemented in this way for a number of key reasons:

• Extendable model to support synchronisation with additional project management systems (Planner, PFTW, Project Online etc.). Hence dedicated sensei_[external_system]task, sensei[external_system]task_delta and sensei[external_system]_assignment tables
• Extendable model to support the addition of custom meta data fields from external systems.
• Supports a separation of concerns between base Altus entities and external system entities. Facilitating further reconciliation between Altus and External System entity state.
• There are a number of flows (scheduled) and plugin task sync (triggered) activities to support the CUD activities occurring on the external systems project and tasks. Again this is to separate areas of concern as updates are applied from the external system.

Plugin Utilisation

Numeroues Plugins are deployed to support the integration process as data is imported into Altus and transitioned through the supporting entities via Plugin triggers.

An overview of the key entities and plugin utilised by each is summarised below.

External System Entities:

Project Online Entities:

Plugin Assembly

SenseiDataIngestionPlugin deployed in SenseiProjectIndependent solution (Atsumeru)

SenseiDataIngestionPlugin classes utilised (subset):

• Sensei_AlignExternalProject - handles Create and Update calls to External Project.
• Sensei_AlignExternalTask - Handles the Aligning of an External Task entity, including synchronization with the corresponding Sensei Task. Triggered on the creation or update of External Task records.
• Sensei_API_DeleteExternalTasks - This function will attempt to delete a number of external tasks from the system.
• Sensei_EnforceFieldLock - This plugin code is designed to run whenever a sensei_project or sensei_task record is updated. It performs field validation during the PreValidation stage to ensure that the update does not include fields specifically locked for the project.
• Sensei_HandleNewProjectPayload - This Plugin is designed to look for a new project payload stored in the OriginOfChange field on create and creates the external project link if present.
• Sensei_DeleteExternalProject - This Plugin code handles the deletion of ExternalProject records and ensures the proper reset of associated SenseiProject's FieldLock information.
• Sensei_DeleteExternalTask - This Plugin code is designed to run whenever an ExternalTask record is deleted, ensuring the deletion of associated sensei_tasks.
• Sensei_ProcessIncomingTaskData - Monitors create/update events on the target entity table of a task data flow and manages External Task records.
• Sensei_ProcessTaskQueue - Monitors create/update events on the target entity table of a task delta and manages interactions with the task sync entity.
• Sensei_UnlinkExternalProject_DeleteTasks - Manages the deletion of a sensei_externalproject entity along with associated sensei_tasks and sensei_externaltasks.
• Sensei_UnlinkExternalProject_OrphanTasks - Manages the deletion of a sensei_externalproject entity and breaks the relationship between associated sensei_tasks and sensei_externaltasks.
• Sensei_API_DeleteTasks - This function will attempt to delete a number of tasks from the system.
• Sensei_CascadeDeleteExecution - This function is designed to run as a Synchronous Post event on deletion of certain entity records.
• Sensei_CascadeDeletePreparation - This runs as a PreValidation Synchronous plugin step, looking for related items scheduled for cascade delete outputing to a SharedVariable so they can be deleted Asynchronously by the Sensei_CascadeDeleteExecution plugin code

Sync Logging

A supporting table entity to log dataflow sync logging operations.

The supporting table is called sensei_synclog. It is also available from Altus under Settings | Project Sync | Sync Logs