9 Replies Latest reply on Aug 11, 2011 6:35 AM by Teresa Obis

    How join Tableau Extract with an Excel File

    Teresa Obis

      I receive my data in a Tableau extract (*.tde file). Following an idea of Joe, I must quadruplicate my original data set in order to calculated a Time Dimension (Months, YOY, MAT and YTD). That could be done joining my original data set with a file that only have a new variable with the four dimension and a Join_Key =1


      Joins are very easy if you have your data in the same Excel file. But what happend if they are in different files or diferent type of files. How can I join them?


      I can not save my data to an Excel File. Tableau put it more difficult in version 6.1 (in the view data window has desappear "select all"), but also my data is too big and I get an out of memory in the copy and paste process. Is there any other way to copy your extract to an Excel File?


      My original data set has 10 millions of rows. I create an extract rolling up my data to month. The new file has around half million of rows. May I introduce this extract four times?


      Any help would be appreciated.



        • 1. Re: How join Tableau Extract with an Excel File
          Richard Leeke

          Hi Teresa


          I think I understand what you're trying to do.  I haven't looked at Joe's original post, but I'm sure it makes sense to do what he's saying.


          Unfortunately, you can't join another table to a Tableau Data Extract to multiply the data like this, so you'll either need to multiply the rows at the time of creating the extract, or do what you are trying to do and export the data from the TDE file to some other format where you can join in your other table, then recreate a bigger extract by multiplying the rows.


          There are a couple of options I can see for multiplying the rows at extract creation time.  You could create the dimension table and join to that in the way Joe suggested, or if it is not possible to create an extra table in that data source you could achieve the same with a custom SQL connection which uses a series of UNION ALL clauses to replicate the rows as needed.  (Let me know if you want more detail of how to do the UNION approach).


          If you do need to export the data out of your TDE file again, there are a couple of possible ways.


          1) You could copy and paste in chunks via the view data window.  Obviously you would need to work out how big the chunks can be without out of memory errors, and find some reliable way of selecting those chunks.  (You can "select all" in the view data window using <ctrl>A, by the way.)


          2) You can use File->Export to export the data to an Access database.


          That's all I can think of.

          • 2. Re: How join Tableau Extract with an Excel File
            Teresa Obis

            Hi Richard,


            You understand very well Joe's suggestion.


            I am worry  about the solution. My original extract has 10 millions of rows. So if I have to multiply it by 4 either by a Join or by an Union it will be 40 million of Row. From almost 1GB to 4GB. How can I move it!


            When I introduce this file to Tableau I roll up it to month. So it is 1/2 million row and in this moment in more easy multiply it by 4. But copying and pasting chunks is very dangerous.  Thank you for the<crtl > A option. That saves my live. Now I only need to buy a 64 bit computer to have bigger memory.


            Lastly I do not know how can export my data to an Access Database. Are you talking about Tableau option? Where it is?


            Until Tableau Team decide to introduce a ROW or COLUMN Dimension (I think that will be very useful, isn't it?) I will create 4 worksheets, one for each time dimension, and put all together in a Dashboard. In my report that means 500 worksheets for 75 dashboard. Tableau doesn't work and I have to divide my work at least into two parts.


            Now I have my options clear:

            1) Buy a new computer

            2) I held out patiently and designed the 500 woorksheets.


            Thank you for your comments.



            • 3. Re: How join Tableau Extract with an Excel File
              Joe Mako

              I think the best option would be to go to the point where the .TDE file with 10 million records is created, and use custom SQL to create it instead of just a pull. This custom SQL can both roll-up and duplicate the data, producing a .TDE that is about 2 million rows (4 x 500K) in one step. This would be the most ideal, and lines up with Richard's recommendation, with additional details assistance in crafting the custom SQL can be provided.

              • 4. Re: How join Tableau Extract with an Excel File
                Richard Leeke

                I agree with Joe that multiplying at the point of creating the TDE is the best way to go - either rolled up or at the detail level.  I haven't read your original thread carefully, but I did notice that you were having some problems when you had all 10 million rows in your extract and Tableau support recommended the rolled up option.  That certainly makes sense, but I wouldn't discount a 10 million (or 40 million) row extract.  I have had a lot more rows than that in an extract quite a few times and it still worked fine.


                When you say "how can I move it?", are you just talking about the problem of getting it from the machine where it's created to your machine?  That has to be possible, surely - even if you have to copy it to a USB stick or something?  I can see that emailing it wouldn't work though!


                Just clarifying my comment about exporting the extract to Access.  That is a Tableau option on the file menu (File->Export->Data...) but it actually probably doesn't help at your data volumes.  I remember now that it only exports the data at the level of summarisation and with the fields you have in your view - so you would need to put all fields on the view, with no aggregation - which would almost certainly mean you would run out of memory.

                • 5. Re: How join Tableau Extract with an Excel File
                  Teresa Obis

                  Richard, Joe,


                  I agree with both of you. The best way to solve the problem is to costum the SQL file and roll up the data and quadruplicate the file to get the time dimension.


                  I have problems with the 10 milion row because I am doing a 75 pages (dashboards) printing report, full of small "pieces" more than 500 worksheets). When I have already designed the worksheets and I was creating the page 60, then Tableau stop to work (even I was working with the 1/2 million Extract).  So I have to divide the report in two parts. Probably if I solve the time dimension before, I will need only around 200 worksheets (every 4 small pieces -one for moth, YOY; MAT and YTD - will be only one) and all the report could be in one file. Oh, my God! I just realize if I would like to put all the report in one file I must repeat every worksheets and dashboards because Tableau don't let you copy and paste them. And no one garantue me, the 4 millions records will work with 200 worksheets and 75 dashboards.


                  I was expecting Tableau 6.1 will solve my problems, but it isn't (even it has improve his performance).


                  About "move it" I just think I loose the possibility of send my data by mail.


                  And to export the data, I usually set the number of records as the level of detail. Then if I do View data I get all the data for all the variables. But copy and paste that amount of data give me an out of memory. I can not understand by Tableau Team don't develop an export data option to Excel/Access. We will return to Tableau, is better to work with Tableau than Excel, but human people sometines need to return to their knowlegde to feel sure.


                  By the way, thank you for both of you for all the help you are doing me in this hard entry to Tableau.



                  • 6. Re: How join Tableau Extract with an Excel File
                    Joe Mako

                    I am interested in why you want to have duplicates of the same sheet or dashboard, why not use filters or parameters to adjust the data to be displayed in a view? That way you create the view structure once, and then based on the selected filter/parameter, the data displayed changes to the selection.

                    • 7. Re: How join Tableau Extract with an Excel File
                      Teresa Obis

                      I must duplicate worksheets because I am designing a "Printer" report. Every month we have to capture the new data set,  change the period of analisys and the repord will be ready for printing.


                      We plan to design another report with filters and actions, where users can change their dimension analisis with a filter and go to detailed analysis when select a category of one dimension. But that will be the next.

                      • 8. Re: How join Tableau Extract with an Excel File
                        Joe Mako

                        With Tableau Server there is a tabcmd tool that enables you to automate things, such as setting filters and exporting to PDF.

                        • 9. Re: How join Tableau Extract with an Excel File
                          Teresa Obis

                          I do not know that tool. I am going to investigate and see if it could help me.


                          Thank you,