Last year when I wrote this blog post we were talking about FileMaker 16 and the Data API being in beta. Now that FileMaker 17 has officially been released and along with it the Data API is out of beta and is a version 1.0.
An important change to note is that if you were using Tableau to connect to your your Data using the Web Data Connector using FileMaker 16, then I am sure that you have heard about the release of FileMaker 17. Hopefully you will be upgrading soon to continue using these 2 solutions together. Two other important things to mention is. First, the Data API with FieMaker 16 will stop functioning.
The FileMaker Data API (Trial) License will expire on September 27, 2018.
The second important item is that the url has changed, so when you establish your connection with Tableau you’ll need to be sure to use the correct url. They have included a version number in it to future proof it.
https://myservername.com/fmi/data/v1/tableau/fm_connector.html
Also wanted to mention that over the course of the last year Tableau also made some significant additions and improvements that are worth checking out and getting upgraded.
Here is the original blog post on FileMaker + Tableau, from May, 2017:
FileMaker & Tableau | A Match Made in Data Heaven
It’s here and I feel like I have to tell the whole world… which is what I am doing! Now that FileMaker 16 is officially announced, there are lots of exciting things to talk about that are now possible. I think one of the most powerful new features that have been introduced in version 16 is the capability to integrate with other applications or services. There is so much to talk about what now becomes possible with FileMaker and other services/applications, starting with Tableau.
I have been a data visualization fan for many years. I’m a big fan of D3 ( the work of Mike Bostock ) and used some visualizations in InspectorPro ( force directed chart below ). We have also used D3 (and cousin frameworks like D4) in many of our client projects. The amount of control and flexibility it gives you is amazing, you can do pretty much anything you can imagine. What I find interesting is the direction this takes you in – once you see the big picture ( literally speaking ), it’s hard to go back to columns and rows.
Tableau managed to garner an almost cult following in a short amount of time, then began growing into the leader in data visualization tools. I have been spending some time in the Tableau community, meeting new people and constantly learning new things.
If there were a list of things a FileMaker developer should prioritize spending some time learning this year, I could say learn more about REST, JSON, cURL etc… but after that I think one of the most valuable things you could do with your time, is to spend some time learning Tableau.
If you have your own business and want to see insights and trends or build reports that are interactive, then you owe it to yourself to check out what Tableau can do. If you’re a consultant, then it’s a must to learn this new tool and be able to offer your customers new ways to think about what reports are or rather could be – interactive, with the ability to filter data and when you spot a trend, and then to dive back into the data.
In this regard, the combination of FileMaker and Tableau make Tableau one of the most compelling tools for integration with FileMaker. In enterprise systems, you pull your data out, and depending on how clean it is you’ll probably need to transform it in some way and then load it – ETL! But FileMaker shines in a somewhat exclusive category here, with the ability to include Tableau actions that trigger scripts using the fmp url, thus allowing you to get back to the underlying data. We covered this in another recent post, FileMaker & Tableau | Building Dashboards with Interactivity.
It’s amazing, and hope I’ve sparked your curiosity enough to continue reading on and see how you can make all this come together.
Let’s start at the beginning! And just as all Mac applications start with a “Hello World!” example, all Tableau examples start with the Superstore example.
In this case, I have already imported the Superstore Excel file into a FileMaker database ready for you to use.
Superstore:
Account Name: admin
Password: tableau
You’ll also need FileMaker Server 16, as this has support for the FileMaker Data API that Tableau needs to connect to.
In the Web Publishing section under the FileMaker Data API tab is where you’ll have to make sure that you have it enabled.
In Tableau, the URL that you enter for the Web Data Connector will look something like this:
Now that the Data API is officially out. This is the correct url for the fm_connector is the following:
https://myservername.com/fmi/data/v1/tableau/fm_connector.html
If “Enable FileMaker Data API” isn’t enabled or Tableau can’t reach the web connector, then you might see a message like this in Tableau:
The server is temporarily unable to service your request due to maintenance downtime or capacity problems. Please try again later.
For now, let’s assume that the server is set up correctly and that the FileMaker Data API is enabled.
Next we’ll need to make sure that you have an account and permission that Tableau can use to access the data over the REST API. The example file already has a layout ready for this purpose.
NOTE: Tableau will always only read your data it will never update your data. Still, always take precautions with security to secure your file.
The file has an account named “admin” and the password is “tableau” associated with a privilege set called “data”. This is what that privilege set looks like.
You could also use one of the default settings for allowing read-only access to all tables if you like but, I like being specific about what tables and layouts you are granting access to, especially if down the road you want to then continue to publish your data and people are subscribing to it with Tableau Server.
If your file is hosted and you have become familiar with the security options and extended privileges needed, then you are now ready to have Tableau connect to your file to access the data.
Launch Tableau and from the connect section choose Web Data Connector.
The next screen you get is where you provide the URL that allows you to enter the details as to what file you want to be able to connect to. This is where that account name connected to that extended privilege set will be needed.
The URL that you enter is the following (substitute mydomain.com with your FM 16 server address): https://mydomain.com/fmi/rest/tableau/fm_connector.html
Important Update now that FileMaker 17 is officially been released. The URL has changed to the following.
https://myservername.com/fmi/data/v1/tableau/fm_connector.html
Once you connect to it, then you’ll be asked for the database name, layout name, account name and password ( only the password is case sensitive ).
If you have everything set up correctly then you should see the following screen. Click update now or automatically update.
What happens next is the magic of Tableau. But you have now successfully integrated FileMaker with Tableau. The world of Tableau and all the wonderful things you can do with the Superstore data await you. If you do a number of searches online or in the tableau community you’ll find numerous examples that refer to the Superstore dataset. Before you know it you’ll be building great dashboards that will be the envy of the office. Other departments will be reaching out to you for something similar.
Adding Tableau actions to your dashboard gives you a closed loop. Perhaps you see an interesting trend in your data, but you need to correct something. Click on a bar segment which can then trigger an FMP url that performs a script to find your underlying data. Make the correction and then from Data menu Select your data source Extract/Refresh (Full). Presto! Everything will be updated.
Again I encourage you to check out the earlier blog post to building Tableau Actions using FMP URL.
To summarize what we just managed to do. We took a very simple FileMaker file and made sure we had added a layout, account, and privilege set that grants access to a particular layout. Ensure that this account has the extended privilege “Access via FileMaker Data API – FMS only (fmrest)” enabled. The file is now ready. Host the file on your FileMaker 16 server and make sure that you have enabled the FileMaker Data API option in the Web Publishing section. Then you have everything ready for Tableau to connect to FIleMaker Server to pull data out from your hosted FileMaker file using the REST API.
What you have really lit a fire under is all the possibilities that you can build, either in your organization or for your clients: solutions to provide them insights and interact with their data in ways they could not even imagine doing before. It’s all made possible now that we have one new feature called REST. And we have only scratched the surface of what 16 is all about. Integration/Revolution.
Enjoy.
3 thoughts on “FileMaker & Tableau | A Match Made in Data Heaven!”