1 2 Previous Next 25 Replies Latest reply on Jul 29, 2015 3:51 PM by David Schripsema

    FileMaker 11 ODBC not functional

    Carmen Mercer

      If anyone else has been able to use FileMaker ODBC with Tableau I would be very interested.  We are running the latest FileMaker Server 11 Advanced.  The ODBC version is just listed as 11... which is FileMaker's version.

       

      I have had much success using this ODBC driver with Microsoft Office products (Excel, Microsoft Query), but no love with Tableau.  Here is my experience:

       

      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'll go through Excel and Access for now.  Thanks in advance for any words of wisdom.

        • 1. Re: FileMaker 11 ODBC not functional
          Robert Morton

          Hi Carmen,

          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).

          • 2. Re: FileMaker 11 ODBC not functional
            Carmen Mercer

            FileMaker has been responsive to my inquiry, though unfortunately with no resolution. Here are the details of the ODBC limitation:

             

            http://forums.filemaker.com/posts/a1b6f88335

             

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

            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.

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

             

            If anyone could point me toward some resources that would help me use this information to craft a functional connection I'm not opposed to learning.

            • 3. Re: FileMaker 11 ODBC not functional
              Robert Morton

              Hi Carmen,

               

              There may be a way to address this issue by using an ODBC connection customization file, sometimes referred to as TDC. The detailed documentation is posted in this knowledge base article, but I can help you get started quickly if you can provide a bit more information. If necessary, may I contact you directly via your forum email address?

               

              To start I'll need you to take a look inside your Tableau log files, which you can find under your user documents folder, My Tableau Repository\Logs. The line we're interested in looks like this:

               

               

              GenericODBCProtocol::Connect: Detected vendor ...


               

              Please paste your copy of that line into your response on this forum and I'll see if I can help.

               

              -Robert

              • 4. Re: FileMaker 11 ODBC not functional
                Carmen Mercer

                GenericODBCProtocol::Connect: Detected vendor: 'FileMaker' and driver: 'FileMaker ODBC'

                 

                Yes, feel free to use my email.  Thanks for your help. My supervisor will be quite pleased if I can make this work.

                • 5. Re: FileMaker 11 ODBC not functional
                  Robert Morton

                  Hi Carmen,

                  I'll contact you directly via email with instructions on what to do next. For others following this thread, I've attached the basic TDC file that I'm hoping will address this problem.

                  -Robert

                  • 6. Re: FileMaker 11 ODBC not functional
                    Robert Morton

                    Update: It appears that the problem was with the FileMaker DSN setting for the character encoding of strings. The DSN offers an option dialog called "Advanced Language", where the default mode is auto-detect. Carmen and I were able to resolve this issue by changing the mode to explicitly use "UTF-8".

                    -Robert

                    • 7. Re: FileMaker 11 ODBC not functional
                      howard haimovitch

                      Thank you Mr. Morton, this interchange has been very helpful. I am a FileMaker developer and one of my clients has asked me to look into adding Tableau onto a software package that he sells. One of your staff is checking into a trial setup using FileMaker odbc. This research and problem solving will likely help- us to connect FileMaker ODBC with Tableau. If possible could someone connect with Carmen and ask her to respond if all is working ok with the revised settings for the odbc?

                      • 8. Re: FileMaker 11 ODBC not functional
                        howard haimovitch

                        Hi Carmen M.,

                        My name is Howard Haimovitch howard@everythingfilemaker.com,

                        We are checking into the possibility of using Tableau with FileMaker and ODBC. Were you successful in getting the odbc to work? Please let me know at your earliest convenience. Thank you for your help. howard. Skype:howard.haimovitch

                        • 9. Re: FileMaker 11 ODBC not functional
                          Robert Morton

                          Hi Howard,

                          We don't solicit customer feedback on behalf of other companies, though I will reiterate from my earlier comment that Carmen and I were able to get the connection working well enough to create extracts in Tableau. If you would like to discuss partnership opportunities, I encourage you to check out our Partners page for an overview and contact information.

                          -Robert

                          • 10. Re: FileMaker 11 ODBC not functional

                            excellent forum topic. We are about to move our mysql master db to Filemaker 11 advance, so we can make easy and fast modules in the company (since filemaker front end and mysql can really slow things) but we do use Tableau a lot and cannot afford to have no connection to the future Filemaker database.

                             

                            any ways I will start to make tests with tableau and filemaker 11 advance odbc connector and see how it goes. I will update on my test..

                             

                            our coldfusion VS filemaker test where a success.. works fast and reliable

                             

                            at least I have hope now with the last loose end I had (Tableau)

                            • 11. Re: FileMaker 11 ODBC not functional
                              Robert Morton

                              Hi Guillermo,

                              I'm glad you found this useful. If you run into problems please don't hesitate to post them here, though I may not be able to reply until 11/29. Of course if you have no problems and simply want to share your success story here, we welcome that too!

                              -Robert

                              • 12. Re: FileMaker 11 ODBC not functional

                                Count on it Robert, I will provide my experience so it can be used for other guys in the same situation.

                                • 13. Re: FileMaker 11 ODBC not functional
                                  Robert Morton

                                  Great, thanks! Also, I meant to note that you may find limited functionality with live queries depending on how capable the FileMaker ODBC driver is, but if you create an extract you will get a boost in both performance and functionality.

                                  -Robert

                                  • 14. Re: FileMaker 11 ODBC not functional

                                    I just had time to do some testing with the Filemaker ODBC / JDBC Driver client to Filemaker Server Advanced 11 ODBC connector and sorry to tell but the FM capabilities are some sort of a joke. Unless someone wants to get a simple table or spend very good time twicking SQL syntax that FM supports you will be able to do something more or less useful.

                                     

                                    I was really hoping that we could port a coldfusion / mysql app we have to coldfusion / filemaker and then use tableau as we are doing now but now I realize this wont be possible

                                     

                                    Tableau uses a lot of things that Filemaker driver just wont support. The only viable way I see is to use filemaker export capabilities to map data to a supported db such as excel or mysql and then connect tableau to them. that makes an extra step but don't see other way.

                                     

                                    I really hope FM gets a better odbc driver on the next release (filemaker 12).

                                     

                                    If anyone in this forum things different I would apreciate their feed back to try other way.

                                     

                                    regards

                                    Guillermo Dewey

                                    1 2 Previous Next