1 2 Previous Next 28 Replies Latest reply on Jun 24, 2015 12:21 AM by Bora Beran Go to original post
      • 15. Re: How to sum distinct Status
        Simon Runc

        ...should also mention that to optimize your TDE you need to 'unpackage' it from the .twbx 'optimize' and then 'repackage' (i.e re-save as .twbx).


        I can give you a more detailed process if you are unfamilar with this, just let me know.

        1 of 1 people found this helpful
        • 16. Re: How to sum distinct Status

          Can you please explain in Detail or have Pictures.

          Its really interesting to me.

          • 17. Re: How to sum distinct Status
            Rody Zakovich

            Hey Simon,


            I don't know if optimizing the extract would help in this situation, because LoDs cannot be materialized into the datasource like other Row Level Calcs.


            From my understanding, the calculation is sent to the data engine and creates a temp like table, that is self joined back to the materialized datasource.


            But I am not 100% positive of this.. Bora Beran, can you help clear this up?




            • 18. Re: How to sum distinct Status
              Simon Runc

              In which case the speed the 'query' is executed is dependent on your database. You (or your database team) should be able to see the query that is being sent (by Tableau) when the Viz loads up, so from this they maybe able to add some 'Indexes' to fields being used in the 'query' to speed things up.


              We always create TDE's on a refresh schedule (most of our databases only get a daily update so there is no need to be live) and connect to the TDEs as they are faster (generally) for Tableau, and it stops our database being hit all the time....that has just made me think of another thing to check, which is the amount of traffic/work the database is doing when the Viz is loaded (eg. if someone else is running a large query at the same time as your Viz loads this will effect the performance too)


              Hope this helps to give you a few places to look for performance enhancements.

              • 19. Re: How to sum distinct Status
                Simon Runc

                ...Interesting! I hadn't really thought about it before, but yes that makes sense (as to use LoDs live against our databases, the login credentials, to our database, has to have hash table creation permissions). I'll look forward to Bora's insight as I'm using them more and more.


                ...I've said it before, you just learn so much on the forums!!

                • 20. Re: How to sum distinct Status
                  Rody Zakovich

                  So I just quickly re-read What’s new in Tableau 9.0? Part 2 – Level of Detail Expressions « Bora Beran


                  How does it work?

                  Level of detail expressions rely on database queries to calculate the results. If you look at your Tableau logs, you will notice them as sub-queries with inner joins and sometimes with cross joins. If your database doesn’t have a CROSS JOIN operator, Tableau will join without an ON/WHERE clause or use one that always returns true to get the same effect as long as your database supports it.


                  This makes a lot more sense. I think this goes back to your point about adding indexes to the source table in the Database, when using a Live Connection.


                  And yes I completely agree, I've learned so much in forums. Love this community!

                  • 21. Re: How to sum distinct Status
                    Rody Zakovich

                    Also, from the same article


                    Level of detail expressions rely on database queries to calculate the results. Performance heavily depends on your database and size of the data, whether necessary indexes are in the database to be able to perform joins rapidly, complexity and level of nesting in your calculation. If your dataset is large and database is fast, level of detail expressions can give you vast performance improvements over Table Calculations since you can push finer detail calculations directly into the database instead of moving a large dataset over the wire into Tableau on your desktop.

                    You can make your calculations run more efficiently on the database, by avoiding unnecessary cross-joins. Also if you have large tables, you can get performance gains by using MIN instead of ATTR when you know replication will happen. For ATTR, Tableau queries for both MIN and MAX to see if it needs to show * or not. If you know your calculation will replicate, you already know MIN will be same as MAX. This way you can get the same results and shave-off a sub-query.

                    • 22. Re: How to sum distinct Status
                      Simon Runc

                      As Rody suggests this might not be much use here (we'll wait for the guru that is Bora to get the official answer) but below I'll detail out the setps.


                      A .twbx is just a .zip file containing the Tableau Workbook, and the TDE, and as such can be unpacked. If you right click on a .twbx you get an 'unpackage' option


                      Unpackage Workbook.png


                      One you select this you'll now have a TWB, and another folder which if you open up you can find the .tde (and shapes/images...etc.)


                      Unpackage Workbook Folders.JPG


                      now open up the TWB, and on the data right-click, and select Extract and then Optimize




                      This has the affect of 'materializing' row level calculated fields (which means that the result is stored as the value it [the calculation] returns so the actual calculation isn't done every-time you use that dimension/measure). If you change the calculation, you need to re-optimize (I'm not 100% sure on that but I do just to be safe), and when the TDE is refreshed from the datasource the materialization is redone as part of the rebuild process (which happens on our daily refresh schedule).


                      You can now save as .twbx (in fact you can take the .twb and folder, 'invert2 - SR.twb files', in my example, zip them up and then change the .zip affix to a .twbx) and then the extract inside the .twbx is the new optimized one.


                      I tend to always work with .twb and have my .tde saved in a particular folder, and only create a .twbx if I'm sending to someone. That way I can optimize at will!


                      Hope that helps, but here is a few links


                      Optimizing Extracts



                      • 23. Re: How to sum distinct Status
                        Bora Beran

                        LOD calc is a subquery that gets joined in. We currently do not materialize LOD calcs because TDE doesn't have the concept of a value having been pre-computed at a particular LOD. It assumes everything is row level. We need to introduce that to TDE first to prevent replication skewing aggregate results. Without that for example {fixed State : SUM(sales)} would repeat the value for every row in the table (assume every row is a transaction) in that state. So computing the AVG of this column would give some sort of a weighted average where the states with more transactions would impact the average more than the states with fewer transactions.


                        Is the desire to materialize resulting from performance concerns?

                        • 24. Re: How to sum distinct Status
                          Rody Zakovich

                          Hello Bora,


                          Thank you for responding (Big fan!).


                          I think this was more of a general inquiry. My question was, though an LoD computes Row Level results, it is not a Row Level Calc in the sense that we can optimize the Extract, and write the results in the TDE. So optimizing the extract would not necessarily improve performance of an LoD calc, unless the LoD calc addresses a materialized Row Level Calc?


                          If I am completely off base, please let me know. I am starting to use LoDs more and more, and am very curious to how to improve performance with them.


                          Thank you again!


                          • 25. Re: How to sum distinct Status
                            Simon Runc

                            hi Bora,


                            Thanks for your insight, and that makes sense. Yes the question came up due to performance concerns, but as it turns out they are using a live connection anyway.


                            I've suggested they look at the query being sent to their database (on the live connection) and add indexes into the required fields to speed things up.

                            • 26. Re: How to sum distinct Status
                              Matt Lutton

                              Good stuff in this thread -- hope folks see it!    

                              • 27. Re: How to sum distinct Status
                                Bora Beran

                                That is correct.You will still get benefit from optimized row level calcs and through them in other calcs that use them.

                                • 28. Re: How to sum distinct Status
                                  Bora Beran

                                  It would be great if you could share with us the SQL that is being generated (creating a perf recording when connected live and sharing the recording workbook if possible would help a lot) as well as the calculations in the sheet.

                                  1 2 Previous Next