Configuring Project Online project and task sync

The following videos walk-through the setup process covered in more detail below:

After setting up the dataflow, don't forget to setup the External System entry:

Note

There is no audio in these videos

Note

The provided dataflow(s) should work out of the box for most customers, but there is an issue where Dynamics doesn't convert dates to UTC time when reading them, so if your source system and (PWA) and dynamics are in different timezones then you may need to use the version of the task workflow that calculates hashes to determine if a task should be updated. Downloads
Project Online Timezone Settings: Account Icon (top right) / View Account / Settings & Privacy / Region / Time zone (https://myaccount.microsoft.com/settingsandprivacy/language)
Dataverse Timezone Settings: [Altus App] / Settings / Personalization Settings / Options / General / Time Zone

Data Flow	Query Name	Sync Table
Project	sensei_projectonline_project	sensei_projectonline_project
Task	sensei_projectonline_task_delta	sensei_projectonline_task_delta
Task	sensei_projectonline_assignments	sensei_projectonline_assignment

To connect a Dataflow to your Project Online instance, you can follow these steps:

• Navigate to https://make.powerapps.com and log in with your administrative account
• Ensure that you have selected the Power Apps Environment that contains your Altus installation
• From the quick launch menu, select to expand Data and then select Dataflows

If the sample Dataflows already exist,

• Locate the Dataflow that you wish to configure, open the ellipsis (…) menu and then select Edit (then continue with the steps below from 'Populate the SourceUrl...':

If the sample Dataflows do not exist,

• Select New dataflow > Import template (Preview)
• Select the .pqt file that you wish to upload the Dataflow template for. (Links to the example Dataflows are in the Initial Setup section)
• Click Create
• Populate the PWAUrl parameter with the Url of your Project Web App (PWA) environment. For example: https://myorganisation.sharepoint.com/sites/pwa/
• Populate the SenseiIQUrl with the Url of your Altus Power Apps environment. For example: https://org1303d343.crm.dynamics.com
• It is recommended to leave the "Modified Last Hours" parameter to 12. This parameter governs how long back the dataflow will look for modified dates in order to perform a delta operation. Only adjust this if your data flow only runs once a day or less by adjusting the number upwards.

Note

In cases where you are combining data from multiple M365 tenants (i.e. Altus in one Microsoft tenant and PWA in another, please ensure that you select Options from the ribbon bar (Project Options) and check 'Allow combining data from multiple sources'

• Select the dataset query from under the Queries heading, then select to Configure connection
• If there is no existing connection for your Dataflow, select 'Create new connection'
• Enter the following details:

Column	Value
Connection name	{Enter a name for your connection (if not populated automatically)}
Authentication kind	Organizational account
Privacy level	Organizational

• Select Sign in and enter the credentials of an account which has access to all project data in the environment that wish to connect to.

Note

If you are using the same account to log in to the source environment as the one accessing this environment, still choose to enter the credentials afresh (rather than selecting the already logged in account) - for whatever reason, selecting the account (rather than re-entering the credentials) does not seem to have a high success rate in terms of the data flowing through upon creation of the connection.

• Select Connect

• If successful, data from the external system will load to the preview in the Power Query window.

• Select the Next button in the bottom right of screen

• Ensure that the Parameters Queries are configured with the Load setting set to Do not load (this information does not need to logged anywhere).

• Follow these steps for each remaining query in your dataflow:

  • Select a Query a query from the left hand panel.
  • Select Load to existing table. And select the correct destination table (see reference in this article)
  • Check the 'Delete rows that no longer exist in the query output' checkbox.
  • In the right hand panel map all of the columns present. Click 'Auto Map' to complete this quickly.
  • Select to run your Dataflow on a schedule. The frequency depends on both your business requirements as well as the volume of data that needs to be sent, particularly if you are synchronizing tasks. We recommend running dataflows once per day.
  • Verify that your Dataflow runs successfully.

• Use the screenshots below to verify your column mappings:

• sensei_projectonline_project Dataflow Mapping:

• sensei_projectonline_task_delta Dataflow Mapping:

• sensei_projectonline_assignment Dataflow Mapping:

Note

If the Dataflow contains a query called Tasks_Raw, select 'Do not load' as the Load setting for that Query. (It is used internally to the Dataflow only).

Once the dataflow is running and populating the sync tables, you're ready to complete the second part of the configuration process, which consists of the following steps:

• Enable the Disabled External System Record(s) by navigating to the 'Settings' area, then to 'External Systems'. Change the view to 'Inactive External Systems' and select the record for the system you wish to activate and click 'Activate' from the ribbon.
• Once the record is active, review the column mappings by opening the External System record and navigate to the ‘Projects’ tab. Altus has mapped the standard columns that we typically see our clients using in Project for the Web and Project Online to columns that exist for a Altus Project. If there are new or additional columns that you require to be mapped, first, ensure that your data flow is configured to load the new column data into a new column on the target table, then a ‘Metadata Mapping’ for the column desired to a column in the sensei_project table. This will ensure that the data retrieved by the data flow will carry through to the Altus project that is linked to the external project.
• Configure the ‘Project URL Pattern’ to provide a direct link to the project in the external system. The “Pattern” for Project Online projects needs to be updated with the org name and optionally PWA name as follows: https://m365x608009.sharepoint.com/sites/pwa/Project%20Detail%20Pages/Schedule.aspx?ProjUid={ID}&ret=0 where the text above would be replaced with your organization name and PWA name.
• Finally, on the ‘Details’ tab of the ‘External Project’ record, there is a ‘Icon URL’ column. This displays the base64 encoded icon image that represents this type of external project in the External Project selector on the sensei_project form and also in the Power BI Insights reports. It shows what system the external project record originated from. The values for these ‘Icon URL’s are configured correctly for Project for the Web and Project Online projects, however, if you are setting up a different type of system or want to change the icon for whatever reason, please enter a new base64 encoded icon image URL into this column. Jump to: Icon URL Reference

Use the tables below as a reference for the default configuration of each of the metadata mapping tables.

Project

Project Sync Table Name: sensei_projectonline_project

Project URL Pattern: https://TENANTNAME.sharepoint.com/sites/PWANAME/Project%20Detail%20Pages/Schedule.aspx?ProjUid={ID}&ret=0

Name	External Column Name	External Column Type
ID	Sensei_projectonline_projectid_key	ID
sensei_effortcompleted	sensei_projectactualwork
sensei_effortremaining	sensei_projectremainingwork
sensei_efforttotal	sensei_projectwork
sensei_name	sensei_name	Name
sensei_percentcomplete	sensei_projectpercentcompleted
sensei_projectfinish	sensei_projectfinishdate
sensei_projectstart	sensei_projectstartdate
sensei_proposedstart	sensei_projectstartdate

Task

Task Sync Table Name: sensei_projectonline_task

Task Sync Delta Table Name: sensei_projectonline_task_delta

Task URL Pattern: https://TENANTNAME.sharepoint.com/sites/PWANAME/Project%20Detail%20Pages/Schedule.aspx?ProjUid={ID}&ret=0

Name	External Column Name	External Column Type
sensei_duration	sensei_duration
sensei_effort	sensei_effort
sensei_effortcompleted	sensei_effortcompleted
sensei_effortremainingoverride	sensei_effortremaining
sensei_externalparenttaskid	sensei_projectonline_parenttask_id_key	Parent Task ID
sensei_externalprojectid	sensei_projectonline_projectid_key	Parent Project ID
sensei_externaltaskid	sensei_projectonline_taskid_key	ID
sensei_index	sensei_index
sensei_milestone	sensei_milestone	Is Milestone
sensei_name	sensei_name	Name
sensei_percentcomplete	sensei_percentcomplete
sensei_summary	sensei_summary
sensei_taskfinish	sensei_taskfinishdate
sensei_taskstart	sensei_taskstartdate

Assignment

Assignment Sync Table Name: sensei_projectonline_assignment

Name	External Column Name	External Column Type
sensei_externalassignmentid	sensei_projectonline_assignmentid_key	ID
sensei_externalresourceid	sensei_projectonline_resourceid_key	Resource ID
sensei_externaltaskid	sensei_projectonline_taskid_key	Parent Task ID
sensei_name	sensei_name	Name
sensei_unit	sensei_units

Modifying the standard task sync dataflow templates to include custom task attributes

Frequently, our clients have a requirement to sync over additional task attributes from the source execution tool system into Altus. This might be additional metadata about the task like a "Task Category" or "Task Cost Type" as an example. To do this successfully, please ensure you follow the steps below (in the images, the steps that require modification are shown in red boxes, and the specific fields custom to our example are shown in purple.):

1. For a project online external system where a new attribute needs to be synced, ensure that your custom column has been added to the following tables:
   • sensei_task
   • sensei_projectonline_task
   • sensei_projectonline_task_delta
2. Ensure that you have created an additional metadata mapping for the "Tasks" tab in the Project Online External System configuration.
3. Modify the Project Online Task Sync dataflow template using the following guidance:
   • for the "Tasks_Raw" query, open the advanced editor and alter the "BaseUrl" step to include your new field from the source system - note Project Online custom fields will be case sensitive and have no spaces or special characters (ex. "Task Category" would become "TaskCategory"). In the example below, we are adding two fields - "TravelRequired" and "TaskCostType". We are also showing a filter to remove inactive tasks from the query output.
   • also in the "Tasks_Raw" query, update the "RenameTable" step to rename the field name from the source to the internal name of the column in the Altus tables.
     
     
4. Continue modifying the queries:
   • in the sensei_projectonline_task_delta query, open the advanced editor and for the "DynamicsUrl" step, ensure you add the column from the sensei_projectonline_task table.
   • also in the sensei_projectonline_task_delta query, change the "UpdateRows" step to include the new field/column on both sides of the comparison, meaning, add the column in the first set of fields {}, and in the set of fields after the "Replaced Value" text {}.
   • ensure that the field from the source query is then mapped to the column in the dataflow "Map Tables" screen, if the field was renamed correctly, you can using "Auto map".

DLP Policy Details

This integration will create/utilise the following Power Platform connections types:

Downloads

Dataflows for the Project and Task Templates can be found on the Initial setup page.

If the client is having a timezone issue with the above Task template you can use the following file - Dataflow for Project Online Tasks Hash