1 2 Previous Next 17 Replies Latest reply on Jan 27, 2016 12:05 PM by Shawn Wallwork

    Evolution of the Order of Operations Diagram

    Alexander Mou
        • 1. Re: Evolution of the Order of Operations Diagram
          Alexander Mou

          Joshua Milligan

          So for the secondary data source filtering, what is your order of operation?

          • 2. Re: Evolution of the Order of Operations Diagram
            Joshua Milligan

            Here is a quick view of my understanding of Tableau's order of operations.  I suspect there are exceptions, caveats, and even errors in my understanding, so I welcome any corrections or clarifications.

             

            Order of Operations.png

             

            From what I can tell, the extract filters and data source filters would be applied in the same way as expected. Secondary Dimension and Measure filters will be applied to the secondary source next*

             

            Additionally, Tableau will apply filters to the secondary source based on values present in the primary source for enabled linking fields.  So, for example, if you are linking on Region and the values East, South, and West are present (after all primary filters) in the primary source, then the secondary source will be filtered to East, South and West regions.

             

            *The exception appears to be if the secondary field used as a field is a linking field.  In this case, the filter seems to be applied after (in the process of) blending (which is what I show above).


            I also suspect that data densification occurs in several places in the pipeline.  What I show above is specific to "Show Missing Values" on dates which are used as linking fields.  In that case, the missing values are filled prior to blending.  But I think there are other cases where densification happens elsewhere.


            Bottom line: the pipeline is incredibly complex. I think it would be a great exercise to work through it as a community and refine the model.  Jonathan Drummey probably has some of the best insight into the order of operations and could probably clarify anything that isn't precise in my understanding.


            Best Regards,

            Joshua

            6 of 6 people found this helpful
            • 3. Re: Evolution of the Order of Operations Diagram
              Alexander Mou

              This is my latest diagram for easy reference. Made in Tableau.

              2015-10-27 10_34_36-Order of Operations - A.M. _ Tableau Public.png

              3 of 3 people found this helpful
              • 4. Re: Evolution of the Order of Operations Diagram
                Jonathan Drummey

                I'd responded (via my phone) to the post on your blog, Alexander, it looks like the internet ate it. Thanks for taking a swing at compiling the latest information!! I'll put back together what I wrote:

                 

                Unfortunately, the Tableau consultant you referred to has incorrect information in two ways:

                 

                - Data blending begins to be computed *after* dimension filters are applied. I set up a quick example in this Tableau Public workbook https://public.tableau.com/views/blendingorderofops/blendisafterdimensionfilterprimary?:embed=y&:display_count=yes&:show…, the "blend is after dimension filter primary" and "blend is after dimension filter secondary". If the data blending was applied before dimension filters then we'd expect to see the two measures be different (because the primary sum of Sales would be filtered prior to the secondary or vice versa), they aren't. The order of operations around the two types of data blending is actually more complicated than that, see my TC14 session on Extreme Data Blending at Post-Conference Materials | Tableau Conference 2014 for details.

                 

                - Computed Set filters and FIXED LODs are effectively computed at the same level of precedence. The diagram as it exists makes it appear that a computed Set would be applied before the FIXED LOD is computed and that is not the case. The general process is that the Set & FIXED LODs are computed as separate subqueries that are inner-joined back to the data, though Tableau will get smarter when it can to reduce the number of queries. Here's an example https://public.tableau.com/views/blendingorderofops/LODvsSet?:embed=y&:display_count=yes&:showTabs=y where filtering on the Set doesn't change the FIXED results and vice versa.

                 

                FYI, the way I figure these things out is mostly by setting up test cases (usually on a data source that uses SQL, I'm not very skilled at reading Tableau's query language used for extracts and the new connector for Excel & text files) and then going over the log files.

                 

                Finally, there are a few useful (in my opinion) elements missing from the diagram, particularly how context filters can be based on Set, FIXED LOD, or dimension filters, the different order of operations within dimension filters, plus densification, and hiding marks. Check out Question on how to create a product velocity ca... | Tableau Support Community and the comments after it for more details.

                 

                Jonathan

                2 of 2 people found this helpful
                • 5. Re: Evolution of the Order of Operations Diagram
                  Alex Blakemore

                  Alexander,

                   

                  One thing I suggest refining in your visualization before it goes viral.

                   

                  Tableau ignores data source global filters for secondary data sources, so the orange bar near the top left of your diagram is incorrect. http://onlinehelp.tableau.com/current/pro/online/windows/en-us/help.htm#filtering_global.html

                   

                  I'm not sure why they have this restriction, but apparently they do. If you'd like it removed, then upvote http://community.tableau.com/ideas/5531
                  Alex

                  • 6. Re: Evolution of the Order of Operations Diagram
                    Jonathan Drummey

                    Hi Alex,

                     

                    I replied on the Idea, there's a terminology difference between a data

                    source filter (which actually does what you say) and a global filter (which

                    is what you are wanting to act like a data source filter actually does).

                     

                    Jonathan

                     

                     

                    On Thu, Oct 29, 2015 at 5:44 PM, Alex Blakemore <

                    • 7. Re: Evolution of the Order of Operations Diagram
                      Alexander Mou

                      Alex,

                      We are talking about the data source filter on any data source, regardless of being primary or secondary. See the picture below.

                      2015-10-29 15_11_31-Tableau - Book3.png

                      • 8. Re: Evolution of the Order of Operations Diagram
                        Alexander Mou

                        Jonathan,

                        1.The diagram shows exactly "Computed Set filters and FIXED LODs are effectively computed at the same level of precedence. "

                        I could have misunderstood you here.

                         

                        2.Regarding dimension filters, I figured out the following:

                        Linking dimension filters work after blending.

                         

                        Non-linking dimension filters seem to work before blending. But as long as they only filter their respective data source with or without blending, it doesn't matter the order of operations between blending and non-linking dimension filters. We can thus assume all dimension filters operate after blending.


                        I could be wrong. Would love to figure out the correct order of operations. Let's make it evolve.


                        • 9. Re: Evolution of the Order of Operations Diagram
                          Alexander Mou

                          Here is a new update on the diagram with a timestamp. Would love to hear feedback from you guys.


                          Vizible Difference: Update of the Order of Operations Diagram 11/8/2015

                          Dashboard 1 (2).png

                          • 10. Re: Evolution of the Order of Operations Diagram
                            Jonathan Drummey

                            1.The diagram shows exactly "Computed Set filters and FIXED LODs are effectively computed at the same level of precedence. "

                            I could have misunderstood you here.

                             

                            The vertical ordering of the diagram implies that one thing occurs after the other. Since Computed Set filters and FIXED LODs are at the same level of precedence, i.e. simultaneously, I think they should be side-by-side.

                             

                            2.Regarding dimension filters, I figured out the following:

                            Linking dimension filters work after blending.

                             

                            Non-linking dimension filters seem to work before blending. But as long as they only filter their respective data source with or without blending, it doesn't matter the order of operations between blending and non-linking dimension filters. We can thus assume all dimension filters operate after blending.

                             

                            I think I can see how you'd come to that conclusion because the net effect of most regular dimension filters is that they can often look like they are applying after data blending, however that's not how data blending works and making that assumption could lead into some troubles later. I'll try to explain:

                             

                            a) There are two types of data blending, DB1 and DB2 and they work differently.

                             

                            a1) In DB1 there are no dimension filters for secondary sources, only dimension filters on primary sources and those are applied before the blend. See slide #56 in Dropbox - Extreme Data Blending 20140908.pptx.

                             

                            a2) In DB2 we can filter on secondary sources, so filters get applied slightly differently because Tableau generates multiple queries to each source. See slide #62 in the presentation above. These slides were created by me out of a whole lot of looking at queries in the logs and then validating my theories with the data blending developers.

                             

                            b) Filters on dimensions are added to the WHERE clauses of the queries Tableau issues to the source, it doesn't matter whether they are linking dimensions or not. In DB2 the interaction between what linking and non-linking dimension filters has to do with the effect of the filters that you are seeing is about whether the filters make the data sparse or not. Here's an example. Using Superstore and a duplicate with the linking dimension as Department, here's a basic view (this is using DB1 blending):

                             

                            2015-11-09 08_57_32-Tableau - Book8.png

                             

                            Now I exclude Computer Peripherals from the secondary. The SUM(Sales) from the secondary changes to $10.6M because that is excluded from the results.

                             

                            2015-11-09 09_00_08-Tableau - Book8.png

                             

                            Now I exclude the other three Categories in Technology from the secondary Category filter. Now the entire Technology dimension goes away:

                             

                            2015-11-09 09_01_37-Tableau - Book8.png

                             

                            The reason I use this example is that it shows that even a filter on a non-linking dimension ultimately effects the results returned from the primary source, and this is visible in the queries that Tableau issues.

                             

                            c) No matter what style of data blending, though, queries are issued to the data sources that include the dimension filters on each and *then* Tableau links the two sources, effectively inner-joining the result set of dimension filters on one or both sides of the blend. (As an extra complication any linking dimension values that only existed in the primary and not in the secondary are preserved, which is why it looks like a left-join). The result of this is that it does look like the filters are applied after the blend, when in fact they've been applied earlier when it comes to the queries on each source and then in the blend the "inner join" makes the filters apply to the entire result set.

                             

                            I realize this is getting really into the weeds here, however what I've found with helping people out is that they are often looking at the queries in order to identify trouble spots and find optimizations so something that leads them to assume that things work one way when they in fact work differently is unhelpful to them and I try to avoid that.

                             

                            3) This is with regards to your updated order of operations slide that you posted here: Re: Evolution of the Order of Operations Diagram. It is still incorrect when it comes to the order of data blending & dimension filters. Besides point 2c) above, there's another key factor and that is data densification. Tableau figures out the domains, applies densification, and then blends in results from the secondary. Here's a way I've use this to my advantage: http://drawingwithnumbers.artisart.org/wp-admin/post.php?post=899&action=edit.

                             

                            Here's another example, I'm using superstore and doing a self-blend on Order Date, filtering out 2011 from the primary: This is using DB1 blending:

                             

                            2015-11-09 08_40_27-Tableau - Book8.png

                             

                            Now I've turned on Show Missing Values for YEAR(Order Date). SUM(Sales) for 2011 is still not returned at all from the primary source, but with the densification it is returned from the secondary source. This is still using DB1 blending:

                             

                            2015-11-09 08_42_24-Tableau - Book8.png

                             

                            Now here's one that I haven't run into before because I'm careful not to set up views this way. I've swapped the YEAR(Order Date) filter from the primary for one from the secondary. This triggers DB2 blending. My expectation is that 2011 would be filtered out, then densification would put 2011 back in just like above however it is completely removed. I'm not sure what is happening here, this is a case where it certainly looks like the filter is being applied after densification.

                             

                             

                            2015-11-09 08_43_57-Tableau - Book8.png

                             

                            I'll have to do more testing, my suspicion is that Tableau hasn't changed the order of operations here and instead there's a certain ruleset around how densification is performed in this case. This is based on my experiences with the sheer strangeness of densification behaviors.

                             

                            Ultimately for point 3) about blending and where it is in the order of operations, I think it's really important with graphs like this to be accurate and I have not yet seen an argument that convinces me that data blending happens before dimension filters are applied, while I can provide many arguments that data blending happens *after* dimension filters are applied.

                             

                            Jonathan

                            1 of 1 people found this helpful
                            • 11. Re: Evolution of the Order of Operations Diagram
                              Alexander Mou

                              Jonathan Drummey

                               

                              Somehow I missed this reply. I only found out this reply a couple days ago. And now I got time to go through it. Thanks for the very detailed reply!

                               

                              1.Initially I thought blending and dimension filtering are permutable logically. Your research further convinced me that the dimension filtering is done before the blending. I will update the diagram accordingly.

                               

                              2.Regarding densification

                              I feel like it is a bug in the implementation by Tableau.

                              2.1 dimension filter in primary

                              When year 2011 is filtered in the primary, both 2011s in primary and secondary should go away, with or without densification

                               

                              2.2 dimension filter in the secondary

                              Yes, I expect it to be like a left join where 2011 in the primary should stay, while 2011 in the secondary should be empty, with or without densification.

                               

                              If possible, please confirm or infirm the bug with the folks at Tableau.

                              • 12. Re: Evolution of the Order of Operations Diagram
                                Alexander Mou

                                Regarding the position of densification in the order of operations, I expect it to be as follows

                                 

                                dimension filter

                                densification

                                blending

                                 

                                I suspect that the example you provided could be an anomaly.    I could be wrong though.

                                • 13. Re: Evolution of the Order of Operations Diagram
                                  Alexander Mou

                                  Regarding the place of LOD, the above is provided by Tableau officially.

                                  It uses branching to indicate parallelism. I am using a similar way to draw the diagram. I agree it is not the best way to explain a side by side computation.

                                  • 14. Re: Evolution of the Order of Operations Diagram
                                    Alexander Mou

                                    The latest update incorporating Jonathan's contribution.

                                    Vizible Difference: The Order of Operations Diagram: Update on 12/10/2015

                                     

                                    Dashboard 1 (3).png

                                    5 of 5 people found this helpful
                                    1 2 Previous Next