I’m rather excited about the release of FileMaker 15. One of the hidden gems is the expansion of SQL database support within ESS (External SQL Data Sources) and the addition of PostgreSQL databases. This means FileMaker can integrate directly with Amazon’s Redshift Data Warehouse. ESS Redshift tables can be dropped directly as a TO in FileMaker’s relationship graph.
What you need:
- Actual Technologies ESS Adapter, required for Mac and Windows.
- Actual Technologies ODBC Driver for Open Source Databases if on a Mac. This is the only driver that works with FileMaker ESS on the Mac.
- Amazon ODBC Driver or PostgreSQL ODBC Driver if on Windows
How it works:
The ESS Adapter will present the Postgres data from Redshift to FileMaker in a format that FileMaker will accept as a supported ESS data source. As per Actual Technologies, here’s a simple diagram of the data flow:
FileMaker <–> Actual ESS Adapter <–> Postgres ODBC Driver <–> Postgres Database
Installing and configuring on a Mac:
You need to install both the Actual Technologies ODBC Driver for Open Source Databases and their ESS Adapter. First, setup and configure the ODBC Driver using the ODBC Manager from Actual Technologies. If you have already installed other FileMaker ODBC drivers, then most likely you already have this app. You can also use the iODBC Administrator from OpenLink Software.
- Create a new system DSN using the Actual Open Source Databases driver
- Name the data source, give it a description if you want and select PostgreSQL
- Enter the connection information but make sure not to check off the “Connect to server t0 obtain default settings for the additional configuration options.” I think there is a bug here and it tries to use a default database name that will produce and error.
- Since the connect to server option doesn’t seem to work, you need to know the name of your database, so enter it in the next screen, then choose your other options.
- Click continue, then test your connection.
- Once the main ODBC driver is setup, tested and configured, you must create another DSN using the ESS Adapter driver.
- Name the driver so you won’t get confused, I added ESS to the name, add a description and choose the real ODBC DSN entry you just added in the above steps.
- When you click continue, the next screen lets you determine if the access is read only, otherwise it’s full access as long as your PostgreSQL login allows it.
- Once done here, you are finished with the driver setup and you can do another test just to make sure everything is working.
- Next, configure your External Data Source in FileMaker. Make sure to select the ESS Adapter DSN and configure your other settings.
- Now, in manage database on the relationships tab, you can add a Redshift Table to your Table Occurrence.
So far, I have only tested and configured the drivers on my Mac using FileMaker Pro Advanced. I have been able to do finds, create records, edit records and delete records in the Redshift tables. I’m working with tables that have a few hundred records to tables with over 400,000. At first glance and try, the speed seems doable. More to come soon.
In the next few days, I plan to setup FMS 15 on our Windows Server and will then provide instructions and details. After that I will be testing various scenarios and will post the results here.