Data Mapping / Data Exchange Guidelines

Follow

One of the important steps in getting your Encompass online community configured is the review and completion of a data mapping document that mimics your database schema.  Once a data map has been completed, export of all constituent data using the defined data map is required.

TABLE OF CONTENTS


What is a data map?

The data map is the Excel document provided to you by your project manager that is pre-filled with most of the fields you will need for your Encompass database. This is a guide and should be adjusted to match your offline database. (The data mapping decisions made in this initial phase can be changed at a later date by your Encompass site Administrator if your needs, or the needs of your constituents, change.)
 

Requirements to complete data mapping for Ellucian Advance ™, Ellucian Banner Advancement™, and Raiser’s Edge™

NOTE: See data map for additional requirements.
  • Review the Export Column Header Names provided and confirm their match to your database.
    • Advance and Banner – the Export header names match the Advancement Connector and should not be changed.
    • Each time you import data into Encompass, the Import system is matching to the header name defined in the data map.
    • Each time an Export report is exported from Encompass, the Export system will use the same header names defined in the data map.
  • Remove fields you do not wish to have included (delete rows).
  • Add fields you want included as part of the Encompass schema (add rows).
  • Edit Friendly Names as needed.

Requirements to complete data mapping for other databases

If you have a different type of database that is not listed above you will receive a generic data map pre-filled with most of the fields you will need for your Encompass database.

NOTE: See data map for additional requirements.
  • Review the fields and decide which ones you’d like have in the Encompass database.
  • Update the Export header name to match/closely match your offline database field name.
  • Remove fields you do not wish to have included (delete rows).
  • Add fields you want included as part of the Encompass schema (add rows).
  • Provide Max data length for each of the fields which matches your offline field size.
  • Update the Export header name to match your database field name.
    • Each time you import data into Encompass, the Import system is matching to the header name defined in the data map.
    • Each time an Export report is exported from Encompass, the Export system will use the same header names defined in the data map.
  • Edit friendly names as needed.

Fields to include

You should only provide fields that enhance constituent interaction on the site (not your entire database).
Additional data collection fields can be defined but should be limited to those needed for the Encompass database. Encompass site Administrators will have rights to add fields as the need arises.

NOTE: Your Encompass data base schema can be accessed using this Navigation:  Data > Import Member Data > Review iModules Schema.  Please note that this will include all fields in your database (across modules/forms). Use this a reference to confirm which export header names are defined within Encompass.

File Format

How you provide the data to us is important in ensuring that the data is uploaded into our database correctly.

  • It is required that the data file be in a comma delimited standard ASCII format.
  • The first row should be the header row containing export header names as defined in the ‘Export Column name’ of the data map.
  • The file should be comma delimited: .CSV/.txt.
  • You can use double quotes as a text delimiter to manage random commas (e.g., Constituent_id, first_name, last_name: 12345,”Mary,Ann”,Morgan)
  • A carriage return at the end of each row indicates the end of a particular record.
  • The order of the field names does not matter. The name of the file does not matter. Use double quotes where the data set includes commas
  • The data file should have one header row and each subsequent row would represent a unique constituent record.  Here is an example of a properly formatted text file:

    Constituent_id,first_name,last_name,email_address
    12345,Mary,”Morgan,Jr”,mmorgan@state.edu

Pre-launch– Data Exchange

This is the provision of constituent data and any implementation related files prior to site launch. The initial data file will include all fields and records and is referred to as an 'Initial load file’ or ‘Refresh file’.  This will be submitted through a secure FTP location: Officesftp.imodules.com. A username and password will be supplied by your Project Manager or Account Manager.

Who should be included?

  • Alumni / Advancement constituents
  • Encompass Site Administrators – staff that will need access to Encompass

Other optional communities to include:

  • Parents
  • Friends
  • Students
  • And any other constituent types you’d like to access from the online community

Post Launch – Data Exchange

This is the import of updates made in your offline database into Encompass, and export of any online updates from Encompass after the site has launched. 

Who should be included?

The update files should contain any records updated since the submission of your refresh file or last update file, including new records (e.g. new alumni). You will need a system in place that utilizes a 'last_updated' date so you are only importing updates since the last import.

Update Types

There are 3 types of updates we expect to receive from you after we receive the final refresh data file:

  • Updates to existing records
  • New records/adds
  • Deleted records

Updates to existing Records

 Records are assumed to be updates if they exist in the Encompass database based off the Constituent ID

New Records/Adds

 Records are assumed new if they do not exist in the Encompass database based off the Constituent ID

Extract an updates/add file

  • Pull entire record as long as there’s a change in one of the fields

For example: A constituent has changed their last name from Smith to Morgan. The file would show:
Constituent_id, first_name,last_name
12345,Mary,Morgan

  • Pull only records and fields that have a change

The import file will have blanks (or other data marker) to indicate no change.
Use of Import data markers in Encompass Import settings is required so that the Import tool will skip the blank (or other data marker) .
For example: A constituent has changed their last name from Smith to Morgan. The file would show:
Constituent_id, first_name,last_name
12345, ,Morgan

Note:   First name is blank; you can utilize a data marker setting during import.

More information: Import Member Data Tips & Tricks

Deleted Records

Import a list of constituents to delete.

What does it mean to delete a record from Encompass?

The Encompass system allows you to flag records as deleted. Records that are deleted are excluded from ALL site functionality and can only be retrieved by iModules Application Support.  Deletes can be included in the same file as the update and add records; however, it is recommended that a separate import file be used for deletes to prevent addition-deletion of the same record each time the record is included in a file. 

NOTES:

  • Records cannot be purged from the database
  • More than one import of the same record for deletion causes the record to be created each time, leading to existence of many "duplicate" records flagged as deleted.
  • An Encompass site administrator can manually delete records from Encompass.

What is expected in the ‘Deletes’ Import file?

Include the Constituent_ID and the Encompass system field is_deleted with a value of one (1) to delete the record.  Here is an example of a file to delete constituents from the site:

Constituent_id,is_deleted

12345,1

Import Utilities

Encompass Import utilities can be accessed using several different methods. The iModules import utilities are intended to be used for incremental updates in order to keep your iModules Encompass database in sync with your offline database.  Full data base refreshes (all records/all fields) are NOT permitted without coordination with your project manager or account manager.

Manual Imports

  • Accessed via the Import tool user interface in Encompass.
  • You must be an Import Admin to utilize this import utility.
  • You must attend import/export training to gain import rights.
  • This is a queue system: First in first out. 
    • See Import queue/History for status.
  • Import starts at :35 after the hour and continues until the queue is clear.
  • Used to process updates/adds - NOT refreshes. Refreshes are allowed via SFTP with coordination with your Account Manager

More information: Importing Member Data via the Manual Import Tool

SFTP Imports

  • Automated Secure File Transfer Protocol (SFTP using SSH

  • An understanding of the Manual Import/Export process is required.
  • An SFTP account – A unique directory on the iModules secure server will be provided.
  • The client will provide import settings so that iModules can set up auto SFTP configuration – i.e Import rules.
  • You can supply your own RSA password, Preferred public key is: rsa.pub.
  • Files delivered via SFTP are picked up twice daily and queued for processing starting at 5.30 AM and 7.30 AM Central time.
  • The Import process is a queue system:
    •  See the Import queue/History for status.
  • Email notification is sent to Admin defined in the import tool configuration file
  • Used to process updates/adds  - NOT refreshes.  Refreshes are allowed via SFTP with coordination with your Account Manager.

More information: Importing Member Data via Automated SFTP

Web Services

  • Client must build the middle ware for web services.
  • Instantaneous data updates – occurs within seconds to a few minutes.
  • Additional cost is associated with this service.
  • No file transfer is required; access the database at any time except during blackout hours.
  • Used to process updates/adds  - NOT refreshes.

More information: iModules Web Services 2.1 - Engineering Documentation

Export Utilities

Manual Exports

  • Import/Export training is required.
  • Can have multiple export reports.
  • Can create on-demand or scheduled reports.
  • Standard member Export – All current data
  • Updated fields export
    • Based off 'last updated' date for profile
    • Field level changes
    • Blanks indicate fields that are unchanged or that a value was removed from the field
    • A value indicates the updated value
    • You can request an ‘Unchanged data marker’ setting to help distinguish between blanks meaning old value was removed, and blanks meaning the old value was unchanged.

More information:  Standard Member ExportUpdated Fields Export - Run ImmediatelyUpdated Fields Export - Scheduled

SFTP Exports

  • Import/Export training is required.
  • Can have multiple export reports.
  • Can create on-demand or scheduled reports.
  • Standard member Export – All current data
  • Updated fields export 
    • Based off 'last updated' date for profile
    • Field level changes
    • Blanks indicate fields that are unchanged or that a value was removed from the field.
    • A value indicates the updated value.
    • You can request an ‘Unchanged data marker’ setting to help distinguish between blanks meaning value was blanked out/removed, and blanks meaning value was unchanged.
  • The file naming convention for SFTP Export/Reports is [Date_Processed]_[Export_Report_Name].csv

    The date will be an 8 digit number (YYYYMMDD) and any spaces in your file name will be replaced with an underscore.  For example, a report processed on May 5, 2010 would be named as follows: 20100505_Updated_Fields_Export.csv

More information:  Standard Member ExportUpdated Fields Export - Run ImmediatelyUpdated Fields Export - Scheduled

Web Services

  • Import/Export training required.
  • Client must build the middle ware for web services.
  • Data extract happens within a few seconds to a few minutes.
  • No file exchange; access the database at anytime except during black out hours.

More information: iModules Web Services 2.1 - Engineering Documentation

 

Have more questions? Submit a request