ETL best practices

This section describes some of the best practices for managing the ETL transformation process for converting your system data to CSV files to include in the ZipSync archive. Some of these best practices are general ETL best practices, while others are specific to using the EPIC tool in ZipSync mode.

Check the license threshold before updating xMatters

Ensure that the xMatters installation has enough available licenses for the users you are importing into the system. If you run out of licenses during the import process then some of the users will not be imported. Count the number of users that you are adding to xMatters and compare that number to the number of available licenses.

If you do not have enough available licenses to complete your import, perform the following tasks:

  • Check for duplicate Users and remove them.
  • Check for people that have left your organization but have not been deleted.

Remember that Users consume a license in xMatters even when marked as inactive. If you need more licenses, contact xMatters Sales.

Check the user loss threshold before updating xMatters

You can avoid accidentally deleting a large amount of data from xMatters by checking the number of Users you are about to delete before committing your changes. This is especially important if you are using mirror mode, as xMatters deletes data that is not specified by the update. If you update xMatters in mirror mode and accidentally transmit only some of your data, the remaining data will be deleted from xMatters.

You can define a user loss threshold for your organization. Determine the typical number of people that leave your organization over the time between updates. This number will depend on the size of your organization, how often you update xMatters, and how often people leave your organization. For example, a small organization that updates xMatters on a daily basis may expect only a few Users to be deleted at a time. A large organization that updates xMatters on a weekly basis might expect to lose dozens of Users during an update.

Once you determine your user loss threshold, you can compare the number of Users in your update with the number of Users from the previous update, and stop the process if the number of deleted users appears to be too high.

When you make updates in mirror mode, log the number of users that are transmitted in each update. You can then compare number of users in the current update to the number of users in the previous update to determine how many users will be deleted.

Log errors and send summary reports

By keeping statistics about each step of the ETL process, you create a baseline of valid data that you can examine when errors occur. Record the amount of data that is processed, and log every error and piece of invalid data that is discovered during the ETL process. If you find errors in the source data during the ETL process, notify the person responsible for maintaining the source system so these errors can be corrected.

Log the following statistics for each type of item, including Sites, Users, Custom Values, Supervisors, Devices, Groups, Teams, Team Memberships and User Attribute joins:

  • number of records processed
  • number of added records
  • number of deleted records
  • list of records that contain errors

Log the results of the following processes:

  • Creating each file for the ZipSync archive. Ensure that the file exists and contains the expected number of records.
  • Return code from running the EPIC tool.

Record the following errors, and send a summary of these errors to somebody who can correct the information in the source system:

  • invalid email addresses
  • invalid phone numbers
  • Users with missing Sites
  • Sites with missing country information
  • Email Devices with no User key
  • Voice Devices with no User key
  • SMS Devices with no User key
  • Custom Values with no User key

You can view the log file of the EPIC tool at <epic installation directory>/log/epic.log.

Test the upload before committing

You can use the EPIC tool to validate the ZipSync archive without uploading it to xMatters. Run the EPIC tool with the -v or --validate option to validate the upload without actually importing records; for example:

epic file zipArchive.zip --validate

epic file zipArchive.zip -v

Validate results

Validate that your data import has been completed successfully with the expected results:

  • View the Synchronization Report by logging in to xMatters and navigating to the Reports tab.
  • Verify that the number of Users and Devices added to xMatters appears to be correct.
  • Review the logs created during the ETL process and look for errors.