3 Replies Latest reply on Jul 6, 2016 7:44 AM by Nagarajan R

    Find slow running extract data source within a workbook

    Nagarajan R

      Hi,

       

      I have a workbook with 30 data sources included within as a extract and have setup the workbook for daily refresh.

      Scheduled extract refresh was on average taking about 4800-5300 secs to complete. It has been the same for about 1 year now.

      All of a sudden (three days back), it stretched beyond 7200 secs (max query time limit in Tableau) and refresh failed.

       

      No new data was updated in source between the day when it completed successfully and the day it failed. Now its the 3rd day it has failed.

      How to trouble shoot the issue and know which extract(s) among 30 is causing a delay?

       

      I know increasing query limit in Tableau server is an option but wish to know the reasons for this extended refresh time before I take that option.

       

      Below is the error I get when scheduled refresh fails:

      com.tableausoftware.nativeapi.dll.ResourceGovernorException: The query time resource limit (7200 seconds) was exceeded

       

      Do I need to open this workbook from Desktop and individually refresh all extract within to troubleshoot or any alternate ways?

      Any help please!!

       

      Thanks

        • 1. Re: Find slow running extract data source within a workbook
          Mahfooj Khan

          Increasing timeout setting of gateway.timeout can fix your problem. However as you said you have 30 data source connected to in a single workbook. Schedule your refresh one by one. Another approach increase number of backgrounders. To increase number of backgrounder follow the link Reconfigure Processes .

          How many dashboards you have in your workbook? I guess more than 30. Its obvious you've connected with 30 data sources.

          I'm just thinking about the response time of your dashboard If any user filter something how much time it'll be taking to show the values. Can you tell me are you using blending in your viz? Do you've any dashboard where its using a single data source? If yes then try to publish that dashboard in a separate workbook. And use URL action to navigate from original dashboard to the other published dashboards. So that it wont affect the load time in your original dashboard. Is it possible to split the dashboards lets say 5 dashboards in each workbook and each dashboard is connected with URL action?

          Or

          Just publish the dashboards individually with their respective data sources. So ideally you should have more than 30 published dashboards. Then create a dashboards which will be the landing page where using images of your all 30 dashboards and set the URL of your published dashboard on each of the images then published it in server and give this dashboard access to the end users.

          See the screen shot.

           

          Let me know If this help.

           

          Mahfooj

          • 2. Re: Find slow running extract data source within a workbook
            Nagarajan R

            Hi Mahfooj,

            Appreciate your response.

            Yes indeed the workbook has around 60 dashboards (with each dashboard having 4 to 5 sheets within). its quite a large workbook and certainly we are having performance issues with some having blending implemented. We have already recommended splitting the dashboards with table of contents landing approach and story board type navigation strategy.

             

            Btw the extended refresh time we found was because of the slowness in database. As part of the troubleshooting process, we went to the database and direct queried and found a simple select count took longer than usual.

            Raised this immediately with DBA team and they are working on it.

             

            With that said I was wondering if there is a way to know how many records each data source within the workbook was pulling from the database. I know drag and drop "Number of Records" in worksheet for each data source will give this answer but any properties where i can see directly the number of records pulled by each data source?

             

            Thanks

            Nag

            • 3. Re: Find slow running extract data source within a workbook
              Nagarajan R

              Hi,

               

              In addition to above, can you help understand how Tableau sends queries to database in this scenario?

              If 30 data sources are there within the workbook and if workbook is set up for daily once refresh, does all 30 data sources related SQL query get trigger to Database or does Tableau send it one by one sequentially?

               

              Also along with a way to know rows count for each extract, is there a way to know how much time each extract took within the workbook when the scheduled refresh is complete?

               

              Thanks