NCOA Processing

All

We've been working on our NCOA process with MBS for quite a while now and have encountered many glitches. I'll list some of them here for the benefit of all. Note that many may not apply to your situation, and the most problematic issues are, or will be, addressed in future releases from Tessitura.

Export:

Problem: Our data had hidden ASCII characters, a legacy of our conversion from Artsoft. Many fields had <cr><lf> embedded. When viewing those records in Tessitura, nothing appeared amiss. When exported however, the <cr><lf> was an end-of-record marker...in the middle of the record. That record and the next record were then garbage. MBS processing would end prematurely.

Fix: The export portion of the NCOA utility was modified to strip out invalid ASCII characters from all fields. On the first try, only the address fields were cleaned. Subsequently, all fields were cleaned. As far as I know, this is now incorporated in the current version of the utility.

Upload:

Problem: MBS had difficulties with the file that we transferred. (Wouldn't process, not all records processed, other?)

Fix: We ZIPped the file and then transferred via FTP.

Import:

Problem: Import process would not load any records. No error reported.

Fix: We manually removed the header row from the file that was returned from MBS per trouble shooting instructions from Tessitura.

Problem: Import process would not load any records. No error reported.

Fix: We used the wrong Session ID due to our confusion over which export attempt was processed and returned by MBS. The session ID is in the leftmost column in the file. Lesson: open the file in Notepad or equivalent before importing to view the Session ID (left most column).

Processing:

Problem: Many records were updated, but not close to all. After looking for patterns and discussing with Tessitura, we concluded that the date last updated in the address table was blank on all Artsoft converted records. The logic in the NCOA processing checks the date last updated on a record before updating to ensure it doesn't overwrite manual changes made between export and import dates. The logic handled comparison with NULL in the date last updated field by skipping the record. After fixing handling NULL in date last updated didn't solve the problem, the logic was further changed around handling NULL in the By: field.

Fix: Tessitura changed the NCOA Utility code to handle NULL appropriately. We now have NCOA compliant data in Test.

Further notes:

1. We did the export in Live, did a db copy of live-to-test, and then tested the import on the Test Tessitura server. Our plan is to import the file into Live from the same file when we're certain it works. This appears to be a sound and safe method. I strongly recommend that you use your test environment.

2. All of our worst data issues were introduced from outside the system. Data created inside the system can't have invalid ASCII characters, nor can there exist NULL in date last updated. So, if you have problems and ask Tessitura for help, be sure to mention where the data came from originally. Tessitura has incorporated fixes for our data in the standard release, but that doesn't mean you won't be able to invent some new twist to challenge them.

3. In hind site, it would have been better to test with a smaller amount of data (say 200 records rather than 250k - export, process at MBS, and import) to make analysis and handling easier. We might have needed to pay MBS a few bucks to process a sample, but it would pale next to the number of hours we spent. In fact, MBS attempted to process our data many times before they were successful, so it would have been in their best interest to use a small sample as well.

4. Rob Pedersen at Tessitura was very helpful. We helped him remember how software coding is not like horseshoes.

All

We've been working on our NCOA process with MBS for quite a while now and have encountered many glitches. I'll list some of them here for the benefit of all. Note that many may not apply to your situation, and the most problematic issues are, or will be, addressed in future releases from Tessitura.

Export:

Problem: Our data had hidden ASCII characters, a legacy of our conversion from Artsoft. Many fields had <cr><lf> embedded. When viewing those records in Tessitura, nothing appeared amiss. When exported however, the <cr><lf> was an end-of-record marker...in the middle of the record. That record and the next record were then garbage. MBS processing would end prematurely.

Fix: The export portion of the NCOA utility was modified to strip out invalid ASCII characters from all fields. On the first try, only the address fields were cleaned. Subsequently, all fields were cleaned. As far as I know, this is now incorporated in the current version of the utility.

Upload:

Problem: MBS had difficulties with the file that we transferred. (Wouldn't process, not all records processed, other?)

Fix: We ZIPped the file and then transferred via FTP.

Import:

Problem: Import process would not load any records. No error reported.

Fix: We manually removed the header row from the file that was returned from MBS per trouble shooting instructions from Tessitura.

Problem: Import process would not load any records. No error reported.

Fix: We used the wrong Session ID due to our confusion over which export attempt was processed and returned by MBS. The session ID is in the leftmost column in the file. Lesson: open the file in Notepad or equivalent before importing to view the Session ID (left most column).

Processing:

Problem: Many records were updated, but not close to all. After looking for patterns and discussing with Tessitura, we concluded that the date last updated in the address table was blank on all Artsoft converted records. The logic in the NCOA processing checks the date last updated on a record before updating to ensure it doesn't overwrite manual changes made between export and import dates. The logic handled comparison with NULL in the date last updated field by skipping the record. After fixing handling NULL in date last updated didn't solve the problem, the logic was further changed around handling NULL in the By: field.

Fix: Tessitura changed the NCOA Utility code to handle NULL appropriately. We now have NCOA compliant data in Test.

Further notes:

1. We did the export in Live, did a db copy of live-to-test, and then tested the import on the Test Tessitura server. Our plan is to import the file into Live from the same file when we're certain it works. This appears to be a sound and safe method. I strongly recommend that you use your test environment.

2. All of our worst data issues were introduced from outside the system. Data created inside the system can't have invalid ASCII characters, nor can there exist NULL in date last updated. So, if you have problems and ask Tessitura for help, be sure to mention where the data came from originally. Tessitura has incorporated fixes for our data in the standard release, but that doesn't mean you won't be able to invent some new twist to challenge them.

3. In hind site, it would have been better to test with a smaller amount of data (say 200 records rather than 250k - export, process at MBS, and import) to make analysis and handling easier. We might have needed to pay MBS a few bucks to process a sample, but it would pale next to the number of hours we spent. In fact, MBS attempted to process our data many times before they were successful, so it would have been in their best interest to use a small sample as well.

4. Rob Pedersen at Tessitura was very helpful. We helped him remember how software coding is not like horseshoes.