Link to home
Start Free TrialLog in
Avatar of Auerelio Vasquez
Auerelio VasquezFlag for United States of America

asked on

FileMaker as an ETL Solution

Specifically what i'm looking to do:

1. Check for the existence of a file(s)
2. if the files exist copy them to a directory (staging)
3. Import the files (they might be input by date, or may always have the same name, i haven't been given that directive specifically (still working on the requirements)
4. As in any ETL (or massage process, as you stated) i want to first to Empty all of the staging tables. With SSIS/Informatica i can simply issue a truncate table statement within the orchestration.
5. Import the files into the staging tables
6. Transform (massage) the data based on conditions say, 1. if the row exists update or delete the record. or 2. insert a new row
5. Move the data from those staging tables to production tables
6. move the files into an archive
Avatar of Will Loving
Will Loving
Flag of United States of America image

1. Check for existence of files:

You can test for a files existence - assuming you know or can calculate the name of the file - using 1) some techniques described here: http://fmforums.com/forum/topic/66020-check-if-a-file-exists/  that use native FileMaker capabilities, of 2) a plugin such as the Troi File Plugin. Because of your second item, I think you'll want to consider a plugin.

2. Copy Files

To copy/move/rename files via a script in FM you will need a third-party FileMaker plugin such as the Troi File Plugin. Because file manipulation operations are so common and easy to implement in a Plugin, many other plugins include file move/copy/rename commands in addition to whatever else they do. There are free plugins that do this but my experience with the free ones is that they are sometimes unstable and are not well supported over time such that you have to eventually replace them with something else. I've had a developer license to Troi File for 15 years and while I don't use it as much as I used to because of FileMaker's improved Import and Export options, I still do use it when I need to move, copy or rename files. It may be that you don't really need the "staging" step at all, just import directly from the source directory and then delete or archive.

3. Importing files

File imported can be scripted to use a calculated or hard coded name. The example you gave before had the file name the same each time except for the date in the name. A calculation can determine the file name for a particular date. Alternately, if you know the source directory, you can get a list of all files in the directory and then import each one.

4. Emptying tables

A scripted function that goes to a layout for each table, performs a "Show All Records" and then "Delete All Records" should take care of this.

5. Import to staging tables

Again, a scripted import can import the contents of the files into the appropriate staging table. If you are working with flat files that contains both parent and child data, you can import different sets of information into different tables, it's just a matter of setting it up. FileMaker supports Match by Field name where it looks at the first row of the import data and attempts to match columns to field names.

5. Transform data

Using script commands and perhaps other tables beyond the staging tables you should be able to manipulate your data as required. In addition to the import fields, your staging tables might also have calculation or summary fields, including calculations that reference or compare staged data to production data, and  that produces values based on the imported data that you can then, query or do further manipulation on.

7. Moving data

Depending on what your goals is, you may simply be able to run looped scripts that directly updates related records in the production data, OR, you may want to Import with Matching into your production data. You might even have steps that delete irrelevant records in the staged data before Import into Production.

8. Archive Files

Not sure which files you are wanting to archive, but I'm assuming it's the original files that were imported. Just use the Plugins "Move File" function to put them wherever you want.
Avatar of Auerelio Vasquez

ASKER

This all sounds great, i'll check the plug in. Now, i guess there isn't a corresponding orchestration tool, so my last part of this, is how do i put it all together, in a "Job" sorta speak ? Does filemaker have the ability to execute outside executable file (for a lack of a better word, i'm really not familiar with how macs do things, in a windows/sql world, you could schedule a sql job, that can call the command line utility to accomplish alot of this, or create a .net program that can be executed from SQL). Thanks!
ASKER CERTIFIED SOLUTION
Avatar of Will Loving
Will Loving
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Well, thanks again for all your help with this project. You are definately sending me in the right direction! Cheers.