Changes

{{Banner-Billable}}

=Overview=

The following article outlines the procedure for migrating historical information from an external source into SmartSimple during the initial implementation of your system. Data migration involves the extraction, transformation, and then loading of data from one system into another.

'''Note:''' The migration of your historical information is not necessary for a successful implementation of SmartSimple, and is not recommended for clients without the appropriate resources. There are exceptions to this however, where SmartSimple will assist or take on the full responsibility of the data migration for you. These exceptions are listed below.

=Exceptions=

==MicroEdge GIFTS Clients==

Clients transitioning from a MicroEdge GIFTS offline system may provide the full GIFTS database without performing any of the migration tasks listed below. SmartSimple has a standard procedure for directly transforming a GIFTS database into an appropriate format for import.

==Simple Datasets==

For clients with a sufficiently small or simple set of data, SmartSimple will assist you with the migration tasks listed below or even carry out the full data migration for you.

=Migration Process=

==Prerequisites==

* You must have resources with the authority, and expertise capable of determining which historical data need to be migrated.

* You must have resources with the ability to review, and ensure the data integrity of historical data.

==Client Responsibility==

===Review of Current System===

You will first need to determine which information you wish to migrate into SmartSimple. While it may be tempting to transfer everything, it is recommended that you review your data carefully, and only transfer what is essential. The exercise of reviewing your current data will better inform your requirement specifications while designing your new SmartSimple system. This translates to a better implementation of, and successful transition to, SmartSimple.

===Review of Data Integrity===

You are responsible for ensuring the data integrity, and quality of data that is imported into SmartSimple. It is recommended that the remediation of any issues be performed directly on your previous system prior to data export. This will help to improve the repeatability of the data extraction process. For a successful migration, you will need to ensure the following.

'''Required:'''

* ''Entity Integrity'' - every record set must possess a unique identifier for each record. This can be comprised of multiple columns. For example, for contacts, emails must be unique and contain no duplicates.

* ''Referential Integrity'' - every related record set must be linked by their respective unique identifiers. For example, a contact associated to an organization should be indicated by the contact record containing the unique identifier for the organization.

'''Recommended:'''

* ''Other Field Formatting'' - if you wish to validate and enforce formatting conventions beyond the strict data types above, you will need to ensure that your data is similarly formatted. For example, if you wish to format phone numbers to '(###) ###-####', or zip codes to '#####-####' your data must be similarly formatted first.

* ''Picklist Integrity'' - for any fields that should be restricted to a pre-defined picklist of values, you should ensure the integrity of these field values. For example, address states should all be the full name, e.g. 'New York', or all be the abbreviation, e.g. 'NY', but not a mix of both, and not contain spelling errors or typos.

===Data Extraction===

* Plain Text Files (CSV, XML, JSON formatted)

* Microsoft Excel

* Microsoft Access Database

* Oracle Database

'''Recommended:'''

* Export using UTF-8 character encoding to avoid encoding issues with special characters and language support.

* Convert any ''NULL'' values instead to empty values to avoid importing 'NULL' as a text.

'''Constraints:'''

* If providing data in plain text format, please ensure strict formatting is adhered to with the escaping of delimiter characters. For example, a CSV should have all values encapsulated by double quotes and escape any quotes that exist within a value.

The below is an example of a correctly formatted 3-column CSV that accounts for line breaks and escapes embedded quotes.

"Oranges Inc","4321","Peel.

Eat.

===Data Transformation===

====Data Schema====

'''Required:'''

Data must be provided in a normalized form such that the information for each record type be represented in separate tables or files. Typical record types are defined by the following tables or files, but are not limited to:

* Organizations

* Organization Addresses

For example, a common guideline would be the below.

* All contacts must on one worksheet with a unique identifier for each contact and a matching identifier for their organization (for those associated with organizations)

* All organizations must be on one worksheet with a unique identifier for each organization.

'''Constraints:'''

* Maximum number of columns in any given table or file should be limited to under 100. If you require more than 100 columns, then this should be represented by multiple tables or files that each contain the unique identifier that identifies the record.

====File Attachments====

In order to migrate file attachments into SmartSimple you will need to provide two items.

'''Required:'''

* A zipped file of all files attachments to be uploaded.

* A mapping file to match files to records. This will simply be CSV sheets that describe which record each file should be uploaded to. The CSV should contain columns for the unique identifier of the record to upload to, the record type (e.g. organization, contact), and the relative file path for each file.

fileattachments.zip

An appropriate mapping file could look like the below. The unique identifier column being a reference to the unique identifier of each record.

{| class="wikitable"

|-

|-

|-

|-

|-

|-

|-

|}

'''Constraints:'''

* File size of each individual file <= 1GB

* Length of full path description < 255 characters

==SmartSimple Responsibility==

===Data Transform===

As the project team designs your SmartSimple system and architecture, we will work closely with you to design the final data translation to migrate your data into SmartSimple. SmartSimple will create a transform mapping and review this with you.

===Data Load===

During the course of a project implementation there will be two data loads conducted. One at the beginning of the project to allow you to verify the information that is input into SmartSimple. A summary report will be provided to you to review.

The second data load will be at the end of the project for the final migration from your current system to SmartSimple. For this final data load, you can expect between a 3-5 day maintenance window where we ask you to freeze any updates to your current system in order to cut over and transition to SmartSimple.

[[Category:Data Import]]