edison365 Datastore: Admin User Guide

Guide for PWA Administrators configuring DataStore

Contents

1. Product Features
2. Prerequisites
3. Getting Started
4. Reporting Options
5. Other Considerations
6. Data Captured - Project
7. Data Captured - SharePoint
8. SharePoint Configuration Change Considerations
9. Removal of DataStore

Product Features Available Today

• SaaS based product hosted in Azure
• Queue based sync system
• Driven from Project Online PWA events
• Key Project Online data
• SharePoint List data from Project Sites
• Snapshot / history tables
• Bulk / on demand Sync
• Scheduled Sync
• Email alerts
• SQL database maintenance
• Sync History via the UI
• Configured via an intuitive UI
• 1 Database per PWA instance

Prerequisites

• Azure SQL Cloud database - https://docs.microsoft.com/en-us/azure/sql-database/sql-database-get-started
  • At least S2 tier or equivalent tier.
  • Blank Database.
  • Allow the DataStore service IP addresses to the Azure SQL firewall – support can provide the IP addresses.
    • Alternatively it is possible to allow Access to Azure Services but it would be recommended to restrict the IPs.
  • SQL username and password with full control over the target database:
    • Ideally the Azure SQL Server Admin account or an account with at least dbmanager role for the initial deployment.
    • Post deployment, the account requires dbowner access to the database.
  • Ensure the backup / restore options are understood: https://docs.microsoft.com/en-us/azure/sql-database/sql-database-business-continuity
  • Enable auto tuning: https://docs.microsoft.com/en-us/azure/sql-database/sql-database-automatic-tuning
  • Consider enabling Auditing: https://docs.microsoft.com/en-gb/azure/sql-database/sql-database-auditing-portal
  • Consider enabling Threat Detection: https://docs.microsoft.com/en-gb/azure/sql-database/sql-database-threat-detection-portal
• Project Online PWA instance
  • PWA user account with site collection Admin access and full PWA Admin access to configure the DataStore app.
  • PWA Project User account to run as a functional service account.
    • With the correct access in PWA and correct Project Online license.
• Access to Project Online SharePoint App Catalogue site.
• The DataStore .app files from edison365 / your edison365 partner.
• Ensure that custom app authentication is enabled on the SharePoint Online tenant, to enable it run the following SharePoint PowerShell command:
  • set-spotenant -DisableCustomAppAuthentication $false
  • Details on the set-SpoTenant cmdlets here: https://docs.microsoft.com/en-us/powershell/module/sharepoint-online/set-spotenant?view=sharepoint-ps 

Getting Started

• Add the app files to the internal app catalog site in the App for SharePoint library.
• Log into the PWA site with the site collection admin (SCA) account.
• Deploy the edison365DataStore.app file to the PWA site via the Add an app feature using the PWA SCA account and trust it when prompted to.
• Optional (when Project Sites exist in other site collections): Deploy the edison365DataStoreAccess.app file to the site collections via the Add an app feature using the SCA account and trust it when prompted to.
  • This can be done as and when required if the business configures PWA to create Project Sites in other site collections
• Launch the DataStore app from the PWA > Site Contents page.
• Click 'Get Started' from the landing page.
• On the Database page, enter details in the 5 fields.
  • Note: even if the default example port is correct (1433) ensure you type 1433
• If your SQL Server is using an Azure Active Directory user account, toggle on the Active Directory switch.
• Click the 'Deploy Schema' button.
• Click 'OK' then click 'Next Step'.
• On the Project User Account details page, enter the username and password for an account that has the correct access as detailed on the page.
• If your SharePoint Online tenant enforces modern authentication, the “Modern Authentication” option must be enabled in DataStore and the Azure AD app approved.
• Click 'Save'. 
• At this point the edison365 team will need to activate the DataStore instance, edison365 support team can work with the edison365 product team to get this instance activated.
• Click 'OK' then click 'Next Step'.
• On Select data to sync page turn on the feeds that you want to sync and set the time phase date range as needed.
• Only enable the feeds that you need in the database, enabling everything will cause longer sync times and increase SQL costs.
• Worth noting the “Project Multiline HTML Values (Global Setting)” – this means that having it set on the Project Sync page means it will be enabled on the Scheduled Sync page.
• Click 'Save'.
• Click 'OK'. If SharePoint list data from the project sites are required, click the 'List Sync' button on the nav bar.
• On the select lists to sync page, enter the list titles as required.
• Enter the list title as it appears on the project sites including the correct case then click the '+ Add List' button.
• Optional: On the list sync page, notice the Add Project Host Site Collection Location section.
  • This is used if your Project Online deployment uses other site collections in the tenant to store the Project Sites. Those other non-PWA site collections will need the edison365DataStoreAccess.app added to the site
  • Enter the site collection URL as indicated and click '+ Add Site'
• Once the list sync page is completed, click the 'Save' button.
• As per the status, this process could take a while if you have many project sites and many lists registering. Check back to the page periodically (you will need to navigate away from the page then back for it to refresh). The status will show complete once the process is finished.
  • Note: To temporarily turn off the list sync, use the 'Suspend Sync' button

• Click the Bulk Sync link on the nav bar to launch the bulk sync settings.

• To run a bulk sync, turn on the required sync then click the 'Sync' button.

  • This process may take some time to complete if you have a large data set

• This functionality can only be used once every 7 days, once the bulk sync operation has completed the status message bar will give you a count down to the next time a bulk sync can run.

• Click the Schedule Sync link on the nav bar to launch the schedule sync settings.

• If required, set up the scheduled sync. The scheduled sync option is recommended to ensure the data is correct before reporting but to also refresh the time phased data for entities that might not have been updated in PWA such as Resources.
• The Sync History page will show the job history. It is recommended to check this daily for any errors. If errors are found, use the retry button to try the sync again.
• The Notification page enables email addresses to be added to receive email alerts for any long running jobs such as bulk syncs or any sync errors from all job types.
• The SQL Maintenance page enables a default SQL database maintenance plan to be enabled on the DataStore Azure SQL Database. Turn this feature on then set the schedule.
  • The SQL maintenance feature is only available to Azure SQL databases

• The DataStore configuration is now completed.

• Data will be synchronised via the queue based system as data is created or updated in the PWA instance or also via the bulk sync / scheduled sync process.

Reporting Options

• Any tool that can connect to a SQL Database, including but not limited to:

  • Power BI – recommended tool

  • Excel / Excel Online

Other Considerations

• Monitor the SQL DTU usage in the Azure Portal and scale the database as required.

• The database makes use of the SQL Temporal tables, this features keeps history records for all rows. By default no retention policy is set. A retention policy should be set based on the business reporting requirements on each system versioned table. Details here on how to set a retention policy: https://docs.microsoft.com/en-us/azure/sql-database/sql-database-temporal-tables-retention-policy

• The system versioned history tables can also be turned off if this feature is not required: https://docs.microsoft.com/en-us/sql/relational-databases/tables/stopping-system-versioning-on-a-system-versioned-temporal-table?view=sql-server-2017

Data Captured - Project

Data Captured - SharePoint

SharePoint lists can be synchronised to the DataStore database. Lists from the root PWA site collection and from Project Sites for Projects in PWA or other site collections on the tenant. The following SharePoint events are used:

Data synchronised to the WSS_{list title} tables in the SQL Database.

SharePoint Configuration Change Considerations

Certain changes in SharePoint will cause data sync jobs to fail in the DataStore service. Always fully test changes including DataStore syncs, ideally on a non-production PWA site collection but at least on one Project Site, before rolling out the change to all Production Project Sites. The example scenario below will cause a DataStore sync error for the SharePoint list:

1. DataStore is set up to sync the "Benefits List" and has synchronised data for all project sites
2. On the "Benefits List" there is Single line of text column called "Benefit Value" with an internal name of "BenefitValue". Users have been entering data into this column on the list
3. It is decided that the "Benefit Value" column on the "Benefits List" should really be a Number column, the column type for "Benefit Value" is updated from a Single line of text column to a Number column in SharePoint

This will cause a sync error in DataStore as the DataStore service has a column in the SQL database called "BenefitValue" that is an Nvarchar data type as this column was created when the "Benefit Value" column was a Single line of text column in SharePoint. When DataStore attempts to sync the data from the "Benefits List" list after the "Benefit Value" column has been updated to a Number column, the service will see that this is a new column / data type. DataStore will then attempt to create a new column in the SQL database called "BenefitValue" but as a Decimal data type. At this point SQL will throw an exception as there is already a "BenefitValue" column in the database and database columns in each table must be unique. 

The options to resolve this, using this scenario, would be:

1. Change the "Benefit Value" column from a Number column back to a Single line of text column in SharePoint
2. DataStore will then continue to sync data for the "Benefits List" list
3. If a number column is required, create a new Number column on the "Benefits List" list with a unique name

Removal of DataStore

If DataStore needs to be removed from the PWA site collection, ensure that the event receivers are all removed from the PWA site collection before removing the app by performing the following in the DataStore app:

List Sync (SharePoint):

• On the List Sync page click 'Suspend Sync'.

• Ensure this job completes before removing the app. The UI will update to show it is completed.

Project Sync:

• On the Project Sync page, turn off all of the toggle options and click 'Save'.