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

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

FileMaker 12 Review – Part 1

We’ve been running the FileMaker 12 family of products since August 2012. These are my impressions and experiences in a real world, live development environment. My experience is taking an existing FileMaker 11 solution and converting it to run in FileMaker 12. I wrote this up a few months ago and have noted a few updates since then. I will have a followup post with any new developments or findings soon. My findings are based on FileMaker Server (FMS) running on a Windows Server 2008 with both FileMaker Pro clients on Mac and PC. My workstation is a Mac, so many descriptions are based from FileMaker Pro on the Mac.

The Good

  1. Overall, bottlenecks are fewer. FMS seems to manage multiprocessors much better. And, the extra cache seems to help.
  2. Progressive backups and hard links really help solve a huge bottle-neck.
  3. The ability to control and restart specific processes on FMS is huge. This is really helpful if a server-side script is hung. The users never know I restarted the script process and I’m back in business.
  4. CSS, themes, fewer objects, new button controls, image fills, etc. are good things, although I’m just now beginning to learn and use.
  5. I’m trying to find other positive things about FMP client, maybe it’s ExecuteSQL but that’s a big nut to crack, see conclusion. I just have to keep reminding myself that this is version 1 of a new file format and good things will be coming.

Problems, Issues, Complaints

  1. Temp files haven’t really changed. What goes to temp, still goes to temp. Although the cache has increased considerable, things like imports still go to temp files, among other things.
  2. Things are sluggish, but it’s hard to explain. I’m not sure if it’s due to the “classic” custom theme due to conversion or not. Maybe when I redo my layouts, it will be better. Here are some of the slow areas, IMHO.
    1. Switching layouts.
    2. Going to Manage Database (this is not consistent and seems to be file related). Also seems like it’s more of a delay (with beach ball) the first time you enter. I also notice this in other development areas when you click OK to a calc or to a script. Just seems like saving schema changes are slightly paused or delayed.
    3. Same is true with layout work. Just switching to layout mode may cause a delay or beach ball, and saving it too. Again not consistent.
  3. Our overnight routines just take longer. I was hoping FMS 12v2 would solve this with the import issue being fixed. It did help, but only by about 30 minutes. So, it’s still a good hour longer than FMS 11. UPDATE: By the way, FMS 12v3 has not improved this either. I guess the good news here is, I rewrote my overnight routines, so it forced me to think about optimizing my code.
  4. There are problems with layouts after conversion. Not everywhere, but some elements get shifted. I’ve had to adjust a few summary sections in reports.
  5. Some sub summaries don’t update on a re-sort. I had to put in the old standby of Browse Mode -> Preview Mode -> Browse Mode. UPDATE: The FMP 12v3 updater solved these problems.
  6. I have sporadic problems in both lists and portals where clicking on a button selects the record above. This is extremely annoying and I cannot pinpoint when it happens and how it happens. I’m thinking it has something to do with the status bar if you show the status bar and the layout shifts down it’s not updating all the elements on the layout. This was a problem early on when FileMaker changed the position of the Status bar from the left to the top . UPDATE: Seems like the FileMaker 12v3 updates have solved these issues.
  7. I don’t think FMP 12 likes XP as well as Windows 7. Seems to be more problems on my XP machines that in Windows. Specifically font issues and FileMaker Crashing when saving a report to PDF. It’s more proof that XP is at the end of it’s life cycle.
  8. PDF files are a general issue, from bloating to crashing. Just MAKE SURE all your scripts are updated to save in Acrobat 7 or higher. If not, you’ll have PDF bloat of 10 times or more of the size the file should be. I think some of the crashing issues may be due to layout issues or Trebuchet! UPDATE: I’m not positive about layouts causing crashing and our crashing issues seem to be less and less now.
  9. Layout tools and behavior is taking some adjusting, but I’m sure I’ll get used to it. I just wish somethings had been thought out better. Here are a few annoying ones (UPDATE: This is not much better after some extensive use):
    1. Drag copy is totally bastardized – wish we had constrain back. Why can’t it at least snap to the grid when dragging? And, don’t decide to click the option key after you’ve positioned the element, it won’t copy. You must click the option key while the object is moving.
    2. If we have snap to grid turned on, why can’t the guides snap too?
    3. Selecting a guide and being able to enter it’s position numerically would be helpful.
  10. Themes – someday these will be great, but much improvement is needed. Some thoughts:
    1. More themes and customized themes, need to be able to create your own.
    2. Apply themes to multiple layouts and then manage these as a theme group. Change the group theme and all layouts get updated.
    3. Would love to have Custom Styles more like Quark and Pagemaker where we could attach a custom style to elements, edit this style and all the elements are updated across all layouts, etc. that have that style.

Conclusion

Because some of the major bottle necks are gone, specifically with Finds and other areas related to server, the “sluggishness” is tolerable and a trade off. I hear less grumbling. I really wish we had FileMaker Pro 11 with FileMaker Server 12. I really think FMP 13 is going to be what 12 should have been, but we’ve been down that road before.

ExecuteSQL

I really tried to put this in practice for an addition to a report we put in place last week. Specifically, calculating the response rate based on Nielsen Household Demo impressions and calls received from commercials running on the networks. Thanks to Kevin Frank and his blog for great ExecuteSQL examples and a good place to start learning. I still need to wrap my head around this a bit more and I just wasn’t successful in a real world application. I found it too slow to calculate what I needed and it’s so un-FileMaker like. I ended up going back to the traditional FileMaker route and adding a few more fields and some more TOs than I really wanted, but the end result is quick and solved the need. I’ll try to revisit when I can carve out some time. I just may have had tables or statements not in the right order or something. I see the application for using ExecuteSQL for some things, but it’s not a be all and end all and you have to be very careful not to make it too static. We love to change field and TO names without having to think about the impact and ExecuteSQL will break if you do and you didn’t make the code dynamic. UPDATE: I’ll have a separate post soon on ExecuteSQL since I have used it since I wrote this, but I feel it only has specific uses.

Post to Twitter

FileMaker Server, Please Abort Cancelled Finds!

I’m usually not one that likes to rant or get on a soapbox. I find this unproductive and I’m not really sure it does much to contribute, plus I feel that most readers don’t like a complainer. However, there is one topic that I’m in a uproar over.

Whenever a user cancels a find using FileMaker Pro Client in a FileMaker Server hosted environment, FileMaker Server does not cancel and abort the find. No matter how long it takes, FileMaker Server is going to complete the find. Even if the client force quits or gets disconnected from the FileMaker Server. You cannot even use the FileMaker Server Admin Tool to “force” close the offending client. If you do, or if the client has force quitted FileMaker Pro client, they will still be visible under the Admin Tool under clients until the find is complete.

What makes this worse is how FileMaker Server works with multiprocessors, especially in a Windows Server environment (note: I have not tested this much on a Mac Server). FileMaker Server hands off specific tasks within a table to a free core. For example, if you have single processor dual core machine and you perform a find in table A, FileMaker Server is going to utilize one of the cores until it finishes the task. And, any other find request that comes in on Table A will be queued until the first Find is complete. It will not pass over the second request to the other core, even if it’s free. However, a different task or a Find taking place in a different table will utilize the second core.

So, if a user performs an unindexed find in Table A, they are going to bog down that entire table until the find is complete. When you are talking millions of records and Table A is a busy table with all users working in it and performing searches, this can have drastic results. Daily, I use a high-end server with 4 dual-core processors (8 cores), and I’ve flatlined (maxed out) one processor core with an unindexed find on a heavily used table and I brought the system to a grinding halt. I was using less than 5% of the processing power and the other 7 cores were free and ready to work.

I was doing a search in order to perform some maintenance routines on a set of records. Once I hit find, I knew I was in trouble. It was a mistake and I wanted to constrain the find on a smaller set of records, but I hit perform find instead. There was no way to cancel. I hung up the entire company. Two hours later, we were back up. Not fun!

Post to Twitter

Import Fails on FileMaker Server

Yep, it got me last night. I wrote a script to import data into a reporting system (post coming soon on optimizing reporting). It worked fine on FileMaker Pro client, but failed when I implemented the script to run server-side. I thought I would add the routine to the nightly updates so any changed or new entries would automatically get updated into the Report System overnight. It’s all part of optimizing reporting so the user doesn’t have to wait for large updates to run when generating a report, only changed or new data.

So, why did it fail? FileMaker Server can’t import from one table to another when the files are hosted. Bummer and I forgot this. I’ll be re-writing the script to follow an export and import routine. Steven Blackwell and Wim Decorte wrote a great post on Server Side Import and Exports that I’ll be sure to follow. It helps clarify what can and can’t be accomplished. For the knowledge base article on the subject, check out ID 7035.

 

Post to Twitter

Best FileMaker Server RAID Configuration

RAID 10 is recommended for FileMaker Server, a combination of redundancy and mirroring and gives the best performance.

If you have RAID 1, mirrored, and separate RAID 5 or 10, the OS should be on the RAID 1 and FileMaker and the Live databases on RAID 5 or 10. The backups should be on a separate disk and channel to take advantage of full async read/writes. You might have better performance making your backups to the RAID 1 drive with the OS. If anything, just use the RAID 1 for backups and leave the OS, FileMaker and the live files on the RAID 5 or 10 drives.

Having the FMS application, backups and logs on a separate partition on a RAID drive does nothing for performance. A partition does not mean separate disks.

UPDATE:  Recently, I did a lot of work with Temp Files, RAM Drives, RAIDs and SSDs. I’ve changed my recommendation on what I wrote here regarding the OS on RAID 1. The ideal setup would be OS, FileMaker Server, and Backups on RAID 10 with the live databases and database temp files on an SSD RAID or SSD PCI Card. Actual FileMaker Server backups happen to a RAM Drive and then a script moves those files to the RAID 10. If you are using FileMaker 12, Progressive backups negate the need for backups to a RAM drive. However, the progressive backups should be located on the RAID 10 volume and not on the SSD where the live databases reside.

Post to Twitter

Configuring FileMaker Server Cache

Fine tuning FileMaker Server cache can greatly improve performance in a large or heavily used solution. So, what is the optimal cache setting? I find that the general rule for setting cache is to set the RAM to the recommend setting that FileMaker Server suggests. I would leave the interval set at the default 1 minute, increasing the interval could leave you at a greater risk of loosing data.

Depending on what your solution is designed to do and how active the solution is, and whether you are writing or reading more data, you may be able to adjust the cache to tweak performance. Think of it this way, if you are writing more data by doing more edits or creating more records, then a smaller cache size may improve performance. Smaller chucks of data are written at a time. If you are reading more data, like running reports, then a larger cache size may improve performance since more data will be accessed from RAM instead of having to keep reading from disk.

In Windows Server 2003 or 2008, I recently confirmed that the memory usage on fmserver.exe is directly related to cache size. Whatever the cache is set to, that will be the minimum memory usage plus whatever extra ram is needed by FMS. The cache memory is reserved, regardless if it’s filled up or it’s empty. It kind of works like a RAM disk.

If you pop up Task Manager and click on the Processes tab and find fmserver.exe, watch the memory usage. Then go to the Admin Console, Configuration, Database Server, Databases and change the Cache size and click save. Watch the memory usage for fmserver.exe and you’ll see it change as you change the cache. There is need to restart FMS. Now you can tweak the cache setting until you find the optimum performance.

Post to Twitter

FileMaker Server Bottlenecks

One of my clients is running FMS on Windows 2003 Server on a maxed out box that is extremely fined tuned. I rarely see the processors over 7%, although at times one of the 8 cores maxes out. The same goes for memory and networking. Although network usage does see more use. Disk usage is active, but I’m running on RAID 10 with 15K disks and backups are done on a RAM disk which an OS script then passes the backups off to an entirely separate hard drive system. In my opinion, FileMaker does not do a good job taking advantage of multiple processors.

It’s all about how FMS queues requests on specific files and one unindexed search, or something else that takes processing power like running a report with unindexed calculations, can bog down an entire group of users. If a particular file is extremely active and you run an unindexed search, that file is handed off to a single core to process one task at a time. You can just imagine what can happen here if you’re at the end of a long queue. I cannot remember if it’s one file or one table within a file and I have not done enough testing here to confirm. But, this is why in a system with multiple modules (and files for each), a solution may “seem” slow in one area, but not in others.

Even a table with hundreds of fields is much better split into smaller tables each with fewer fields since server passes all data in all fields from a single table over to the client when the client has to process certain requests. This can be another major bottleneck.

Post to Twitter

Prevent Error 101, Server-Side

When running a script to loop through records and you have the Exit after last checked, you get a 101 error message, record is missing. I “understand” this is supposedly expected behavior, however to me it’s an erroneous error since you have the box checked to exit after last. It’s kind of like “duh, I know the next record is missing, that’s why I’m exiting this loop.”
This is a problem when running the script server-side. The schedule indicates an error and the log viewer shows “FileMaker Script Error.” The script actually performed OK and was successful, but reported the 101 error and shows the error status.
I tried trapping for error 101 and then just exiting the script, but the error was still reported to the FMS log and displayed in the Schedule Window as “FileMaker Script Error.”
The only way around the error is to put a condition to check if you are on the last record, something like:
Exit Loop If [Get ( FoundCount ) = Get ( RecordNumber )]
Put this exit loop condition right before the Go To Next Record script step.

Post to Twitter