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
Auerelio VasquezETL DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Will LovingPresident, Dedication Technologies, Inc.Commented:

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:  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.
Auerelio VasquezETL DeveloperAuthor Commented:
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!
Will LovingPresident, Dedication Technologies, Inc.Commented:
Just so you know, while FileMaker, Inc. IS a wholly owned subsidiary of Apple - a fact which many people don't know - FileMaker has also been available on Windows since 1993 and a majority of FileMaker users are on Windows.

The way you "put it all together" in FileMaker is to create a "script" - essentially a macro or series of steps/operations to execute - and then either trigger that script manually, set it to trigger under certain circumstances, or schedule it to run at regular intervals from FM Server. Almost anything you can do manually in FileMaker can be scripted and you can include conditional statements, temporary variables ($) and persistent variables ($$). If I were to go about what you are describing above I would create a script that would work through each part of the sequence and I would test it manually. The example file I included for your previous question contains a script in it with notes on what I did where and why.

If after you have the sequence fully working with a manually launch, you can then look at setting it to run automatically as a server-side script. When running scripts on the server, certain script steps such as "Save as PDF" are not supported and there are certain restrictions such as a limited number of location where files can be imported from, but assuming those are taken into account, you should be able to automate it.

Sounds like an interesting project and you're likely to post more questions and learn a lot about how FM works, but it sounds quite doable.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Auerelio VasquezETL DeveloperAuthor Commented:
Well, thanks again for all your help with this project. You are definately sending me in the right direction! Cheers.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
FileMaker Pro

From novice to tech pro — start learning today.