5 Replies Latest reply on Aug 12, 2013 4:44 AM by Russell Christopher

    Is it possible to limit memory consumption by each user in Tableau Server?

    Sukumar Semalaiappan

      In a Tableau server environment with significantly large concurrent user base(>100) and live data connection to a large multi-terabyte(10-15TB) database table, how can we limit the size of data each user can pull into Tableau server memory? Is it possible to set user level memory usage limitation?

       

      We want to enable users to run adhoc analysis on any part of the data residing in the large table (each user can create new worksheets/views on web), BUT at the same time, we want to ensure that other concurrent users in the system are not impacted and the tableau server doesn't crash due to heavy memory usage from one session. I understand that query processing is done in the database and only the result is returned to Tableau, but I am concerned about the scenario where the query result set is really huge (e.g crosstab showing row level data).

        • 1. Re: Is it possible to limit memory consumption by each user in Tableau Server?
          Zach Leber

          We used to have a big problem when users downloaded too many rows into a crosstab, as the rendered image became larger than 2Gb and then eventually 4Gb which is the current memory limit I believe. In many cases they were using Tableau Server just to get a million rows into Excel. There's no solution to prevent users from exhausting memory which then can impact other users on that same session. Here's what we've done to mitigate:

          1. increase # of VizQL processes to 8 (this may be less necessary in Server 8) in order to minimize the chance that a new user lands on another user's session that had run out of memory. Out of memory was worse than not hitting a cache, and in this case each user was pulling different rows.
          2. Improve the reports to prevent accidental overloads and work with users to develop useful aggregated alternatives.
          3. Document our database tables to allow power users to use other tools like JMP to pull all the data in for analysis.
          1 of 1 people found this helpful
          • 2. Re: Is it possible to limit memory consumption by each user in Tableau Server?
            Russell Christopher

            Sukumar -


            I'd focus on Zach's second idea -- and let's expand on it a bit:

             

            • Use  a highly aggregated (maybe extract-based) data source to power most of the views in your dashboard. These generally don't need to display the lowest grain of the data  to the user anyway. They'll be quite fast.
            • Use a second "leaf level" data source which allows the users to see data at row-by-row level.
            • Use this to feed only 1 "grid/pivot/crosstab-like" chart. Keep this view and data source intentionally simple (fewer fields than the other data source, no calculations, etc.) so that you won't be asking to the db server to do lots of extra work. Base this data source on a Custom SQL statement which uses a TOP <Number> clause to prevent more than maybe 10K rows from being displayed. Don't even show this crosstab to the user until they've already done some filtering with other charts in the dashboard.
            • Add a filter to grid/crosstab/pivot so that it initially shows nothing. For example "Region = 'none'". Only when the user begins to interact with other views does the WHERE clause actually start showing some rows.
            • Create a visual signal that the user is being "throttled" in the dashboard if they show all 10K rows: If [Number of Rows] >= 10000 Then "You're being throttled. Stop being silly, you can't read 10,000 rows anyway" ELSE "All rows displayed" END. Drop this field in the dashboard to make it clear to the user that your dashboard is not a mechanism that they should use to view and/or try to export thousands of rows.

             

            Increasing the # of VizQL processes will only give you a longer runway till problems could start appearing. You want to solve the problem vs. treat the symptoms.

             

            As Zach says, the biggest issue here is generally poor report design. New Tableau authors typically expect users to interact with a dashboard like they always have...

             

            "Thinking in Excel":

             

            • Show the user "everything"
            • Let the user filter down to what is useful to them

             

            In my opinion, this is the wrong approach both in terms of making the dashboard immediately useful to the user, and unnecessarily hurting performance.

             

            Instead, "Think in Tableau":

             

            • Use what you know about the user (groups they are a member of, regions they work in, etc.) to pre-filter the dashboard so that it relevant to the user when it is first rendered.
            • Train the user to use the "Remember My Changes" feature to personalize selections even more. Add "tips and tricks" text in the dashboard to encourage its use and discourage bad behavior.
            • Show the minimum amount of data possible when the dashboard is first rendered
            • If the user want to see more, allow them to "widen" the filters a bit.

             

            Hope this helps!

            1 of 1 people found this helpful
            • 3. Re: Is it possible to limit memory consumption by each user in Tableau Server?
              Sukumar Semalaiappan

              Thank you both for your great suggestions!

               

              On your following suggestion, I suspect using TOP <N> on the data source custom SQL might mislead the end users in some cases.

              "Use this to feed only 1 "grid/pivot/crosstab-like" chart. Keep this view and data source intentionally simple (fewer fields than the other data source, no calculations, etc.) so that you won't be asking to the db server to do lots of extra work. Base this data source on a Custom SQL statement which uses a TOP <Number> clause to prevent more than maybe 10K rows from being displayed. Don't even show this crosstab to the user until they've already done some filtering with other charts in the dashboard."

               

              I think, here we are limiting the scope of the data source even before applying any user filters. I expect the Tableau query to DB with the custom SQL and user filters, will look something like this:

               

              SELECT x, y, sum(z)  FROM

                 (SELECT TOP 10000 x, y, z FROM tbl) --> Custom SQL on the data connection

              WHERE x = 20000; -->  User filter

               

              And its possible that x= 20000 doesn't show up in the inner SQL result set if it is not in TOP 10000. But with x=20000 being present in the DB, Tableau will report that it doesn't exist which is not the expected result. Let me know if this scenario is correct.

               

              But thinking along the same lines, one alternative(to TOP N) we have here is to use Parameter(s) within the Custom SQL based data source which can limit the number of records returned from the DB. Unlike the user filters, the Parameters on the custom SQLs are mandatory and nothing will be returned if they are not set right. The author should be able to identify these parameters based on the business use case(like, a region + a store + a product, etc) and add to the Custom SQL.

               

              And, how do I achieve this (ie. not showing the crosstab)?

              "Don't even show this crosstab to the user until they've already done some filtering with other charts in the dashboard."

               

              Appreciate your help!

              • 4. Re: Is it possible to limit memory consumption by each user in Tableau Server?
                Russell Christopher

                Each database is different of course, but most apply clauses like TOP and LIMIT after the ORDER BY (if any) is utilized. SO, use ORDER BY and this shouldn’t be an issue.

                1 of 1 people found this helpful
                • 5. Re: Is it possible to limit memory consumption by each user in Tableau Server?
                  Russell Christopher

                  Whoops! For the second question (showing/hiding a crosstab), take a look at this blog and the OTHER blog it references:

                   

                  http://tableaulove.tumblr.com/post/43080983480/how-to-show-and-hide-a-tableau-viz-with-action-filters