1 Reply Latest reply on Jun 22, 2016 7:06 AM by lenaic.ridinger

    Tableau extracts on top of BEX query

    Rajendra Cheraku

      Hi Experts,

       

      Need your help!!!

       

      I have created Tableau dashboard using Extract option on top of BEX query.

      When I execute the BEX query, it is taking just <1 Min, but the Tableau extract job is taking around 40 min.

       

      Please help me to understand what is causing the issue for tableau extract job to take that much time.

       

      Thanks,

      Raj

        • 1. Re: Tableau extracts on top of BEX query

          Hi Rajendra Cheraku,

           

           

          Here is a post from Russell Christopher that might help you with your issue:

          Christopher wrote:

           

          When thinking about extracts, the "width" of the final extract (all columns in the extract) is often as, and sometimes more important than the height/number of rows. So keep that in mind for the rest of the "commentary".

           

          I just pulled ~5M rows from SQL (via a 10-table join) and it took 5 min, 10 sec - If the output isn't too wide and your database is responsive, ~1M rows / minute is fairly standard.

           

          If you have a particularly "fast" data source, you can do better. For example, when extracting rows from a huge local extract with not too many columns, I was doing about 80M an hour, or ~1.33M / minute. Yay!

           

          Flip this equation - I had a client pulling ~190M rows from a SQL Server via a fairly ugly query - it also returned 100+ columns, some of which contained extremely unique values (which makes for slower sorting). It took these folks about 8 hours to return results from SQL, and then an additional 10+ hours to process/sort because of the super-high cardinality in some of their fields - about 174K rows / min. Boo!

           

          So...I'd say that 11M rows in 2-3 hours is too slow. The things you should look at:

           

          • How long does it take after you start the extract process for SQL Server to start returning records (based on the Tableau dialog that says "X rows retrieved so far")? This is pure "SQL think time"
          • How long does it take to return those 11M rows? This is a combination of SQL Processing time, latency in terms of returning 11M rows across your network, and our engine dropping temporary "batches" of rows to disk locally.
          • How long does Tableau take in the "Optimizing" and "Sorting" phases - this is pure Tableau time

           

          If you see the lion's share of your time being sucked up in bullets 1 & 2, you should see if you can optimize SQL - look at the tables/query you're using and add appropriate indexes, etc. etc.

           

          If Tableau is taking a long time to optimize/sort, you may have lots of columns and/or columns that contain very unique values which take longer to sort. Consider removing those fields from your data source before you create the extract.

           

           

          In the meantime, feel free to check our blog. We created very interested articles about the extracts and their optimizations!

           

          Hope this helps!