6 Replies Latest reply on Jan 9, 2014 8:05 AM by Toby Erkson

    Tableau import data limitations 30 m

    Sirish Bhatta

      Hello,

      I am new to Tableau. Have created some basic graphs and Chart using this tool in the past. We have 30 m rows that we are trying to create a tabular report ( Maseter child -- with 5-6 levels when you click on an id, it drill down further and further to the detail level).

      I have tried importing all 30 m rows and i am getting out of temp or out of memory error.

      If I try connecting live then, just to design drag and drop a dimension takes 15--20 seconds, may be because of the volume of data.  So for now i am only selecting a subset of data.  My questions are:

       

      What is the best way to handle 30 m rows thats store in a SQL server database ( Live extract, work offline, any other)

      Are there any limits on the number of rows or columns that can be displayed on the dasboard/sheeet. We have live 70 columns and it's overlapping at 16.

       

      For now i have started creating a basic report ith master, child tabular report. But at some point  we need to we defualt the report display based on who is logged on ( securit and parameter) . Can this be done through Tableau.

       

      I would appreaciate any suggestions or ideas. Thanks for your help 

        • 1. Re: Tableau import data limitations 30 m
          Toby Erkson

          Sounds like you're using Tableau to pull data to then dump into another application.  Bad juju.  A 70 column table report?  That's not a report, that's more like information mining/exploration.  Anyway...

           

          Are you using the 32-bit or 64-bit version of Desktop?  How much RAM does you system have?  Open the Windows Task Manager and see how much of your memory is consumed before opening Desktop, then keep watching it as you run the "report".

          • 2. Re: Tableau import data limitations 30 m
            Toby Erkson

            Yes, you can filter the report based on the user ID.  You need to tell us if you're using Tableau Server and/or Desktop/Reader as there are a few ways to accomplish it.

            • 3. Re: Tableau import data limitations 30 m
              Sirish Bhatta

              Hi Toby,

              What we are trying to do is create a tabular report where you click on one id, then display the corresponding details and again when you click on an id of the details then display the corresponding details( report - subreport)

               

              The idea I was  told is to click on one level and then drill down to  4 - 5 levels... to the detailed data itself  by creating a report/dashboard and also publish the data for other to use.

               

              I am using a 64 bit Desktop Win 7 16 GB Ram .  I am running the import again and 3904  MB memory is free and so far 6 million is completed. Last time i tried it errored out at 15 million.

              I am using Tableau Desktop.

               

              Even when I tried using like a million, there is 10-5 seconds lag when i drag and drop a column/dimension in the sheet.. is there a way to work in the design mode and then run it afterwards.

               


              • 4. Re: Tableau import data limitations 30 m
                Toby Erkson

                I'd recommend using an extract instead of connecting live to the db.  After doing that, people will probably want you to attach the workbook so they can better see what's going on.

                 

                Are you just getting columns or are you also performing logic (IF...THEN, etc.) and/or custom calculations, are you using Custom SQL to get the data, etc.?  I can't help now but here's some info from Robert Morton that I kept as it's helpful to know:

                You should consider using the single-table or multi-table connection interface instead of Custom SQL. It's especially important to avoid prematurely grouping and aggregating your data in Custom SQL, since that can lead to mis-computed aggregations in Tableau and can easily cause performance problems.

                Remove any existing GROUP BY and ORDER BY clauses from your Custom SQL.

                Furthermore, remove any WHERE clauses and replace them with normal Filters in a Tableau worksheet, or define them as Data Source Filters.

                If you continue to have bad performance (since many databases have trouble with the subqueries that Custom SQL requires), then consider making a Data Engine extract.

                • 5. Re: Tableau import data limitations 30 m
                  Toby Erkson

                  Sirish Bhatta wrote:

                   

                  ... is there a way to work in the design mode and then run it afterwards.

                   

                  There is   Uncheck the Auto Update Worksheet:

                  terkson - Thursday, January 09, 2014 7_58_33 AM - Tableau - World Indicators [Read-Only].bmp

                  • 6. Re: Tableau import data limitations 30 m
                    Toby Erkson

                    To then refresh/update your workbook after you've made changes and want to see the results click on the Update icon:

                    terkson - Thursday, January 09, 2014 8_01_03 AM - Calendar.bmp

                    Naturally, you can turn Auto Updates back on whenever you want.

                     

                    (I apologize for the multiple posts but I'm working on a crippled IE8 browser and the forum software doesn't play well with IE8, too.  The combination really cripples my dynamic creativity).