Difference between revisions of "Data Migration Guide"

From Charitylog Manual
Jump to: navigation, search
m
 
(8 intermediate revisions by 2 users not shown)
Line 1: Line 1:
Data Migration is the process by which data is imported into a new system. This may take the form of simply typing in records where there are only a few pieces of data to enter, or where large amounts exist, creating an import program to import data stored in a database or spreadsheets.
+
#REDIRECT [[Data Migration]]
  
==Should you automate the import of your data?==
+
==About data migration==
  
One of the most important factors in deciding to have the data imported is the volume of data. If there are hundreds or thousands of records then it is worth creating a program to import the records. Conversely, if there were only 20 records or so, it is better to have them manually imported. Volunteers may be able to help.
+
It is usually the case that an organisation buying Charitylog/Crossdata is working from a number of different systems. One project co-ordinator may be using an Access database, while another is using a series of Excel spreadsheets to store their data. Some projects, or even whole organisations, may still work on a paper filing system.
  
Another factor to consider is the cleanliness of your data. A manual import can be a good time to cleanse data. Cleansing data is a term used to describe the process of removing duplicated records, or correcting misspellings etc. This would apply to data where duplicates had appeared in the system (for example “Mr Richard Paul Smith” and “Mr Richard P Smith” have been entered in a Customers Database). Equally, it is possible for misspellings to be an issue. An example of this might be where a client has “Altzheimers” rather than “Alzheimers” against their record.
+
Data Migration is the process by which this data is imported into your new system. This data can usually be divided into two categories:
 +
 
 +
# Data about people or organisations. For example, your list of current clients, and perhaps their associated data such as date of birth, address, phone number, etc.
 +
# Data about contacts with these people. For example, a client's case history, with the details of what work has been done and when.
 +
 
 +
'''Data about people or organisations is usually easier to import correctly than data about contacts.'''
 +
 
 +
Please note that the setup of your system is not considered data migration. Lists of Projects (services), drop-down lists and so on are initially configured using your [[Pre-Implementation Spreadsheets|Pre-Implementation Spreadsheet]].
 +
 
 +
There are three common ways that you can migrate data.
  
It is not generally possible to correct such mistakes during the data migration process, so if large scale data cleansing is needed, it is better to do this manually, or approach a contractor who specialises in data cleansing.
+
===Manual data migration===
  
==Automated import==
+
The simplest way of importing your data is to have your own staff or volunteers do it. They can put clients onto your new system, add details about those clients, and even fill in case histories (the ysstem will allow you to record contacts retrospectively).
  
This guide deals with the process of automating the import of your existing data to Charitylog. This needs to be done by the Charitylog programming team, and comes with a cost (though it is very reasonably priced!).
+
===The standard data migration spreadsheet===
  
It is usually the case that an organisation buying Charitylog is working from a number of different systems. One project co-ordinator may be using an Access database, while another is using a series of Excel spreadsheets to store their data. Some projects, or even whole organisations, may still work on a paper filing system. Invariably there will be some data which can only be imported manually. From your point of view, the important thing is to get your existing data into the best possible state for migration, to ensure that the process goes smoothly and that the end result is as expected. Ideally, you will also save time, and therefore money, in the process!
+
We can provide you with a standard spreadsheet for you to put your data in. This is primarily for data about people or organisations, although you can use it to record the fact that a client is associated with a particular Project. The data entered must conform to the specification you will be given with the spreadsheet. Once you have filled in the spreadsheet, this can be imported into your new system.
  
==What can we do with our existing data?==
+
There is no limit to the number of records you can enter via this spreadsheet, so this can be a very cost-effective way of migrating your data, providing you are able to extract it from your existing system(s) and insert it in the spreadsheet correctly. Please contact the office on 01989 763 691 for pricing.
  
The preferred format to receive data for import into Charitylog is in the form of Spreadsheets (often Excel files [.xls or .xlsx]).
+
===Custom, automated data migration===
  
We can also accept Microsoft Access databases from which we can extract the relevant data, but this creates one further step in the process and so more time will be required.
+
You can also opt to have your data migrated with assistance from our programming team. This is the most complicated option and the price is entirely dependent on the complexity of the work needed, so if you are considering this option, please contact the office to discuss your options and obtain a quote (customer support: 01989 763 691).
  
To avoid any issues of ambiguity, all spreadsheets should be uniquely and clearly named, for example;  
+
The preferred format to receive data for import into the system is in the form of Spreadsheets, usually Excel files (.xls or .xlsx). We can also accept Microsoft Access databases from which we can extract the relevant data, but this creates an extra step in the process and so more time will be required. To avoid any issues of ambiguity, all spreadsheets should be uniquely and clearly named, for example;  
  
 
* Carers.xls
 
* Carers.xls
Line 28: Line 37:
 
* Doctors.xls
 
* Doctors.xls
  
This will make it clear to us where the imported data needs to be stored within your new Charitylog system.
+
This will make it clear to us where the imported data needs to be stored within your new system.
  
==Charitylog "rules" for data==
+
====The system "rules" for data====
  
 
'''Name''' - All records ''must'' have a surname, or an organisation name. Records with no name cannot be imported.
 
'''Name''' - All records ''must'' have a surname, or an organisation name. Records with no name cannot be imported.
  
'''Address format''' - Addresses in Charitylog are held in the following format:
+
'''Address format''' - Addresses in the system are held in the following format:
  
 
*Address 1
 
*Address 1
Line 43: Line 52:
 
*Postcode
 
*Postcode
  
If the address is stored as a single string, we will not be able transfer it as part of the data migration process. Therefore you will need to manually separate it into different fields, and we can create the address properly in Charitylog.
+
If the address is stored as a single string, we will not be able transfer it as part of the data migration process. Therefore you will need to manually separate it into different fields, and we can create the address properly in the system.
  
==Key Fields==
+
===Key Fields===
  
 
[[File:data_migration_1.png|thumb|right|300px|An example of a record being linked to another record by a unique number]]
 
[[File:data_migration_1.png|thumb|right|300px|An example of a record being linked to another record by a unique number]]
Line 61: Line 70:
 
It may be obvious to the user that “Dr Patterson” and “Doc. Patterson” are the same person, but as far as data migration goes, these will not be simple to import.
 
It may be obvious to the user that “Dr Patterson” and “Doc. Patterson” are the same person, but as far as data migration goes, these will not be simple to import.
  
==What happens once the Charitylog team have the required data?==
+
====What happens once the programming team have the required data?====
  
[[File:data_migration_4.png|right|thumb|300px|A diagram of the Charitylog data migration process]]
+
[[File:data_migration_4.png|right|thumb|300px|A diagram of the data migration process]]
  
Once we have the data that is to be imported we will review it to see that it meets the expected criteria. The next step for us will be to write a program to aid the import of the data. Assuming the data is in good order, this will be a fairly straightforward process cumulating in a “Test CL Database” being made available on Charitylog to you after the import is complete.
+
Once we have the data that is to be imported we will review it to see that it meets the expected criteria. The next step for us will be to write a program to aid the import of the data. Assuming the data is in good order, this will be a fairly straightforward process, resulting in a test system (a snapshot version of your live system with the migrated data added) being made available to you after the import is complete, so you can check it over for any errors or omissions. Once you are satisfied with the data contained, we will re-migrate the latest version of your data to your main (live) system.
  
The “Test CL Database” is a copy of the current Charitylog Database that contains as much background information (ethnic groups, age ranges, districts etc.) as possible.
+
==Choosing how to import your data==
  
A period of time is then allowed for the end-user to review their new CL Test Database and check for any errors or omissions. Once the end user is satisfied with the data contained we will re-migrate their latest version of their data to their “Live CL Database”. At this point the import is complete and your Charitylog system is live.
+
One of the most important factors in deciding to have the data imported is the volume of data. If there are hundreds or thousands of records then it is worth creating a program to import the records. Conversely, if there are only 20 records or so, it is better to have them manually imported.
 +
 
 +
Another factor to consider is the cleanliness of your data. A manual import can be a good time to cleanse data. Cleansing data is a term used to describe the process of removing duplicated records, or correcting misspellings etc. This would apply to data where duplicates had appeared in the system (for example “Mr Richard Paul Smith” and “Mr Richard P Smith” have been entered in a Customers Database). Equally, it is possible for misspellings to be an issue. An example of this might be where a client has “Altzheimers” rather than “Alzheimers” against their record.
 +
 
 +
It is not generally possible to correct such mistakes during the data migration process, so if large scale data cleansing is needed, it is better to do this manually, or approach a contractor who specialises in data cleansing.

Latest revision as of 11:16, 8 August 2018

Redirect to:

About data migration

It is usually the case that an organisation buying Charitylog/Crossdata is working from a number of different systems. One project co-ordinator may be using an Access database, while another is using a series of Excel spreadsheets to store their data. Some projects, or even whole organisations, may still work on a paper filing system.

Data Migration is the process by which this data is imported into your new system. This data can usually be divided into two categories:

  1. Data about people or organisations. For example, your list of current clients, and perhaps their associated data such as date of birth, address, phone number, etc.
  2. Data about contacts with these people. For example, a client's case history, with the details of what work has been done and when.

Data about people or organisations is usually easier to import correctly than data about contacts.

Please note that the setup of your system is not considered data migration. Lists of Projects (services), drop-down lists and so on are initially configured using your Pre-Implementation Spreadsheet.

There are three common ways that you can migrate data.

Manual data migration

The simplest way of importing your data is to have your own staff or volunteers do it. They can put clients onto your new system, add details about those clients, and even fill in case histories (the ysstem will allow you to record contacts retrospectively).

The standard data migration spreadsheet

We can provide you with a standard spreadsheet for you to put your data in. This is primarily for data about people or organisations, although you can use it to record the fact that a client is associated with a particular Project. The data entered must conform to the specification you will be given with the spreadsheet. Once you have filled in the spreadsheet, this can be imported into your new system.

There is no limit to the number of records you can enter via this spreadsheet, so this can be a very cost-effective way of migrating your data, providing you are able to extract it from your existing system(s) and insert it in the spreadsheet correctly. Please contact the office on 01989 763 691 for pricing.

Custom, automated data migration

You can also opt to have your data migrated with assistance from our programming team. This is the most complicated option and the price is entirely dependent on the complexity of the work needed, so if you are considering this option, please contact the office to discuss your options and obtain a quote (customer support: 01989 763 691).

The preferred format to receive data for import into the system is in the form of Spreadsheets, usually Excel files (.xls or .xlsx). We can also accept Microsoft Access databases from which we can extract the relevant data, but this creates an extra step in the process and so more time will be required. To avoid any issues of ambiguity, all spreadsheets should be uniquely and clearly named, for example;

  • Carers.xls
  • Clients.xls
  • Disabilities.xls
  • Doctors.xls

This will make it clear to us where the imported data needs to be stored within your new system.

The system "rules" for data

Name - All records must have a surname, or an organisation name. Records with no name cannot be imported.

Address format - Addresses in the system are held in the following format:

  • Address 1
  • Address 2
  • Address 3
  • Town
  • County
  • Postcode

If the address is stored as a single string, we will not be able transfer it as part of the data migration process. Therefore you will need to manually separate it into different fields, and we can create the address properly in the system.

Key Fields

An example of a record being linked to another record by a unique number

In some instances an organisation will have separate tables of data which link to one-another with key fields (numbers that relate to uniquely numbered records in a different table). In the example on the right, Joe Hughes has the Doctor number of 0125 stored against his record, and if we look at Doctor number 0125, we see that the doctor in question is Dr Marvin Monroe. If records are linked to other records (held in a different table) by a unique number, this is no problem. If they are linked by unique text, this too is not generally a problem for automated data import.

The problem occurs where the text used is simply typed in, as it will often contain errors (spelling, punctuation etc) or be different in some way. Consider this table of clients:

Data migration 2.png

And this table of doctors:

Data migration 3.png

It may be obvious to the user that “Dr Patterson” and “Doc. Patterson” are the same person, but as far as data migration goes, these will not be simple to import.

What happens once the programming team have the required data?

A diagram of the data migration process

Once we have the data that is to be imported we will review it to see that it meets the expected criteria. The next step for us will be to write a program to aid the import of the data. Assuming the data is in good order, this will be a fairly straightforward process, resulting in a test system (a snapshot version of your live system with the migrated data added) being made available to you after the import is complete, so you can check it over for any errors or omissions. Once you are satisfied with the data contained, we will re-migrate the latest version of your data to your main (live) system.

Choosing how to import your data

One of the most important factors in deciding to have the data imported is the volume of data. If there are hundreds or thousands of records then it is worth creating a program to import the records. Conversely, if there are only 20 records or so, it is better to have them manually imported.

Another factor to consider is the cleanliness of your data. A manual import can be a good time to cleanse data. Cleansing data is a term used to describe the process of removing duplicated records, or correcting misspellings etc. This would apply to data where duplicates had appeared in the system (for example “Mr Richard Paul Smith” and “Mr Richard P Smith” have been entered in a Customers Database). Equally, it is possible for misspellings to be an issue. An example of this might be where a client has “Altzheimers” rather than “Alzheimers” against their record.

It is not generally possible to correct such mistakes during the data migration process, so if large scale data cleansing is needed, it is better to do this manually, or approach a contractor who specialises in data cleansing.