Experiences of a FileMaker Pro Database Developer

FMS 15, ESS & AWS Redshift

This is part 2, configuring for Windows and FileMaker Server. Click here for Part 1 and configuring for a Mac.

fmsAmazonredshift_220x110Now, to get FileMaker Server running on Windows 2008 configured for ODBC access to Amazon Redshift. I first tried the specific Amazon Redshift ODBC driver and this did not work with the Actual ESS Adapter. The Adapter would not recognize Amazon’s driver. Maybe Actual can work on this. So, you will need to use the recommended PostgreSQL driver.

  1. Download the PostgreSQL driver and run the installer. The direct zipped installer can be downloaded here.
  2. After the driver is installed, go to the Start Menu, All Programs, Administrative Tools and open the Data Sources (ODBC), the ODBC Data Source Administrator.Screen Shot 2016-05-18 at 3.34.47 PM
  3. Click the System DSN tab, then click the Add button and select the PostgreSQL Unicode driver that you just installed and click Finish.Screen Shot 2016-05-18 at 3.38.06 PM
  4. I entered my server and credentials and set the SSL Mode to prefer, then left all the other default settings.Screen Shot 2016-05-18 at 3.41.37 PM
  5. Once setup, click the Test button and you should see a successful test dialog. Click OK and then Save. You are now done setting up the driver with Redshift.Screen Shot 2016-05-18 at 3.43.58 PM
  6. Next, download the Actual ESS Adapter for Windows from Actual Technologies. You will need to purchase the Server Edition for FileMaker Server for $299.
  7. After you purchase the license, download the driver and double-click the installer. When finished you should get this success dialog.Screen Shot 2016-05-18 at 2.44.56 PM
  8. Now you can jump back to the ODBC Data Source Administrator if it’s still open, or go back to the Start Menu, All Programs, Administrative Tools and open the Data Sources (ODBC), the ODBC Data Source Administrator.
  9. On the System DSN tab, click Add and then choose the newly added Actual ESS Adapter and click Finish.Screen Shot 2016-05-18 at 3.46.12 PM
  10. The Actual ESS Adapter DSN Configuration wizard will now pop-up, so first enter your license key, click OK and then click Next.Screen Shot 2016-05-18 at 2.52.12 PM
  11. Name your DSN and give it a description and select the PostgreSQL Redshift System DSN that you setup in the above stepson click Next.Screen Shot 2016-05-18 at 3.47.29 PM
  12. Then click Next if you want to set a log entry if you want to log long running queries, otherwise click Finish.
  13. Test your connection and if successful you are done configuring the ODBC setup.Screen Shot 2016-05-18 at 3.51.38 PM

The External Data Source configuration in FileMaker is the same setup as I described in Part 1, except that the Data Sources available are those defined by what is setup on the Host machine, in our case the FileMaker Server.Screen Shot 2016-05-18 at 4.21.07 PM

The above screenshot is from FileMaker Client 14 because I have not installed 15 on the Windows Server box yet. What I think this means is that the Actual ESS Adapter has been built to conform other SQL datasources to FileMaker’s ESS requirements and is just exactly that, an ESS adapter. I am not sure what this means yet and if it’s actually compatible and will work with other versions of FileMaker. Here is a screenshot of FileMaker Pro client v13 accessing the local PostgreSQL Redshift data source I configured in Part 1.FMP13_Redshift

 

There is a lot more testing needed to see if it would actually work in previous version or not. Just because I can add the table and do a simple find, doesn’t mean all things SQL will work. I need to dig deeper and find out if it was designed and created to only work with version 15. Regardless, I’m just happy that I now have a connection using ESS to our Redshift data sources.

Next, will be testing speed, adding and editing records, etc.

Post to Twitter

Comments

  1. Great post, Matt. The adapter may or may not work with previous versions of FM, but it was only tested with FM 15. The kinds of things that were tested are quite detailed (search criteria for special characters, handling of unicode characters, timestamp comparisons – the list goes on and on). The same goes with Postgres version compatibility. It may work with earlier versions (or forks of Postgres like Redshift), but there may be issues. We had a customer who reported using it with Postgres 8.2, and ran into issues creating records with Japanese text (which should work flawlessly with Postgres 9.4). The adapter won’t stop you from using these untested versions, but it would be worth your while to verify that the features you need are working with your configuration.

    Jonathan Monroe
    Actual Technologies – ODBC for Mac OS X

    • Hi Jonathan, thanks for the feedback. I know I am early testing and have a lot to do to make sure this works with Redshift. Thank you for explaining and helping me realize just because it works, maybe it really doesn’t.

  2. Hello, this is an aws dummy, ….
    I would like to know why or for what , we have to use redshift aws.
    So, is it not as another instances as MySQL ?

    • Hello Joan,
      It’s primarily for big data, large data sets with millions of records. It’s basically just a PostgreSQL database but optimized for Columns instead of Rows.
      Thanks for asking,
      Matt

Trackbacks

  1. […] This week, Greger shows how to make the connection on a 2008 Windows Server.  Be sure to read the comments for feedback from Actual Technologies on the compatibility of their driver with different versions of FileMaker.  You will need to do a lot testing before taking a solution live for a client. […]

Speak Your Mind

*