8 Replies Latest reply: Jul 30, 2013 10:03 AM by Branchbird - Pat RSS

    EQL Join and Nav State

    Jackie_C

      Hi folks,

       

      I've currently loaded two separate sets of data into a single data domain, differentiating them by an attribute such as "dataSet"

       

      I've created a view isolating each data set and have written some EQL queries for some metrics that require me to join the two views,

      The resulting metrics look great but as you navigate and add filters on the page, the metrics "break" and end up with a value of 0.

       

      After looking into this some more, I've discovered that this is because th filters are applied to the two views before performing the join itself.  Depending on the filter, one side of the join inevitably returns no records and hence the result is 0.

      My thinking would have been that the filter be applied after the join had been performed, but clearly this isn't the case.

       

      Has anyone encountered this before and could share any creative ways to get around this?

       

      Thanks!

        • 1. Re: EQL Join and Nav State
          Dan at Branchbird

          Hi Jackie,

           

          Welcome to an age old Endeca Information Discovery challenge!  Joins are a powerful new feature, but can produce results like you're seeing.  If you need to filter by one entity and see the other entity (aka. dataset) filter in kind by some foreign key association, you have to approach this differently.  You could attempt the following:

           

          1) Create your VIEW using AllBaseRecords on one of the two entities so that it never gets "filtered out".  This could be a performance no-no depending on the number of records in your data domain and is only going to work "in one direction" from a pivoting perspective.

          2) Join the two datasets together into one fully denormalized dataset.  Sometimes this just isn't possible, though and/or could produce a Cartesian product that blows the record volumes through the roof.

          3) Employ a custom state manager like Branchbird's as seen here: http://branchbird.com/blog/pivoting-in-endeca/

           

          Thanks,
          Dan

          http://branchbird.com

          • 2. Re: EQL Join and Nav State
            Jackie_C

            Hey Dan,

             

            Thanks for a quick reply!

            I've tried out option 1, but as you said, the results will never respect the nav state, which isn't desirable.

            My fears about option 2 are as you indicated regarding the record volumes.

            It's looking more and more like a custom state manager is the way to go, which isn't ideal but most feasible.

             

            Thanks again for your insight Dan!     

            • 3. Re: EQL Join and Nav State
              Dan at Branchbird

              No problem, Jackie.

               

              During my time in Oracle Endeca product management, this was a well-understood pain point in the product.  I am sure considerations are being made around the proper ways in which the product can handle these scenarios more robustly in the future.

              • 4. Re: EQL Join and Nav State
                Jackie_C

                I sure hope so.  Like I mentioned, it seems that if they could change the process to apply the nav state against the final results rather than the base views, that should resolve this issue, but I'm not the engineer

                • 5. Re: EQL Join and Nav State
                  Jackie_C

                  Hey Dan,

                   

                  I was just going through your post on your custom state manager and had a question as I'm trying to put one together myself.

                  Do you know if it's possible to identify the views being used in a join from the state manager so that one could test the existence of an attribute in that view?

                  From what I read in your solution, you're testing two different data sources, but my issue lies within two sets of data in a single data source to be used in a join.

                   

                  Thanks!

                  • 6. Re: EQL Join and Nav State
                    Dan at Branchbird

                    Hi Jackie,

                     

                    It certainly is possible.  You can retrieve the the view definitions via the API and then you can interrogate the DEFINE statements for the existence of certain attributes. 

                     

                    I would need to tear through the API a bit myself to find the specifics.

                     

                    Thanks,
                    Dan

                    • 7. Re: EQL Join and Nav State
                      Jackie_C

                      My thinking originally was one of two options:

                      1. Determine if any base views in the join does not contain the filtered attribute and not apply the filter there.  But further thinking made me realize that this isn't really possible because of where the join actually occurs.

                      2. Intercept the query request, strip the refinement and have the Endeca Server perform the join first, then apply the refinement to the result set. But again, because of where the join is being processed, it would still apply the refinement to the base views first.

                       

                      Is there truly no way to avoid this issue in an EQL join?  Pre-joining the data in the ETL is not an option due to the volume of resulting records and the custom state manager seems to really only work when the two data sets are in separate data sources and not when performing an EQL join.

                      • 8. Re: EQL Join and Nav State
                        Branchbird - Pat

                        Definitely no way to avoid this in an EQL join today unless you do something like hardcode the refinements into your views and do a bunch of AllBaseRecords gymnastics.  Even assuming that the list of non-applicable refinements isn't' that large, it still probably won't perform all that well.

                         

                        The State Manager solution will definitely work (all refinement actions pass through it) but, having contributed to functionality like this in previous versions (the old 2.2 Multi Metric Chart handled this but not at the State Manager level), there's just a lot to consider and a lot of coding to be done (once you've got "straight refinements" working, need to consider Range Filters, Negative Refinements, Search, etc.).  It seems like you're thinking about it in the right way, though.

                         

                        To be honest, we've considered adding this to our Branchbird State Manager but it hasn't come up with any of our current customers on the current version so we've focused on other enhancements in this area rather than investing.  It's definitely surprising as every OEID implementation I was involved in prior to 2012 used this functionality heavily.

                         

                        Patrick Rafferty

                        http://branchbird.com