From Dwayne Wright PMP
Certified FileMaker Developer
Please Note: If you are viewing this page in a news feeder, the images may get munged up a bit or other formatting of the posting may fail. For the best experience, please visit the journal directly by clicking (here).
The Extraction Transformation And Loading Process (ETL for short) is a common term in the Data Warehousing world and focuses on how to handle mismatched imported data. Lets take a moment and break this down into its individual pieces, shall we?
Extraction - the process of getting the data from the other source such as a text file export, excel spreadsheet or another FileMaker database. It is also possible to extract data from the internet in methods such as importing, ESS connections or email downloads.
Transformation - the process of cleaning the extract data so that it fits well in the new database. For example, say the database has the contact data in one field and the destination has a first name and last name field. Part of the transformation process is to break the extracted data up properly into the two fields.
Load - Now sometimes the loading of the extracted data is done in the destination file. That is to say that you automatically load the full data into the destination database and transform it there. This is OK for very limited transformation needs. However, if your transformation tasks are more complex, we recommend you have a compiler table / file. Extract the data into this loading table, transform it, verify the format and then load it into your production database. So in a ETL process, loading is the process of taking cleaned data from a resource table and integrating it into the production database system.
Now it is quite possible to automate the ETL process via scripting. For example, say you are using an email plug-in such as 360Works Email to bring in all the emails from a particular email account. For this example, lets say the account is firstname.lastname@example.org. So the plug-in will do the extraction and place the data into a holding area. Then you isolate (and possibly transform) just the data from the order email that is used to build a new order record such as the customer, payment type and product ordered information. Then you have the loading process of taking the extracted data and placing it into your LIVE production database system for order processing.
© 2010 - Dwayne Wright - dwaynewright.com
The material on this document is offered AS IS. FileMaker Pro is the registered trademark of FileMaker Inc.