1 2 Previous Next 18 Replies Latest reply on Feb 27, 2017 6:28 AM by William Bourgeois

    Unable to materialize temporary table

    Meenakshi Chaudhary

      Has anyone encountered "Unable to materialize temporary table" while connecting to data sources. We often connect to IBM Netezza data sources and get this error sometimes.

        • 1. Re: Unable to materialize temporary table
          Brian Coggon

          Meenakshi,

           

          I have dug around a bit and have a couple questions. Can you explain what type of filters you have on your sheets? Does this error persist or if you refresh will it go away? Are you connecting to the data source live or does this happen with an extract?

           

          Intermittent errors are very hard to diagnose so the circumstance and setting are helpful...

           

          Let us know if you have any additional details that will help us help you!

           

          Cheers,

          Brian

          • 2. Re: Unable to materialize temporary table
            David Schultz

            I just got the same error. I'm using Tableau 10.

             

            I have my main query that is using SQL Server, and then I have a left join to an Oracle source. If I leave the query "Live" I do not get the error. But when I try to make this combined dataset an "Extract" that's when I get the error.

             

            Thanks,

            David

            • 3. Re: Unable to materialize temporary table
              Anju Andrews

              I was facing the same issue when trying to join excel and csv sources and the below thread helped me resolve my issue:

               

              Error "Unable to materialize temporary table" Joining Data Sources | Tableau Software

              2 of 2 people found this helpful
              • 4. Re: Unable to materialize temporary table
                David Schultz

                Thank you for referring me to the thread, however we don't have any antivirus software on our Tableau server.

                 

                Thanks,

                David

                • 5. Re: Unable to materialize temporary table
                  Harold van Garderen

                  I'm having the same problem with Tableau 10 using Desktop Professional on a Mac when (left) joining XLS and MySQL data. As far as I know there is no anti-virus on (my/the) Mac. Is there any progress on this?

                  • 6. Re: Unable to materialize temporary table
                    David Schultz

                    Harold,

                     

                    After working closely with Tableau here is the solution that worked for me. The join I was using between SQL Server and Oracle both had a CustID, however they didn't seem to be the same data type. In both custom SQL statements I casted them both as "int" and then they wouldn't join together. I casted them both as "integer" and they still wouldn't join together. I then casted them as "varchar(15)" and now they join together and the extract works properly and I no longer get the error.

                     

                    It was not the anti-virus.

                     

                    I just got it working this morning.

                     

                    Hope this helps,

                    David

                    3 of 3 people found this helpful
                    • 7. Re: Unable to materialize temporary table
                      Harold van Garderen

                      Hi David,

                       

                      So happy to hear from you and that it's Tableau not the Mac:-). I did some further tests after posting here also today (it's 19:39 here in The Netherlands) and in my case its HOSP-ID (Hospital ID) that had similar problem. It was read as a WHOLE INT from the XLS and as STRING from mySQL. I'm not techie enough to solve this the way you did, but I'm quite sure one of my Canadian colleagues can exploit this workaround. Still I guess version 10.1 should solve this. Does Tableau read with us here?

                       

                      Thanks,

                       

                      Harold

                       

                      UPDATE: I retried and this time I make both sides of the join STR and that worked!!!! Both WHOLE or DECIMAL INT (should work as the data is 1 2 3 4 etc) doesn't work, but with STR it works. Hooray.

                      • 8. Re: Unable to materialize temporary table
                        Nathan Wood

                        Can you explain what this means please? I have no antivirus software either, yet I cannot join Excel Workbook and .csv files.

                        • 9. Re: Unable to materialize temporary table
                          Russell Christopher

                          HI Nathan -

                           

                          In order to join two data sources, there must be a field "in common" between them. In this case, let's say the column in question is called "Item ID".

                           

                          That column must be of the same data type in both places...What this means is that while an Item ID of "123" might LOOK like a number to you, it might be STORED by one of your data sources as a STRING. You can't join a Number (Item ID in data source 1) to a String (Item ID in data source 2),

                           

                          One of quick start guides talks a bit about this:

                           

                          Quick Start: Combine Tables Using Cross-Database Joins

                           

                          Before you join tables using a cross-database join, make sure that the data types of the join keys match. If the data types of the join keys don't match, the join breaks, which is indicated by a red exclamation point. To fix a broken join, take one of the following steps:

                          • For text or Excel-based data, modify the data type of one of the text or Excel fields in the join key using the data type menu in the data grid.
                          • For most connections, you can use the Convert to custom SQL option to change the data type of one of the fields in the join key. The Convert to custom SQL option is available only when the data source contains only one connection. In this case, remove the second connection and then select Data > Convert to custom SQL.
                          • 10. Re: Unable to materialize temporary table
                            Nathan Wood

                            Thanks for the response!

                             

                            This answer must be correct; however, this is an issue I am receiving from the files providing in the Tableau Training Videos. Shouldn't these files be properly coded with the correct data types?

                             

                            Also, I'm not sure what this means: "For text or Excel-based data, modify the data type of one of the text or Excel fields in the join key using the data type menu in the data grid."

                             

                            Thanks again!

                            • 12. Re: Unable to materialize temporary table
                              Dan Quigg

                              I just ran into this - I even had been able to create the extract table and then all of the sudden it stopped working.  I was able to get it to extract if I change one of the joins from a left join to an inner join but it makes my results incorrect.  Trying to figure out now what happened and how to fix it.

                               

                              Nothing to do with anti-virus software.  I disabled that for giggles temporarily and still had the issue.

                              • 13. Re: Unable to materialize temporary table
                                Joe Zellmer

                                I started seeing this same message string. In my case I have an Access Database with a Location (text) and a material (text) and doing left join to two google sheets, one for Locations and one for Material with an identical name for the corresponding keys. No virus protection in play (much to the dismay of our corporate security team). Following Dan Quigg's suggestion I changed my join type to inner and my extract completed. Seems to be impacting both Tableau Desktop(win 64)  10.0.1 and 10.1. versions. ..checked my 10.2 beta ..seems to corrected and not a problem so patience is a virtue ..not sure when 10.2 becomes available. 

                                 

                                Went back to the 10.0.1 version and changed the join for Access Database to Google Material to a left join..no problems..changed the join back to inner between Access Database to Google Plant Locations ..failed. In the past few days I have "mucked" around with the google sheet for Plant Location. My "vision" is to use the collaboration functions in Google to allow users to manage the content within the sheets, but have Tableau reflect those changes. Perhaps Tableau is holding on to an older version of the Google sheet?  In the above examples did you modify the excel sheets and then start having the issue?  

                                 

                                Best Regards,

                                Joe Zellmer

                                • 14. Re: Unable to materialize temporary table
                                  Aaron Freed

                                  i have this same issue. i'm on a macbook air running OSX 10.12.2 and tableau 10.1.2 (same issue when using 10.1.1). i'm left joining two google sheets on a single integer field. changing data type to decimal or string does not resolve the problem. the problem only occurs with the left join with a live connection; all other join types and extracts work, but that's not a solution, of course.

                                  1 2 Previous Next