3 Replies Latest reply on Apr 11, 2016 2:13 PM by Rody Zakovich

    LOD calc and Extract: How can I improve performance?

    Francesco Dall'Asta

      Hi all,

      I have a huge dataset of 90 millions rows.

      In order to optimize performance I created an extract and performance is quite nice for normal views/calculations. The problem arises when I use LOD calc, which I really need to use in most of the views.

      In particular, I am using  a fixed calculation for the lowest level of detail  and just after an exclude to not consider one dimension (I do not want to breakdown the data depending on that dimension).

       

      Calc 1: {(FIXED Dimension: avg(measure)}

      Calc 2: {EXCLUDE Dimension 2: sum(Calc 1)}

       

      When I use in the views Calc 2 the whole thing becomes very slow. Modifying one quick filter implies waiting for 3 to 4 minutes. It makes the online (trough tableau server and browser) datadiscovering almost impossible as you can imagine.

       

      I tried to use Context filter since I hear they improve performance. I applied one and the perfomance increased a bit. A part from that one I do not have many other context filter to apply..


      Any Ideas?? How can I solve this bad performance issue?

       

      Thanks!

       

      Francesco

        • 1. Re: LOD calc and Extract: How can I improve performance?
          Simon Runc

          hi Francesco,

           

          So the problem most likely comes from your FIXED LoD (EXCLUDES are generally, depending on the exact situation, not so bad). In general (there are some exceptions where Tableau realises it doesn't have to) FIXED LoDs will send a query creating a temp-table (creating the aggregate, at the FIXED level specified) and then re-join the result back at that FIXED level. As such if you are creating a FIXED at a very low level, it's a big old query and re-join! There currently isn't much we can do about it. One thing that might help, would be to use an Integer Key for the FIXED rather than the human-readable dimension name (Integers are faster than strings...which is one of the reasons why databases tend to use integer keys rather than strings)...I've not tested this, and maybe Tableau already does this behind the scene!

           

          The other way is to change your FIXED to an INCLUDE/EXCLUDE, although if you don't want filters to affect this result, this isn't an option [FIXED aren't affected by regular dimension filters, due to the order of operations Evolution of the Order of Operations Diagram].

           

          The other way would be to use a Table Calc instead, these are (generally) much faster, but if you need to have the VizLoD at a level that you can run the calculation over, butnot in the Viz (there are tricks which you can use to have the viz (apparently) show one level of detail, but the VizLoD is actually at another...it tends to be in the form IF FIRST()=0 THEN...END - let me know if you've not come across this, and I can send you an example).

           

          Context filters can help (if they are filtering out most [80-90%] of the data, but as they push the filtering up the Order of Operations, these filters now affect the FIXED LoD results, in which case you can probably use an INCLUDE/EXCLUDE.

           

          My personal way of developing is that I only use FIXED LoDs in the Proof of Concept work (playing with the data, Viz...etc.) but then get the LoDs written into the database view (feeding the Tableau model) for any production version.

           

          Hope this is of help, and sorry there isn't a magic bullet for this.

          4 of 4 people found this helpful
          • 2. Re: LOD calc and Extract: How can I improve performance?
            Yuriy Fal

            Hi Francesco,

            Simon Runc wrote:

             

            <snip>

             

            The other way is to change your FIXED to an INCLUDE/EXCLUDE,

            although if you don't want filters to affect this result, this isn't an option

            [FIXED aren't affected by regular dimension filters, due to the order of operations

            Evolution of the Order of Operations Diagram].

             

            </snip>

            As suggested by Simon Runc the first try is to replace a pair of your calcs with this one:

             

            // Calc 2 modified:

             

            {EXCLUDE [Dimension 2]: SUM(

            {INCLUDE [Dimension 2], [Dimension]: AVG([Measure])}

            )}

             

             

            Hope it could help a bit.

             

            Yours,

            Yuri

            1 of 1 people found this helpful
            • 3. Re: LOD calc and Extract: How can I improve performance?
              Rody Zakovich

              Like Simon mentioned, there is no silver bullet for this......

               

              There are so many factors at play, it is hard to pin-point 1 or 2 without seeing your Data, the Viz and Pill Arrangement (vizLoD)

               

              As far as, when to use what, here is a response written up by Jonathan Drummey that hits the nail on the head quite well

               

              Re: LOD

               

              Going back a step, the reason I say there are so many factors at play....The issue with performance may not even necessarily  {EXCLUDE Dimension 2: sum(Calc 1)} , but rather whatever ( Calc 1 ) is. That said, it could also be the version of Tableau you are using. Tableau has made great strides in LoDs since V9, and has made Tableau smarter at recognizing the vizLoD and the calculation LoD (Eliminating excess sub-queries when possible).

               

              The best thing you can do is study the Performance Recording

               

              Interpret a Performance Recording Workbook

               

              This will allow where things are getting bottle-necked.

               

              Also, here is a great PDF on how to design efficient workbooks. Lots of good tips/tricks in there to help better understand what is happening.

               

              Designing Efficient Workbooks – the V9.0 edition! | The Last Data Bender

               

              All this being said, it always comes down to......."It Depends"

               

              Regards,

              Rody

              1 of 1 people found this helpful