14 Replies Latest reply on Feb 7, 2013 7:15 AM by Chris Turnbull

    Calculation for Total Help and Slow Performance

    Chris Turnbull

      Hi All,

       

      I have put together the attached workbook with a friction of the actual data and I am I am at little bit of a loss with 2 Calculations.

       

      If Yoyu have a look at the bottom Axis the 2 Calculations that I am struggling with are S A EXP and S A EXC and in effect I just want to the Calculation to add the Cells above.  The amount of Cells may change depending on the Subjects.

       

      These 2 fields are Served by:-

       

      S A EXP - Number Achieving Expected Progress

      S A EXC - Number Achieving Exceeding Progress

       

      These Calculations are put together with a technique from here http://drawingwithnumbers.artisart.org/customizing-grand-totals-part-3/ and the formatting is also put together from Jonathan Drummey's fantastic formatting Showcase.

       

      Another issue I have is the performance of the Workbook - no matter how much data is in the extract the redrawing of the grid always takes about 25 secs which is far too long.  I think this is caused by some of my novice programming and also the limitation of the Tableau formatting - mainly pouint one I would think.

       

      Any help would be great and I don't mind a critique of my poor programming.  The only good thing for me is that I have the functionality.

       

      Thanks

       

      Chris

        • 1. Re: Calculation for Total Help and Slow Performance
          Alex Kerin

          I tried optimizing the extract - that didn't seem to change much. Also not many people will be able to open this as it's v8.

          • 2. Re: Calculation for Total Help and Slow Performance
            Chris Turnbull

            OOPS Sorry about the V8 - I totally forgot about it.

             

            Thanks for having a look at it.

             

            Are there any glaring schoolboy errors in it?

             

            Chris

            • 3. Re: Calculation for Total Help and Slow Performance
              Alex Kerin

              I haven't used the methods before - it all looks okay....

               

              Edit: I wonder if you can do something with index() = if I sweep over a single mark (e.g the middle 15), there are 30 marks associated with it

              • 4. Re: Calculation for Total Help and Slow Performance
                Richard Leeke

                Hmmm - what version of the beta are you guys using? Every time I try to open the workbook Tableau crashes. I'm on 8000.13.0120.2115 which I'm pretty sure is beta 6. Will tell beta support.

                • 5. Re: Calculation for Total Help and Slow Performance
                  Chris Turnbull

                  Hi Richard

                   

                  I am on that vesion to - which I think is the latest version.

                   

                  Chris

                  • 6. Re: Calculation for Total Help and Slow Performance
                    Richard Leeke

                    I've just discovered that if I open Tableau and then open your workbook from the File->Open menu it works, it's only if I drag the workbook and drop it on the Tableau icon on the desktop that it crashes. (And having done that all attempts to open Tableau result in it hanging until I clear the log directory). Very odd - I always open workbooks by dragging them on to the icon.

                     

                    Anyway, I'll take a look at the workbook and see if I can spot where the time is going.

                    • 7. Re: Calculation for Total Help and Slow Performance
                      Chris Turnbull

                      Thank you please don't laugh at all the bloat with the calculations in there.

                       

                      I am sure there must be an eaier way but do not have the calcs knowledge yet.

                       

                      Cheers Chris

                      • 8. Re: Calculation for Total Help and Slow Performance
                        Richard Leeke

                        Actually the crashing is nothing to do with this workbook, it's whether I use the Tableau 8 icon on my desktop (always crashes) or the Tableau 8 icon in my system tray (works fine).

                        • 9. Re: Calculation for Total Help and Slow Performance
                          Chris Turnbull

                          Dropping on the desktop icon is fine for me with all the workbooks that Ihave tried.

                           

                          I'm on Windows 7.

                          • 10. Re: Calculation for Total Help and Slow Performance
                            Richard Leeke

                            It got worse - I couldn't even start Tableau from the system tray. A reboot fixed it.

                             

                            I took a quick look at your workbook, though I haven't tried to get my head around what you are doing in those calculations. What I can do is tell you where the time is going. The best way to see that in version 8 is to turn on the Performance Recorder (from the Help menu) refresh the sheet and then turn it off again. It gives you a Tableau workbook showing where the time goes.

                             

                            I have filtered that down to exclude the time in just starting up Tableau and opening the workbook, so it just shows the time for refreshing the sheet :

                             

                            Performance Breakdown.PNG

                             

                            What that is telling you is that most of the time is in queries (green). It does lots of queries, the first one took 1.5 seconds and the rest took about 0.3 seconds each. I guess the reason for all the different queries is the way you have so many different sets of calculations of the colour and text shelves.

                             

                            I've attached the performance workbook for you to have a look at. If you click on a bar it shows you the (first part of) the query (though as this is a data extract its not a SQL query, but the Tableau proprietary query language used by the data engine. You should still be able to work out which query relates to which calculation.

                            • 11. Re: Calculation for Total Help and Slow Performance
                              Chris Turnbull

                              Richard

                               

                              Thabks for taking a look.

                               

                              I will give you a n explanation of what I am trying to achieve.

                               

                              A student will come to us from a primary school with  a level which is called a KS2 Level and that will be a number from 2-5 but could be a B or an N if they didn't take the test.

                               

                              By the time they leave us at 16 they should have progressed at least 3 Levels.  The KS2 Level is measured against a GCSE Grade which can be a *(10), A(9) down to a G(3).  The KS2 Level is taken from the GCSE and from that we know how many levels of progress they have made.

                               

                              The Grade Highlight will give me a colour for the Levels of Progress that have been made so Light Green is Expected (3 Levels of Progress) Dark Green is Exceeded (4 or More) and Light Blue Below (2 or Less).  I work this out with an If Statment that works out which cell it is.  I know that is not the way to do it - I should be working it on the Progress field which gives me how many level they have progressed but I ran into issues that I couldn't solve.

                               

                              I originally had the Data as   a single line for each student for each subject they study and did calculations with that and because of the slowness I did the groupo by's in sql to give me the aggregated subject totals instead but I had the same speed issues.

                               

                              I have attached a workbook with a second sheet -  This is how I originally put it together bit I couldn't workout how to fill the Cells that were empty with Colour and I then also didn't know how to add the Calcs to the right handside.

                               

                              I hope this makes sense - I just wanted to try and show what I was trying to achieve.

                               

                              Thanks

                               

                              Chris

                              • 12. Re: Calculation for Total Help and Slow Performance
                                Chris Turnbull

                                Hi All,

                                 

                                The good news for me is that I have worked out that the performance issues are being not by the colored grid but the Calculation on the right hand side.

                                 

                                The following Calcs when taken out bring the redraw down to a couple of seconds.  The first part of each line below is the Dual Axis name with the calcultion after the -.  Looking at the SUM.

                                 

                                S A EXP - Number Achieving Expected Progress

                                S % A EXP - S % A EXP Progress

                                                   - S % A EXC Highlight

                                 

                                S A EXC - Number Achieving Exceeding Progress

                                S % EXC - S % A EXC Progress

                                                - S % A EXC Highlight

                                 

                                I can see why it is slow with all teh SUM's in the 2 progress Calcs but the bad news for me is that I am at a loss of how to put it right so any help would be appreciated.

                                 

                                Thanks

                                 

                                Chris

                                • 13. Re: Calculation for Total Help and Slow Performance
                                  Jonathan Drummey

                                  Hi Chris,

                                   

                                  You are certainly making Tableau work to compute across those 494 records! Like Richard, this workbook crashed the Tableau beta (on my laptop), I was able to load it on my work machine.

                                   

                                  The worksheet initially took 40+ seconds to load on my machine. I optimized the extract and the load time went down to 22 seconds. That might be acceptable.

                                   

                                  In addition, here are some further optimizations to get a little more speed out of this:

                                  - Wherever possible, change ATTR() to MIN(). ATTR() is a really helpful function, but when you need speed, go with MIN().

                                  - For the Grade __ Highlight fields, use numbers and aliases instead of strings.

                                  - The KS2 Level is a string instead of a number, a number might be faster.

                                   

                                  Here are a couple of other suggestions that would be more work.

                                   

                                  - Move more of the aggregation into the SQL before Tableau, particularly the __ SUM fields. I imagine this would be a huge gain, possiblly an order of magnitude because those SUMs get used so much.

                                  - Aggregating the total data set as much as possible in the SQL before Tableau would also help, but not as much as the above step.

                                  - Theoretically, splitting the worksheet into two on the same dashboard would get you a peformance gain between 1x and 2x faster in v8, but that code hasn't been turned on in the beta yet.

                                  - And going way outside the bounds of normal reasoning, maybe moving this to Excel. The conditional formatting here is taking the whole multiple axis crosstab hack to an extreme.

                                   

                                  Jonathan

                                  • 14. Re: Calculation for Total Help and Slow Performance
                                    Chris Turnbull

                                    JONATHAN

                                     

                                    Thanks for the reply, I had tried the SQL tricks above but I need the data prtty much unfettered. 

                                     

                                    Anyway I have had decent cople of Days and I nearly have what I want mainly thanks to you and Richard and Forum Posts that I have seen from you and your website was great for the Totals.

                                     

                                    I have attached the new effort and it is a lt better put together but there are a couple of issues I would like your view and expertise on if you have the time.

                                     

                                    Questions

                                     

                                    My Main issue is that not all Subjects will have all grades and Levels so the Size of the Grid will change and the Gap between the Grades Grid and Totals Grid widens which isn't ideal. (If you click the Art of in the Subject Dropdown you will see the Grid become smaller) - Is there anyway that I can always have a Grid that shows * - U and 3 down to Total.

                                     

                                    You will see that I have created 2 Worksheets and put them on a Dashboard.  This Work Great when you are only looking at a couple of subjects but when you have more than a screen full each window has a slider - is there anyway to sync the 2.  I know you can highlight but you would still have to do a slide and line up.

                                     

                                    Formatting

                                    I have had to use a square - Is there a way to have a Vertical Line down Each Column as I could not find the option.

                                     

                                    Thanks

                                     

                                    Chris