3 Replies Latest reply on Sep 13, 2016 8:35 AM by Glen Robinson

    How can Cache the Report in Tableau Server?

    raghu.thimmaiah.0

      Hello All,

       

      I am looking to cache the Tableau workbook in Tableau server. This tableau workbook has been implemented using the large extract.

       

      I am looking to cache this report with my logins and select all the filters and open the reports once.

       

      If i do this then if any other user logins then it should open quickly?

       

      How can i do this caching in Tableau server to make sure that other users don't find any issue with the performance.

       

      Regards,

      Raghu

        • 1. Re: How can Cache the Report in Tableau Server?
          CHENNA YENUGA

          Understanding Caching in Tableau Server

          There are two primary levels of caching present in Tableau Server:

          Model Cache

          When a request from the browser comes in for a particular view, Tableau Server will check to see if the computations to display the view have previously been done. If so Tableau Server will use the pre-computed results to return back to the browser.

          If the view requested has not been run before, Tableau Server will first determine a set of queries to run and then check to see if the queries have been run before. If we have, we can use those results to compute the view and send that back to the browser, avoiding talking to the database at all.

          Of course, if neither of these cases applies, the underlying database will be queried, building the view as normal.

          Query Result Cache

          If the view requested has not been run before, Tableau Server will first determine a set of queries to run and then check to see if the queries have been before. If they have, we can use those results to compute the view and send that back to the browser. This is more expensive than a model cache hit, but faster than going back to the database for data.

          If neither of the above mechanisms can serve up cached data, the data source will be queried for the data and the view will be constructed and saved into the cache for subsequent use.

          Determining numbers of VizQL and Application Server Processes

          It is important to understand how caching works when determining the optimal number of processes for your Tableau Server environment. With too many Application server or VizQL Server processes running, requests coming into Tableau Server will be less likely to hit a process that has already handled the request and saved the data in cache. For this reason, reducing the number of Application Server or VizQL Server processes in Tableau Server can sometimes improve the performance and user experience of Tableau Server.

          Where to start

          Determining the correct quantity of each of these processes may require some experimentation to find optimal settings, but a good place to start is generally:

          • Use equal amounts of VizQL and Application server processes (this is most important for versions of Tableau Server prior to 6.1)
          • Set both at 1x to 2x times the number of processing cores present on the server

          When to add additional processes

          If all the following conditions are met, add VizQL and Application server processes incrementally and test the results:

          • Load begins to slow the server down but the machine is not yet constrained by physical resources (disk i/o, network throughput, memory, CPU, etc.).
          • Single requests are substantially faster during times of low load/concurrency and slower during times of high load/concurrency.
          • Your data sources are capable of handling more concurrent queries from Tableau Server than are currently being generated (even at times of high load) without suffering from longer query times.

          Determining Number of Workers in a Tableau Server Distributed Environment

          Tableau Server is shipped with the capacity to create distributed environments by adding clustered nodes called workers. These distributed environments and are designed to provide scalability (not to improve performance). Adding workers to Tableau will allow requests coming into Tableau Server to be load-balanced to multiple machines. Bear in mind that Tableau Server relies on HTTP communication to balance load and handle internal requests between processes. Moving from a single server environment to a distributed environment will require processes in Tableau processes to communicate with each other over the network. Since this is added overhead, adding a worker (or multiple workers) should only be considered when a single dedicated server is unable to handle the required load.

          Where to start

          Tableau has published the test results for Tableau Server. The whitepaper is coming soon.Note: These tests were performed by Tableau and show results from a specific test configuration and should not be taken as a guarantee of client response times. These benchmark results were returned in a controlled lab environment, without other applications running during execution. Actual results will vary based on a number of variables including, but not limited to, load type, hardware, network speed, browser settings, and database performance.In these tests (see figure below), 100 concurrent users experienced optimal performance with a single server. Only when concurrent users increase to over 100 users did help to add Tableau Workers. This is largely due to the performance benefits of caching (caching benefits generally increase with concurrent usage).The above findings are by no means universal. Response times for concurrent users can be affected by the number and complexity of workbooks and the frequency with which the users access and interact with the views. Every Tableau Server environment is different and while there is not a one-size-fits-all answer to the question of scaling Tableau Server, the information provided in the above knowledge base article will often give a good baseline.

          When to add additional workers

          If all the following conditions are met you may want to consider adding workers:

          • Adding additional VizQL and/or Application server processes is not possible with current resources, and/or the machine in general is beginning to be constrained by physical resources (disk i/o, network throughput, memory, CPU, etc.).
          • It is not possible to improve the existing hardware sufficient to meet load needs.
          • The machine(s) currently running Tableau are dedicated to Tableau Server and do not have other applications running on them which may contend for resources (SQL Server, IIS, etc.).
          • Your have additional available cores on your core license (sufficient to license another worker), or you are using a named user license.
          • Your data sources are capable of handling more concurrent queries from Tableau Server than are currently being generated (even at times of high load) without suffering from longer query times.
          1 of 1 people found this helpful
          • 2. Re: How can Cache the Report in Tableau Server?
            raghu.thimmaiah.0

            Hello Chenna,

             

            Thanks for the details.

             

            But where it set the options for Model Cache and Query Result Cache in tableau server?

             

            Regards,

            Raghu

            • 3. Re: How can Cache the Report in Tableau Server?
              Glen Robinson

              Hi Raghu

              The settings for Caching with Tableau are pretty rudimentary, in that Caching can be switched on or off (and the duration that the cache is kept for can be set.)

               

              To load a query into cache, you only need to open a report that runs that query.

              Therefore for you where you are looking to load all the variations of a report on a scheduled basis.

              This can be done in 2 main ways.

               

              1. Subscribe to a report, and pick a schedule for that subscription that is useful to you. ie At 05:00 have a subscription schedule which runs the report and sends you an email.

              Manage Subscriptions

               

              Subscribe to Views

               

              For you if you want all the filters for a view, you will need to create Custom Views for each of the filters and subscribe to them.

              http://onlinehelp.tableau.com/current/online/en-us/help.htm#customview.htm

               

              This can be quite time consuming to set up

               

              2. The second option is to do this outside of Tableau, using TABCMD

              With Tabcmd you can export a report as a PDF, which will ensure that all the results of the queries for that report are loaded into Cache

               

              Below is a simple powershell script to do this,

              This script runs through a CSV file called regions, and creates a pdf for each of the regions for SuperStore Overview Report.

               

              ExportReport.ps1

               

              $csvfile = import-csv -path "regions.csv"

               

              $server = "http://localhost"

              $username = "admin"

              $password = "password"

              $Folder = "C:\temp\"

              $tabcmd = "C:\Program Files\Tableau\Tableau Server\10.0\bin\tabcmd.exe"

              $URL = "/views/Superstore/Overview.pdf"

               

              foreach ($line in $csvfile)

              {

                $Region = $line.Regions

                $FileName = $Folder + $Region + ".pdf"

                $FullURL = $URL + "?:Refresh&Region="+ $Region

              $Region

              $FileName

              $FullURL

                & $tabcmd get -s $server -u $username -p $password  $FullURL  -f $FileName

              }

              & $tabcmd logout

               

               

              Regions.CSV File

               

              Regions
              West
              East
              Central

              South

               

              Save the 2 files, ExportReport.ps1 and Regions.csv, change the variables to reflect your environment and requirements, and off you go.

               

              You can then run the Powershell script on a schedule using Windows Task Scheduler

              Good luck

               

              Glen

              1 of 1 people found this helpful