3 Replies Latest reply on Feb 6, 2012 7:13 AM by Eric Maher

    Multiple Tables vs Custom SQL

    Eric Maher

      I have a Tableau workbook that uses the Multiple Tables type of connection to a SQL 2008r2 data mart made up of fact tables and dimensions.

      Back in October the scheduled refresh started failing with the following error:

      SQL Server database error: Explicit conversion from data type datetime2 to float is not allowed.

      I found a posting somewhere here stating that this could be a driver issue and that an older version of the driver processes dates differently than a newer version; but my question goes a little further.

      When I profile the server for the SQL, I get back some strange results.

      There are 3 date fields included in the SELECT and they are all treated differently in terms of their interpretation by whatever generates the SQL.

      I have attached the entire SELECT but here are the 3 different date fields from the SELECT as returned by the profiler.

       

      
       SELECT...,
        CAST(FLOOR(CAST(CAST([InvoiceDate].[Date] as datetime) as float)) as datetime) AS [Date],
        [PODate].[Date] AS [DimDate_Date],
        CAST(FLOOR(CAST(CAST(CAST(FLOOR(CAST(CAST([InvoiceDate].[Date] as datetime) as float)) as datetime) as datetime) as float)) as datetime) AS [Invoice Date],
      FROM ...
      
      

       

       

       

      Notice how 2 of them have a series of CAST/FLOOR functions performed on them and one has nothing.

      Another point to note is that the 1st and 3rd are the exact same field ([InvoiceDate].[Date]) so why are they treated differently?

      Also, the PODate and InvoiceDate tables are actually table aliases back to the DimDate table.

       

      My fix will most likely be to change the type of connection from Multiple Tables to Custom SQL and do away with the CAST/FLOOR operations on the date field but any insight as to why this is happening would be appreciated.

        • 1. Re: Multiple Tables vs Custom SQL
          Eric Maher

          I think part of this is beginning to come together for me. When I look at the original workbook, there is a calculated field for [Invoice Date] which is this: Date([InvoiceDate]). Still not sure why all the extra code to perform a Date function on a date field. And why if there is already an [InvoiceDate] in the source, why create a new field called [Invoice Date]. I can only guess that the creator wanted to strip the time component from this field.

           

          In the original workbook I don't see a [Date] field or a [DimDate_Date] field so I'm still a bit confused about that.

          I realize much of my answers will probably come from my data- but any insight would still be appreciated.

          Thanks,

          • 2. Re: Multiple Tables vs Custom SQL
            Robert Morton

            Hi Eric,

             

            SQL Server 2008r2 introduced the datetime2 type with strange compatibility issues with the datetime type and with older drivers. The casting helps preserve the 'datetime' level of precision from a datetime2 type, as needed. As for the error message you described, that may have occurred when using older versions of Tableau and older versions of the SQL Server driver with the latest SQL Server database, but newer versions of Tableau try to enforce that the user has the latest driver version needed for proper compatibility when refreshing extracts.

             

            Does this help?

            -Robert

            • 3. Re: Multiple Tables vs Custom SQL
              Eric Maher

              Robert, thanks for the input.

              This is roughly the conclusion that our sys admin came to. We're very close to rolling out v7 and he said he would make sure to update all the drivers as well.

              -elm