8 Replies Latest reply on Jun 13, 2018 2:40 AM by Jan Jenko

    Using LOD with ODBC error

    Jan Jenko

      Hi,

       

      I'm trying to use an LOD expression on data I'm connected to over ODBC.  When I drag the calculated field ({FIXED [some_integer_field]: SUM([some_other_int_filed]}) to "Rows", I get a pop-up window saying "An error occured while communicating with data source _____".

       

      I looked at the SQL query it's trying to run:

                SELECT SUM((CASE WHEN `t0`.`X_measure__2` = 0 THEN NULL ELSE CAST(`t0`.`X_measure__1` AS FLOAT) / `t0`.`X_measure__2` END)) AS `sum:Calculation_1062568104450506752:ok`,

                  `t0`.`category` AS `category`

                FROM (

                      SELECT SUM(`table`.`field1`) AS `X_measure__1`,

                           COUNT(`table`.`field1`) AS `X_measure__2`,

                           `table`.`symbol` AS `symbol`,

                           `table`.`category` AS `category`

                       FROM `table`

                       GROUP BY 4,3

                     ) `t0`

                GROUP BY 2

       

      I tried running it in my own MySQL client, which also returned an error. However, things work fine when I change CAST(`t0`.`X_measure__1` AS FLOAT) for just `t0`.`X_measure__1`.

       

      Is there a way around this? Can I tell Tableau to change the query or something? I've had this problem ever since I started using Tableau, but I attributed it to not knowing how to use LOD expressions

       

      Anyway, any help will be appreciated...

       

      Best,

      Jan

       

      Versions:

      Tableau 10.5

      MySQL 5.6

        • 1. Re: Using LOD with ODBC error
          Eric Hammond

          Hi Jan,

           

          I am not getting that error with a similar LOD, but I don't have a MySQL data source on which to test this.  As LOD expressions usually identify a dimension as the fixed level, I would try to isolate the issue as being caused by the use of measure (some integer field).  Try:

          • {FIXED [some_stringdimension_field]: SUM([some_other_int_filed]}    //to confirm that the LOD works with a dimension. If yes, then:
          • {FIXED STR([some_integer_field]): SUM([some_other_int_filed]}       //in hopes that forcing the conversion of the integer to a string avoids the ODBC error.  Otherwise, try:
          • {FIXED 'x' + STR([some_integer_field]): SUM([some_other_int_filed]}   // concatenating an alpha character to prevent the "string" from being converted back to an integer at some point in the process.

           

          Even if a work-around gets past the error, consider reporting the error so that the development team can correct the issue in a future version.  Good Luck!

          • 2. Re: Using LOD with ODBC error
            Jan Jenko

            Hi Eric,

             

            Now, a couple of weeks later I see that the issue is not with the LOD expressions (sometimes they work just fine), the problem is with the ODBC connection. I get the same error (and the same underlying cause, the MySQL CAST statement) when trying to get LOD to calculate averages or simply dividing two fields. Still don't know how to solve the problem though...

             

            The SQL query works fine if I remove the CAST altogether or if I simply replace FLOAT with DOUBLE or DECIMAL(x, y).

            • 3. Re: Using LOD with ODBC error
              Yuriy Fal

              Hi Jan,

               

              May i ask you one question (two actually):

               

              What is the data type of the [field1] as visible in Tableau?

              It is shown when you open the Describe dialog on that field.

               

              And what is the data type of that same field

              as defined in your MySQL table schema

              (in the Create Table DDL statement).

               

              If it is a mismatch, Tableau would trigger a CAST.

               

              Yours,

              Yuri

              • 4. Re: Using LOD with ODBC error
                Jan Jenko

                Ok, so the LOD situation was a while ago and I gave up. A more recent example: I'm trying to create a calculated field [Test] = [A]/[B] where both [A] and [B] are 8 byte signed integers, which is consistent with my database where [A] and [B] are BIGINT's. When I 'ask' Tableau what type [Test] is, there is no answer, it just says that it's a calculated field, but the generated query is trying to cast is as FLOAT.

                • 5. Re: Using LOD with ODBC error
                  Jade Koskela

                  You say "over ODBC," are you connected using MySQL connector using an ODBC driver? Or are you using "Generic ODBC."

                  I'm not surprised the A/B is being cast to a float, I think Tableau is trying to be helpful. Think math rules not SQL rules.

                  In math a integer divided by an integer may result in a real number with fractional parts, even though SQL would say int/int = INT. But Tableau is a layer on top of SQL.

                  • 6. Re: Using LOD with ODBC error
                    Yuriy Fal

                    BTW, for the 'native' MySQL connection

                    Tableau would generate something like this

                    when converting the data type from Integer to Decimal:

                     

                    (`table1`.`field1` + 0.0) AS `$temp1_output`

                     

                    So I'm with Jade -- suspecting a 'Generic ODBC' driver in use.

                     

                    Yours,

                    Yuri

                    • 7. Re: Using LOD with ODBC error
                      Jan Jenko

                      Hey guys, thanks for writing back.

                       

                      I'm using Windows' "ODBC Data Source Administrator", I suppose that goes under "Generic ODBC". I never really questioned why we do everything over ODBC and not directly with 'native' MySQL connection, it's just how things were done around here (a terrible mentality, I know). Now that I think of it though, it probably has to do with the fact that multiple people access these Tableau files and we all share the same ODBC access point (but we each have our own MySQL credentials).

                       

                      Anyhow, I'll make a test project with native MySQL connection and see how that affects the way other people access these files. What I'll be doing in the meantime is a sub-optimal workaround of just changing one of the INT fields (that I'm dividing) in the database to a FLOAT.

                       

                      Jan