Data Integration Blog -

CR Software staff Chris Hausman, Don Rhoads and Yan Chen.

Integrating data can be hard to do, but it doesn’t have to be.  CR Software has helped almost 400 companies integrate accounts receivable data to increase collections, and streamline the collections process. This experience has illustrated some best practices that are critical for ETL success. Share these tips with the team responsible for scheduling and managing ETL for your company, for smooth and easy data migration. By implementing these 10 best practices, you’ll ensure a swift integration process, enabling integration to become more of a value-add for your business. And that’s the goal.  Wouldn’t we all like to get to the point where integration processes just happen, and we just get to reap the business benefits?

1. Use Macros – It’s a good idea early on in the development process to hard-code certain values including path, creditor or file names. Configuring your ETLs to reference macros from the very beginning will almost always save time in the long-run. It will also add an enormous amount of flexibility to your ETL, and help to promote code re-usability.

2. Use SQL Files – Referencing external .sql files instead of embedding SQL in your ETLs can save time down the road when you have to make changes to an already deployed ETL. If there are any query changes, you will not have to rebuild the .djar again; you may simply change the .sql file containing the query.

3. MacroValue Function – The MacroValue function is used in Pervasive to return the value of a given macro. This can also be used in SQL queries. The syntax is ‘DJX (MacroValue(“MACRO_NAME”))’

4. Relative Paths – Whenever you find yourself having to reference path names, it’s best to use relative paths. Most often this will occur in the macrodef.xml file. For example, if your SQL directory is in the same directory as the macrodef. xml file, you may use . SQL instead of the full path. The benefit here is that you can change the location of the ETL without having to go back and modify the macrodef.xml file.

5. .djar Re-usability – If you need to use the same ETL for multiple clients, avoid copying the actual .djar and other ETL-related files. All you need to do is create another .bat file and possibly the macrodef.xml file for the new client. This is far easier than maintaining multiple versions of the same ETL.

6. Log Reject Record – If you want to catch bad records during the ETL process, use the OnReject event handler and specify a reject-connection string in the Reject Connect Info dialog. Manually type the connection string, or you can use the buttons to do the following: Build New Connection String, Build Connection String from Source, Build Connection String from Target, or Clear Reject.

7. Installing Pervasive on a 64-bit Machine – After installing Pervasive on a 64-bit machine and running an ETL, you may get an error message such as ”Can’t load IA 32-bit .dll on a AMD 64-bit platform.” Since Pervasive Development Studio is a 32-bit application, it has to point to 32-bit Java. If you get an error such as the one quoted here, please check whether or not you have 32-bit Java installed on your machine and make sure the path environment variable points to 32-bit Java.

8. Multiple Thread Processing – Pervasive ETLs have the flexibility of running multiple events inside of a process in a multi-threading environment. This can significantly reduce the time it takes for the ETL to finish. To set up an ETL for multi-thread processing, simply drag multiple lines from the starting point, and in the Process Properties set the maximum concurrent execution threads to one more than the number of threads you want to use.

9. Emailing Through Processes – Pervasive contains the functionality to set up ETL to send emails at specifics steps in the ETL process. You can have the ETL send an email to relevant staff members to let them know when the ETL produces an error or when the ETL process is completed.

These emails are a very effective way to keep you and your staff apprised of any issues that may arise during the process and to help you keep tabs on the ETL’s progress.

10. Monitoring Email Accounts and FTP Queues – Pervasive also gives you the ability to set up a specific queue that will monitor an email account and/or an FTP queue so when a file is received via that account/FTP queue, the ETL can automatically retrieve that file and process it as soon as possible. This means that the data in that file becomes available to your system sooner than if it had to be retrieved and processed manually by a staff member.