Integration Hub

Introduction

The purpose of this document is to provide full details regarding Sensei’s Integration Hub solution. This will include:

• What Integration Hub is.

• What it is used for.

• Full solution architecture details.

• Sensei’s approach to integration.

• Explanation of support integration methods.

Solution Overview

What is Integration Hub?

Sensei Integration Hub™ is a proprietary application developed by Sensei. It is a cloud-based solution enabling integration between Project Online and Line of Business systems. It is tightly couple with Sensei Reporting Hub™ and can be used to update objects within this database.

What is it used for?

Sensei Integration Hub enables bi-directional integration between Project Online and corporate Line of Business systems. It enables information to be imported into Empower PPM and displayed in reports or Project Details Pages. It also enables data to be exported from Empower PPM to be loaded into Line of Business systems.

What can be updated in Project Online?

The table below articulates what data can be updated in Project Online utilising Sensei Integration Hub:

	Projects Integration Hub can be used to update projects in Project Online. Project-level fields can be updated e.g. Program, Budget, Strategic Impact, etc.
	Resources Integration Hub can be used to update resources in Project Online. Resource-level fields can be updated e.g. Business Unit, Skillset, Manager, etc.
	Resource Calendar Exceptions Each Project Online resource has their own calendar. These calendars can be used to track periods of unavailability (e.g. Annual leave). These are referred to as calendar exceptions. Integration hub can be used to populate these exceptions from Line of Business systems (e.g. corporate HR systems).
	Resource Cost Rates Each Project Online resource has up to 5 cost rates. These rates can be used to forecast costs in project schedules. Integration hub can be used to populate these rates from Line of Business systems (e.g. corporate financial systems).
	Look Up Tables Look Up tables are utilised to provide selectable values for dropdown fields. These can be updated via Integration Hub, mitigating the need to manage this data in multiple locations.
	SharePoint Lists Project Online utilises SharePoint lists to capture data such as Risks, Issues, Change Requests, etc. Items in these lists can be populated/updated using Integration Hub.
	Reporting Hub tables Reporting Hub is Sensei’s data warehouse for Project Online. Integration hub can write data to tables within this data warehouse where applicable.

Integration Approach

Overview

Integration Hub enables bi-directional integration with customer IT systems. This is achieved by import and exporting data from Project Online.

	Project Online Export This involves data being “exported” from Project Online for upload to a customer’s Line of Business (LOB) systems.
	Project Online Import This involves data being “imported” into Project Online from LOB systems.

Entity files

The default integration method in Sensei Integration Hub™ utilises entity files (flat files) to enable integration between Project Online and LOB systems. These entity files will contain the required data to be updated. Further details on entity files are articulated in a subsequent section of this document.

Project Online Import

Below is a diagram highlighting how entity files are exported from Project Online via Sensei Integration Hub™:

Project Online Export

Below is a diagram highlighting how entity files are exported from Project Online via Sensei Integration Hub™:

Data Transfers

Overview

The data transfer will be facilitated by utilising flat file (.csv) format. Sensei Integration Hub provides an endpoint for the customer to upload and download the .csv files (Azure Storage Container). Sensei’s Integration Hub will handle the generation of .csv files containing Project Online data and the parsing of .csv files.

Sensei will ensure that the .csv files (for upload to SAP) are made available in the Azure Storage Container for the customer to extract (pull data).

Data Encryption

Data transfers in Office 365 and Project Online

Data transferred as part of the operation of Office 365 and Project Online is transferred over HTTPS with TLS.

Data encryption in Office 365 and Project Online

Data transferred as part of the operation of Office 365 and Project Online is transferred over HTTPS. Microsoft do not disclose the data encryption at rest policy for Project Online other than to specify that NIST 800-88 Guidelines on Media Sanitization are followed, which address the principal concern of ensuring that data is not unintentionally released. These guidelines encompass both electronic and physical sanitization.

Data transfers between Office 365 and Sensei Reporting/Integration Hub

Data Transferred as part of system integration efforts are sent over HTTPS to Azure Storage Blob containers secured by SAS tokens.

Data encryption between Office 365 and Sensei Reporting/Integration Hub

Where possible (contingent on user browsers) Sensei employs HTTPS/TLS 1.2 4096 bit key, with perfect forward secrecy (Diffie-Hellman key exchange) for data in transit. For data at rest, the Transparent Data Encryption SQL Azure feature is enabled for all customer databases that ensures the data is encrypted before it is written to disk.

De-coupling of Integration components

This approach of using the Azure storage blob container enables the customer and Sensei components of the integration to be de-coupled. This approach yields the following benefits:

Benefit	Details
Customer Integration components	Changes to the customer integration components will have no impact on the Sensei Integration components.
Sensei Integration components	Changes to Sensei integration components (e.g. Integration Hub) will have no impact on the customers components.
Unavailability	If the customer’s systems or Integration Hub are unavailable, down or upgraded, one will not impact the other. This also makes fault identification and resolution quicker.
Improved Issue Resolution	It enables Sensei and the customer to quickly identify which components of the integration are causing issues.
Postpone	Activities can be postponed during high activity.
Transient Error Retry	Activities can be retried when transient errors occur.

Entity Files

Field Types

The table below provides details regarding the field types that are used in the entity files:

Field Type	Example	Details
Text/String	Project Name	Alpha-numeric data. Commas, line breaks and semi-colons must be encapsulated in double-quotes.
Numeric	2017	Whole number integers (e.g. Year).
Date/Time	2016-07-15T00:04:58+00:00	Date information is to be formatted in ISO 8601 standard.
Decimal	1232130.56	Number with decimals and no millions/thousand separators.
Boolean	TRUE	Flag field (Yes / No). Can be stored as 1 or 0 in some databases.

CSV Formatting

CSV Element	Details
File	Each .csv file contains only one entity table.
Format	The file is generated as a UTF-8 to ensure compatibility with special characters.
Header	The file must contain one header line with column names.
Field Separator	Each field is separated by a ‘,’ (comma), ‘;’ (semicolon) or ‘ ‘ (tab).
Number Format	All numbers are formatted in US Format. ‘.’ (point) as decimal point and no thousand separators ‘,’.
Data Format	All dates are formatted in one of the following formats: ISO 8601 e.g.: “2016-07-15T00:04:58+00:00” or “2016-07-15T00:04:58Z” English USA e.g.: “07/15/2016 00:04:58” English Australia e.g.: “15/07/2016 00:04:58” Custom date and time format It is recommended to include the time zone or ensure that the time is UTC.
String Format	Can be in enclosed in double quotes. Fields containing line breaks (CRLF), double quotes, semicolon, tab and commas must be enclosed in double-quotes. If double-quotes are used to enclose fields, then a double-quote appearing inside a field must be escaped by preceding it with another double quote.
Line Breaks	Each record is located on a separate line, delimited by a line break (CRLF).

Authentication Renewal

For HTTPS and Azure Storage endpoints, a renewal of the authentication (certificate or SAS token) must be performed annually. SharePoint Online access uses an account which can be setup to change the password.

Azure Storage (HTTPS):	Transport security via HTTPS protocol. Access security via Shared Access Signature (SAS). (optional) Whitelisting of the Customer’s nominated IP address for SAS token.
SharePoint Online document library (HTTPS):	Transport security via HTTPS protocol. Access security via Username and Password. No Firewall. Data stored inside SharePoint Online.

File Location and File names

Inside the endpoint provided, two locations will exist:

/XXXX-in/	Files created by the Customer for import to Project Online via Integration Hub.
/XXXX-out/	Files created by Integration Hub for export to the Customer’s Line of Business systems (e.g. Corporate Financial System, etc.).

Load Process / Timing

The Sensei Integration Hub picks up the uploaded files to the Azure Storage Blob by the Customer within a ten-minute timeframe.

The Sensei Integration Hub can export a file / files to the Azure Storage Blob hourly or daily.

Automating transfers to and from Azure Storage

Integration Hub supports two key methods for uploading/downloading data from Azure Storage:

1. Manually.

2. Automated.

Manual Approach

The manual approach requires a user to login to Sensei Hub to either upload or download files via the user interface. The manual approach for uploading and downloading files is covered off in these sections of the document:

• Uploading Files

• Downloading Files

Automated Approach

For regular file uploads or downloads, it is often more convenient to automate the file transfer process. The diagram below articulates the approach at a high-level.

AzCopy

There are many methods to automating the file transfer process of files between the Azure Storage Container and a customer’s environment. One of the available methods that Sensei recommends involves using AzCopy.

What is AzCopy?

AzCopy is a command-line utility that you can use to copy blobs or files to or from an Azure storage account. This utility can be used to upload and download files from the Azure Storage Container linked to Sensei Integration Hub™.

Using AzCopy

The process below articulates the steps involved in setting up AzCopy to upload and download files from the Azure Storage Container.

Getting Started

Download AzCopy.zip to the computer or server:

https://docs.microsoft.com/en-us/azure/storage/common/storage-use-azcopy-v10

Once downloaded, extract all files to a shared location:

Open the folder (where the files have been extracted to) and copy the link (this is required for the PowerShell scripting process):

Example location:

C:\Users\username\folder\AzCopy\azcopy_windows_amd64_10.1.2

Create folders for file upload/download

Create folders for uploading and downloading files. Note – These folders must be in the root where you downloaded the AzCopy components to.

Example folder locations:

Upload	C:\Users\username\folder\AzCopy\CSVsIN
Download	C:\Users\username\folder\AzCopy\CSVsOUT

Create PowerShell scripts

Before you create the PowerShell scripts you will need to generate a SAS token. Details for how to do this can be found here.

Once you have generated the SAS token (You’ll need one for downloading and one for uploading), you can create the PowerShell scripts.

Below are example scripts – You will need to modify these as per the locations you have setup and the SAS tokens generated.

Import files to Integration Hub

Configure the PowerShell script.

cd "C:\Users\username\AzCopy\azcopy_windows_amd64_10.1.2"

.\azcopy.exe copy "C:\Users\username\AzCopy\CSVsIN"

"https://integrationhub000000.blob.core.windows.net/customer-sharepoint-com-sites-epm-in?sv=2017-07-29&sr=c&si=customer-sharepoint-com-sites-epm-in-P01&sig=KdHt7RTW8dTIFgAOeXEfLas47aZZZeiT%2B99XP81U%2F2azM%3D&spr=https"--recursive

Export files from Integration Hub

Configure PowerShell script to export files from Integration Hub.

cd "C:\Users\username\AzCopy\azcopy_windows_amd64_10.1.2"

.\azcopy.exe copy "https://integrationhub111111.blob.core.windows.net/customer-sharepoint-com-sites-epm-out?sv=2017-07-29&sr=c&si=customer-sharepoint-com-sites-epm-out-P01&sig=Q0e1wvoAGuuPaEp2rrliz587wTS6YfRbUUpEY3uaP%2FXo%3D&spr=https"

"C:\Users\username\AzCopy\CSVsOUT"--recursive

Once you have created the PowerShell scripts, save them to the same directory as the upload and download folders.

Automating the Process

The final step is to automate the process to run the scripts to upload/download the files. This can be done using a wide variety of scheduling tools. The process below utilises the Windows task scheduler.

Open Task Scheduler (using Search). sensei@powerlinkcomau.onmicrosoft.com PE3WLPT7zo1yZNh1umMq

Select Create Task from the right-hand pane of options:

Give the task a name and check the Run with highest privileges option:

Select the Triggers tab and set a schedule to run the job. Click OK to save changes.

Select the Actions tab, the click New… to add an action:

Add in the following:

Program/Script: Powershell.exe

Add arguments (optional): & ‘Folder location\script.extention’

Example = & 'C:\Users\username\AzCopy\AzCopy-ImportAllFiles.ps1'

Click Ok to create the job.

Click on Task Scheduler Library to find the job

Jobs can be manually run from the list of jobs by selecting them name, then clicking on Run from the Actions pane.

Error Logging

Sensei Reporting Hub™ captures extensive logs for Project Online including details of all Integration Hub jobs. These jobs are stored in the Reporting databases in two tables:

Schema	Table Name	Details
Integration	Operation Log	Detailed log of Integration operations. Data Flows into/out of the Reporting Hub database and SharePoint List operations.
	Operation Status	Summary of Integration operations. Data Flows into/out of the Reporting Hub database and SharePoint List operations.
dbo	IPMO Import Log	Detailed Log of Import operations. Data Flows from Reporting Hub into Project Online
	IMPO Import Status	Summary of Import operations. Data Flows from Reporting Hub into Project Online

Sensei has notifications setup to provide alerts for job failures. These aren’t sent to customers directly and Sensei’s Customer Care team triages these logs and will notify customers when required to be involved in troubleshooting errors.

Sensei Hub

Overview

Sensei Hub is a web application that provides customers with a front-end user interface for Reporting Hub and Integration. It enables customers to administrate their instance of Reporting Hub and Integration Hub. Below is a list of the common use cases catered for by Sensei Hub:

	Report Designer Accounts Sensei Hub users can create report designer accounts for themselves or other users. These accounts are required if customer wish to create their own standard reporting using SQL Server Reporting Services (SSRS).
	Database Access Sensei Hub users can control who has access to the reporting hub database and can nominate IP addresses and IP ranges that have access. This process is more commonly referred to as IP whitelisting.
	Configuring Integration Import Jobs Sensei Hub users can create, edit, and delete import jobs. Details on how these jobs are created is covered off in a subsequent section of this document.
	Configuring Integration Export Jobs Sensei Hub users can create, edit, and delete export jobs. Details on how these jobs are created is covered off in a subsequent section of this document.

Getting Started

User Account

To access Sensei Hub, log in with your Office 365 user account.

Sensei Hub Address

Sensei Hub can be accessed via the following URL:

https://hub.sensei.cloud/

User Interface

Once logged into Sensei Hub, you’ll land on the home page. From here you can access your Reporting Hub and Integration Hub instances.

To access, create, and edit Integration Jobs, click on the 'Integration Hub' link in the menu on the left hand-side of the page.

From here you can select the Integration Hub environment you wish to modified jobs in:

Integration Hub Operations

There are two types of Integration Hub jobs that can be created and/or edited via Sensei Hub:

	Project Online Export Operation This involves data being “exported” from Project Online for upload to a customer’s Line of Business (LOB) systems.
	Project Online Import Operation This involves data being “imported” into Project Online from LOB systems.

The subsequent sections provide details on how to configure these two types of jobs using Sensei Hub.

Import Operations

Overview

Import jobs are used to update information in Project Online. However, they can also be used to update information in Reporting Hub. This is useful for integration of data that only needs to be provided for in reporting (e.g. Actual costs from a corporate financial solution).

Step	Details
Process	The first step of the process is to configure the data processing step. This will take the import file (CSV) and process it in the Reporting Hub database. This step is required prior to a PPM update being configured.
Update	The next step is to configure the update job to be performed (e.g. Update project data, resource data, etc.). This step requires an SQL view to be created. If displaying integration hub data in reporting only, this step is unnecessary.
Upload	The final step of the process involves uploading files to Integration Hub. If you wish to automate this process a SAS access token will be required to allow this operation to be performed.

Pre-requisites

CSV File: Prior to configuring an import job in Sensei Hub, a valid .CSV file must be created. This file must be formatted as per the instructions in the CSV Formatting section of this document. It must also contain an identifier so that matching records can be ascertained (e.g. Project Identifier, if projects are to be updated).

Process

Once you have your CSV file in place, the next step is to configure the step that will process the file once it is uploaded (either manually or via HTTPS).

To do this, select the Add import operation from the process files section.

Once done the New import operation dialog box will open.

Firstly, select choose file to upload the CSV file you want to configure the process job for. Once done click on Analyse example file.

Once done, Sensei Hub will populate the form inputs based on the analysis of the CSV file:

These settings can be left with their default values:

Sensei Hub will name the integration hub table as per the file name. This can be overwritten as required.

The file analysis will generate field names from the CSV file and will analyse the field type. You may need to change these settings. For example, in the above example, the amount contains two decimal places, so the configuration will need to be update.

Import Operation

There are two types of import operation:

1. The import will overwrite all existing data in the target table.

2. Data imported will append data already in the SQL table.

The default option is the first one. This caters for batch update operations. The second option can be used when you are performing delta-sync operations. Once you have updated the setings, click on Save to apply the process.

Update

SQL View: For Project Online to be updated, an SQL view is required to be created in the Reporting Hub database. There is a specific nomenclature for the view name and structure depending on which object is being updated. Further details are provided below regarding this.

To update PPM, click on the Add update PPM icon, then select Project Online.

A dialog box will pop up. Target is the object(s) to update, further details regarding these are provided below.

Frequency can be either hourly or daily.

Update Types

The following object(s) can be updated in PPM via the Sensei Hub interface:

	Projects Integration Hub can be used to update projects in Project Online. Any project-level data can be updated e.g. Program, Budget, Strategic Impact, etc.
	Resources Integration Hub can be used to update resources in Project Online. Any resource-level data can be updated e.g. Business Unit, Skillset, Manager, etc.
	Resource Calendar Exceptions Each Project Online resource has their own calendar. These calendars can be used to track periods of unavailability (e.g. Annual leave). These are referred to as calendar exceptions. Integration hub can be used to populate these exceptions from Line of Business systems (e.g. corporate HR systems).
	Resource Cost Rates Each Project Online resource has up to 5 cost rates. These rates can be used to forecast costs in project schedules. Integration hub can be used to populate these rates from Line of Business systems (e.g. corporate financial systems).
	Look Up Tables Look Up tables are utilised to provide selectable values for dropdown fields. These can be updated via Integration Hub, mitigating the need to manage this data in multiple locations.

When you select the required target, the dialog box will change to provide you with specific information regarding the system requirements for the update.

SharePoint lists cannot be populated via the User Interface. If you have a requirement to update a SharePoint list, please contact your customer care team.

The sections below provide details for how to update these objects:

Update Projects

This job type is used to update enterprise projects in Project Online. Note – This job type can only be used to update project-level information – Schedules and tasks cannot be updated via Integration Hub

Update Resources

This job type is used to update enterprise resources in Project Online. This is useful for updating information that is typically captured outside of Project Online e.g. Job Title, Team, Skill set, etc.

Update Resource Calendar Exceptions

This job type is used to update calendar exceptions for resources. A calendar exception is a period of unavailability e.g. a public holiday or annual leave. Updating this data will create an exception which will reduce the target resources availability to 0%. This will result in work assigned to this user being re-scheduled to the next available time slot.

Update Resource Cost Rates

This job type is used to update the cost rates for a resource. A resource can have up to five rates specified in Project Online. Rates can be used by inserting the Cost Rate Table column into the task usage or resource usage views in project professional.

Update Look Up Tables

This job type is used to update enterprise Look Up tables with values. This is useful if look up tables contain large datasets or data that is managed in another corporate system e.g. Team Names.

Update SharePoint lists

Details for the Reporting Hub view to update SharePoint data.

The SQL must return the following results. Each row is understood by the Integration Hub as a task to create/update or delete a SharePoint item:

• WebId(primary) or WebUrl
• ListId(primary) or ListTitle or fixed config TargetSharePointListTitle to target only one list
• ItemId - can be NULL for a new list item
• DeleteItem - flag(bool)
  • 0=>Create or Update list item
  • 1=>delete this list item
• all other fields to be updated based on the Internal name or title or Guid of the field.

See table [dbo].[IPMO_WssListFieldMetadata] for the internal name of a field.

Upload

There are two mechanisms for upload files to Integration Hub:

1. Manual Upload.

2. Via Azure Storage Container.

Both methods are covered off in the subsequent sections.

Manual Upload

Select Storage Ad hoc Testing:

Select Choose File, select CSV file, then click Upload.

This will then upload the file to Integration hub. The next two steps (Process and Update PPM) are required for the data to be processed in Project Online.

Upload via Azure Storage

This process is recommended for customers who wish to automate the file transfer process between their line of business systems and integration hub.

This transfer mechanism requires a SAS token to be generate. To do this select Storage Primary Policy.

Select Create SAS from the dialog box that pops up.

Once done specific your IP address or the IP range which should have the ability to upload files to the Azure Storage Container. Then click on Create SAS.

Once done, a SAS Access Token will be generated. Make a note of the Full URL and SAS as these will be required for the file transfer method you use to post files to Integration Hub.

Export Operations

Overview

Export jobs are used to extract key data from Project Online and/or Reporting Hub so that the data can used to update a customer’s Line of Business systems (e.g. Project forecasts).

Step	Details
Generate	First step is to configure the process to generate the export files. An SQL statement or view is required to do this.
Download	Once the generate step of the process has been configured the file can be downloaded. This requires access tokens to be generated to allow this operation to be performed.

Pre-requisites

SQL View / Query: To generate files for export, an SQL view or query must be coded to compile data from Reporting Hub objects (e.g. Tables and Views). Once this is in place, the steps to complete the generate process can be undertaken.

Generate

Once you have an SQL view of query in place, you can complete the steps to generate the CSV file.

This is done by selecting Add export operation, then selecting CSV file.

Provide a name for the export operation. The file name will automatically inherit the Title. However, this can be overwritten where applicable.

Note

The File Name field can contain date formatting characters to generate filenames based on the export time and date. e.g.
to produce:

The file formatting can be left with the default settings, unless you have a requirement to change the field separator or date formats.

Export frequency can either by Hourly or Daily.

If daily is selected, you’ll need to specify a time for the file to be generated.

Final step is to populate the SQL query box with either the SQL view name or the T-SQL statement that will generate the data output.

Once done, click on Save. Then select Activate to start generating the file.

Download

Manual Download

Select Choose File, select CSV file, then click Download.

This will then enable you to download the file from Integration hub.

Click on the file name to download it.

Download via Azure Storage

This process is recommended for customers who wish to automate the file transfer process between their line of business systems and integration hub.

This transfer mechanism requires a SAS token to be generate. To do this select Storage Primary Policy.

Select Create SAS from the dialog box that pops up.

Once done specific your IP address or the IP range which should have the ability to download files from the Azure Storage Container. Then click on Create SAS.

Once done, a SAS Access Token will be generated. Make a note of the Full URL and SAS as these will be required for the file transfer method you use to download files from Integration Hub.

Integration Data

Example Import file

Details

Purpose:	The purpose of this entity file is to enable project budgets from the customer’s corporate finance system to be uploaded to Project Online.
Type:	Batch.	Frequency:	Daily.
Source:	Corporate Finance System.	Target:	Project Online.
Time:	This job will occur daily between 22:00 and 01:00.
File Format:	Comma Separated Values (CSV).
File Name	ImportProjectBudgets_<date/time>.csv
Conditions:	Nil.

Field Name	Type	Notes
ProjectUid	Text	Unique Identifier for the project. Auto-generated by Project Online.
Project Identifier	Integer	Project identifier. Configurable in Project Online per enterprise project type. Can also contain letters.
Budget Code	Text	Budget code generated in the customer’s corporate financial system.
Commitment Item	Integer	Commitment item generated in the customer’s corporate financial system.
Year	Integer	Calendar year e.g. 2019, 2020.
Month	Integer	Month formatted as a whole number e.g. January = 1, February = 2, etc.
Amount	Decimal	Monthly budget value formatted to two decimal places.

Example File

Attached below is an example CSV file based on the details above.

Example Export file

Details

Purpose:	The purpose of this entity is to enable project forecasts from Project Online to be downloaded and updated in the customer’s corporate finance system.
Type:	Batch.	Frequency:	Daily.
Source:	Project Online.	Target:	Corporate Finance System.
Time:	This job will occur daily between 02:00 and 04:00.
File Format:	Comma Separated Values (CSV).
File Name	ProjectForecasts_<date/time>.csv
Conditions:	Nil.

Field Name	Type	Notes
ProjectUid	Text	Unique Identifier for the project. Auto-generated by Project Online.
Project Identifier	Integer	Project identifier. Configurable in Project Online per enterprise project type. Can also contain letters.
Year	Integer	Calendar year e.g. 2019, 2020.
January	Decimal	Monthly forecast values formatted to two decimal places.
February	Decimal
March	Decimal
April	Decimal
May	Decimal
June	Decimal
July	Decimal
August	Decimal
September	Decimal
October	Decimal
November	Decimal
December	Decimal