FileMaker 16 - How to import data from FileMaker into Tableau

Document created by weihaoding on May 24, 2017
Version 1Show Document
  • View in full screen mode

Tableau is a leading Business Intelligence (BI) and charting product that gained its popularity among the FileMaker community in the past couple of years. With FileMaker 16 introducing the Tableau connector, Tableau can connect to a FileMaker solution utilizing the FileMaker Data API without having to export data from FileMaker then import into Tableau or relying on data relay through third party DBMS.

 

1. Prepare databases for Tableau Connector

 

Since the Tableau Connector use the FileMaker Data API to communicate with Tableau, we need to make sure the database containing the data to import meets all the requirements for FileMaker Data API. Here are the three things we want to do:

 

1.1 Create a layout for Tableau to access

 

When configuring the Tableau Connector you will need to specify a layout. So the first thing we want to do is to create a layout with a unique name pointing to the table that we want to import data from. Once the layout is created, go to the Layout Setup dialog and configure the Default view to be “Form View”, as shown in the picture below:

Configure layout default view.png

 

After that we want to place all the fields we want Tableau to access on this layout. In my case, I want to use Tableau to analyze my sales data, so I placed "sales person", "amount" and "date" fields on my layout, as shown below:

Designated layotu for Tableau example.png

Keep in mind that not all types of fields in FileMaker can be imported into Tableau. The following FileMaker field types are not supported when importing into Tableau:

  • container fields
  • summary fields. You can create a summary field in Tableau based on data that you import from FileMaker.
  • calculation fields. You can create a calculation field in Tableau based on data that you import from FileMaker.
  • chart data
  • data from FileMaker Pro portals. To import data from related records, create a FileMaker Pro layout based on the related table, or import data from separate FileMaker Pro tables into Tableau and then join the tables in Tableau.
  • field repetitions where the display of repeating fields for Show repetitions includes multiple values. A single repetition is supported.
  • non-numeric values in number fields. If Tableau finds non-numeric values in number fields, the data will not be imported.
  • Do not use reserved words as field names in FileMaker Pro.

 

 

1.2 Protect and host this database

 

The database has to be password protected. So we want to assign passwords to database accounts used by Tableau. It might be a good idea to create a designated account just for Tableau. When defining account names and passwords, use printable ASCII characters, meaning a-z, A-Z, 0-9 and punctuation characters like “!” and “%”. But do not include colons.

 

Once the database is password protected, host it on your FileMaker Server. Since this is a new feature in FileMaker 16, you need to host your database on FileMaker Server 16.

 

1.3 Enable FileMaker Data API access

 

Next, we want to give accounts access to the FileMaker Data API. To do this, go to Manage Security and navigate to Extended Privileges tab, as shown below:

 

Edit extended privilege set.png

If you have the fmrest extended privilege in there already, double click on it. If not, create on. On the Edit Extended Privilege dialog, assign fmrest extended privilege to the privilege sets with the accounts used by Tableau.

 

In my case, I created a account named “Tableau” and assigned [Data Entry Only] privilege set to it, so all I need to do is check the checkbox for [Data Entry Only] privilege Set, as shown below:

Give access to FM Data API.png

 

2. Configure FileMaker Server

 

Now we have our database file ready, let's configure the FileMaker Server. Note that the FileMaker Data API (as well as Tableau Integration) is considered a beta/trial feature for this release, meaning the feature itself, the pricing model could all change in the future.

 

2.1 Enable FileMaker Data API

 

First things first, we want to make sure we have FileMaker Server 16 installed with FileMaker Data API enabled. This can be configured in FileMaker Server Admin Console, Web Publishing section, Data API tab. As shown in the picture below:

FileMaker Server Enable Data API.png

 

As a reminder, since we are essentially doing web publishing, make sure all the ports needed are open and any port forwarding needed are configured accordingly. The default port for HTTPS connection is port 443.

 

2.2 Make sure a custom SSL certificate is installed

 

Next, we want to make sure a valid custom SSL certificate is installed on this FileMaker Serve. The configuration for custom SSL certificate is under Database Server, Security as shown below:

FileMaker Server Custom SSL Certificate.png

 

 

 

3. Import data into Tableau

 

Now that we have both the FileMaker Server and the database file ready, time to import data from FileMaker to Tableau using the Tableau Connector. You need to have Tableau version 10 or higher to do this.

 

Here's what we need to do to connect:

 

In Tableau Desktop, under Connect (at the left side of the screen), choose More > Web Data Connector. As shown in the picture below:

Open Web Data Connector Configuration.png

Once the Web Data Connector configuration page shows up, enter the URL for your FileMaker Server endpoint
https://<hostname>/fmi/rest/tableau/fm_connector.html
where <hostname> is the fully qualified host name of your FileMaker Server.

Enter end point URL.png

If everything is configured properly, you should see the Imp[ort Data from FileMaker File dialog next, as shown below:

Import data into Tableau.png

 

In the Import Data from FileMaker File dialog box:

  • Sign in to the FileMaker Pro solution by entering the following information or by using an OAuth identity provider.
  • Source Database Name: the name of the FileMaker Pro solution
  • Source Layout Name: the name of the FileMaker Pro layout that we created earlier
  • Account Name: the name of the FileMaker Pro account with the fmrest privilege
  • Password: the password for the FileMaker Pro account
  • Select Enable incremental refresh to enable incremental refresh.
  • This option might be a little bit confusing, because enabling it does NOT create an ongoing, live connection between Tableau and FileMaker. You must run the incremental refresh manually.
  • And incremental refresh imports only the NEW records. FileMaker Pro records that have been modified or deleted are not updated.
  • It also automatically creates a field named -recordId

Click Import FileMaker Data.

 

After that Tableau imports the data. The processing time depends on the number of records imported, server load, and network throughput. Tableau maps FileMaker Pro field names and data to dimensions and measures. String data is typically mapped to dimensions, while numeric data is typically mapped to measures. The mapping occurs automatically during import, but you can customize it.

At this point, the data is in Tableau and we can use it to draw charts, create dashboard within Tableau.

Please be aware of the language and locale setting in your Tableau workbook. In order for date and number data to be imported correctly, the locale setting in your Tableau workbook should align with the FileMaker data that you are trying to import from.

 

4. What Tableau Connector is not

 

 

Tableau Connector is an easy way to bring FileMaker data into Tableau. It is NOT an ongoing, live connection between Tableau and FileMaker. Even with the "Incremental Refresh" option checked, Tableau user will still need to run the incremental refresh manually.

 

Also, the incremental refresh imports only the NEW records. FileMaker Pro records that have been modified or deleted are not updated. To get modified data or to remove deleted records, you must create a new workbook in Tableau and reimport the data.

 

Further more, any change made to the data or schema in Tableau is not transmitted back to FileMaker.

 

 

5. Conclusion

Compared to old connecting methods before FileMaker 16, this Tableau Connector definitely provided a smoother experience to FileMaker users. If you are already a Tableau export, consider using FileMaker to help you collect and prepare your data in a centralized database and then use Tableau Connector to import data into Tableau. Or if you are a FileMaker user and want to conduct Business Intelligence activities with your FileMaker data, think about utilizing Tableau Connector to bring your data to Tableau for further analysis and visualization.

 

 

Weihao Ding

Senior FileMaker Developer

weihao.ding@directimpact.ca

 

Office : +1 450 663-0005
Toll free : + 1 888-558-0005

logo.pngfacebook.pngtwitter.pnglinkedin.png

     

6 people found this helpful

Attachments

    Outcomes