4 Replies Latest reply on May 12, 2011 12:15 PM by carmenm

    FM11 ODBC Driver not functional with Tableau

    carmenm

      Title

      FM11 ODBC Driver not functional with Tableau

      Your post

      I posted this first in the Tableau forums, but I thought I'd crosspost it here.  I'm trying to connect Tableau business intelligence software to FileMaker Server 11 Advanced via ODBC. Tableau is supposed to be able to support ODBC version 3 or higher.

      If anyone else has been able to use ODBC with Tableau I would be very interested.  I'm able to use FM's ODBC driver just fine with Microsoft Office products.  Here is my experience in Tableau:

       

      If I try to select the Driver, I get "An error occurred while communicating with the data source."

      If I select a DSN that I have already set up in the ODBC Administrator, I can get a connection with the following limitations:

       

      ======================================================================

      This ODBC driver does not support all the capabilities used by Tableau. While many capabilities are available, you may want to create a Tableau data extract for complete functionality.

      Tableau was unable to determine support for the following: TOP n / LIMIT n

      -----

      Tableau identified the following warnings for the ODBC data source named 'Student':

      Tableau was unable to determine support for the following: SELECT ... INTO

      Tableau was unable to determine support for the following: Subqueries

      This aggregation is unsupported: Attribute

      This aggregation is unsupported: Median

      This aggregation is unsupported: Std. Dev

      This aggregation is unsupported: Std. Dev (Pop.)

      This aggregation is unsupported: Variance

      This aggregation is unsupported: Variance (Pop.)

      This function is unsupported: + with parameter types 'string, string'

      This function is unsupported: ACOS(number) with parameter types 'float'

      This function is unsupported: ASCII(string) with parameter types 'string'

      This function is unsupported: ASIN(number) with parameter types 'float'

      This function is unsupported: CONTAINS with parameter types 'string, string'

      This function is unsupported: COT(number) with parameter types 'float'

      This function is unsupported: DATETIME with parameter types 'integer'

      This function is unsupported: FIND with parameter types 'string, string, integer'

      This function is unsupported: FIND(string, substring, [start]) with parameter types 'string, string'

      This function is unsupported: FIND(string, substring, [start]) with parameter types 'string, string, float'

      This function is unsupported: FIND(string, substring, [start]) with parameter types 'string, string, integer'

      This function is unsupported: POWER with parameter types 'float, integer'

      This function is unsupported: POWER with parameter types 'integer, integer'

      This function is unsupported: POWER(number,power) with parameter types 'float, integer'

      This function is unsupported: POWER(number,power) with parameter types 'integer, integer'

      This function is unsupported: SQUARE with parameter types 'float'

      This function is unsupported: The date part named 'day' for the date function: DATEDIFF(date_part, start_date, end_date)

      This function is unsupported: The date part named 'dayofyear' for the date function: DATEDIFF(date_part, start_date, end_date)

      This function is unsupported: The date part named 'hour' for the date function: DATEDIFF(date_part, start_date, end_date)

      This function is unsupported: The date part named 'minute' for the date function: DATEDIFF(date_part, start_date, end_date)

      This function is unsupported: The date part named 'month' for the date function: DATEDIFF(date_part, start_date, end_date)

      This function is unsupported: The date part named 'quarter' for the date function: DATEDIFF(date_part, start_date, end_date)

      This function is unsupported: The date part named 'second' for the date function: DATEDIFF(date_part, start_date, end_date)

      This function is unsupported: The date part named 'week' for the date function: DATEDIFF(date_part, start_date, end_date)

      This function is unsupported: The date part named 'weekday' for the date function: DATEDIFF(date_part, start_date, end_date)

      This function is unsupported: The date part named 'year' for the date function: DATEDIFF(date_part, start_date, end_date)

      This is unsupported: Temporary tables

       

      =====================================================================

       

      When I try to create an extract, or connect to the data live, I can get most numerical fields and some dates, but all text fields are Null.

       

      I guess I need to use Excel or Access as a go-between for now...

        • 1. Re: FM11 ODBC Driver not functional with Tableau

          carmenm:

          Thanks for posting!

          I was able to reproduce this behavior as well but it's not clear whether the issue is on our end or Tableau's. Either way, I've forwarded your post to our Development and Quality Assurance departments for further investigation. I'll post back to this thread with new information as I receive it.

          TSBear

          FileMaker, Inc.

          • 2. Re: FM11 ODBC Driver not functional with Tableau
            carmenm

            Thanks so much for your reply.  I also received a response from Tableau, here is a link.

            My first attempts to test the suggestions have been unsucessful, but I'll continue to try.  Here is the response:

            ============================================

            You may find that you can work around this by creating calculated fields which explicitly cast your string fields to a string type, e.g.
            -> calc named "strField_forced":
            STR([strField])

            Or you may be able to use pass-through SQL in the calculated field to force this conversion at a lower level, e.g.
            -> calc named "strField_raw"
            RAWSQL_STR("CAST(%1 AS VARCHAR(100))", [strField])

            This workaround is unfortunate, and it is required for each string field you are interested in. However you may have no choice -- the limitations you posted indicate that the FileMaker ODBC driver itself is limited, or at least does not properly express the functionality it does support.

            Finally, I'm curious to see if advanced users on this forum can improve the connector by way of data source customization (TDC).

            ============================================

             

            • 3. Re: FM11 ODBC Driver not functional with Tableau

              carmenm:

              Our Quality Assurance department has confirmed this as a limitation of the FileMaker 11 ODBC driver. Specifically, in the query to the FM database, Tableau attempts to use the CAST() function within a GROUP BY clause and, as it's currently designed, expressions within the GROUP BY clause are not supported. This issue has been forwarded to our Development department for further investigation.

              TSBear
              FileMaker, Inc. 

              • 4. Re: FM11 ODBC Driver not functional with Tableau
                carmenm

                Robert from Tableau viewed my machine via remote session and we worked through this problem.

                In the FileMaker ODBC Configuration, there is an option for Advance Language setting.  Specifying UTF-8 allows Tableau to access string data via extract.

                Here is the link back to the thread at Tableau's forum