Experiences of a FileMaker Pro Database Developer

FileMaker 15, ESS and AWS Redshift

icon_fmpAmazonredshift_220x110I’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:Redshift_ESS

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.

  1. Create a new system DSN using the Actual Open Source Databases driverScreen Shot 2016-05-11 at 2.07.13 PM
  2. Name the data source, give it a description if you want and select PostgreSQLScreen Shot 2016-05-11 at 2.07.59 PM
  3. 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.Screen Shot 2016-05-11 at 3.07.02 PM
  4. 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.Screen Shot 2016-05-11 at 6.01.32 PM
  5. Click continue, then test your connection.Test_Data_Source_and_Actual_Open_Source_Databases_DSN_Configuration
  6. Once the main ODBC driver is setup, tested and configured, you must create another DSN using the ESS Adapter driver.Screen Shot 2016-05-11 at 6.15.42 PM
  7. 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.Screen Shot 2016-05-11 at 6.17.16 PM
  8. 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.Screen Shot 2016-05-11 at 6.20.31 PM
  9. Once done here, you are finished with the driver setup and you can do another test just to make sure everything is working.Screen Shot 2016-05-11 at 6.21.25 PM
  10. Next, configure your External Data Source in FileMaker. Make sure to select the ESS Adapter DSN and configure your other settings.Screen Shot 2016-05-11 at 6.24.45 PM
  11. Now, in manage database on the relationships tab, you can add a Redshift Table to your Table Occurrence.Screen Shot 2016-05-11 at 6.46.29 PM

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.

See Part 2 for configuring FileMaker Server.

Post to Twitter

Comments

  1. Matt, sir, you are a lifesaver. I had tried to build an application for a client who stores data in Redshift using FileMaker 14. No dice.

    I just ran across your post on how to set this up in FileMaker 15. So thorough, and so helpful. I’m up and running with FileMaker 15 looking at my client’s Redshift database directly.

    And now we do the Dance of Joy.

    • Hi Martin,
      Thank you. Let me know how it goes. I’ve been so busy with MirrorSync and synchronizing our FileMaker Tables with Redshift that I have not gotten back to ESS on Redshift. Let me know if you have any success editing records in Redshift from FileMaker. It was working then it stopped for me.
      Good Luck
      Matt

Trackbacks

Speak Your Mind

*