6 Replies Latest reply on Jun 20, 2018 5:41 PM by Yuriy Fal

    LOD query in EXASOL (OR clause in JOIN statement)

    Daniel Teo

      Hallo,

       

      I have a question on the (EXASOL) query Tableau generates when writing a LOD calc, more specifically whether there is any way I can influence how Tableau writes its queries.

       

      I have a LOD calc (in Tableau 9.3.3) that looks something like this, which calculates the minimum date for each player profile. I use a Include/Exclude instead of Fixed because the minimum date is dependent on the dimensions in the viz or filters.

       

           {INCLUDE [Profile ID]: MIN(

           {EXCLUDE [Game End - Date (Dim)]: MIN(

           [Game End - Date]) })

           }    

       

      I then have a 2nd calculation to count the number of new users:

       

      COUNTD(IIF([Game End - Date] = [LOD calc], [Profile ID], NULL))

       

      So Tableau generates a subquery and joins it with the main fact table, which is what I want, except for the fact that Tableau adds an OR clause in the JOIN statement:

       

           INNER JOIN

             (

             ----

             ) "t0"

           ON ("PLAYER_PROFILE_DIM"."PROFILE_ID" = "t0"."PROFILE_ID")

          OR (("PLAYER_PROFILE_DIM"."PROFILE_ID" IS NULL) AND ("t0"."PROFILE_ID" IS NULL))

       

      When I remove the OR clause and run the query in my SQL IDE, I get the results I want in about 3 seconds, but the query doesn’t finish (I killed it after 10 minutes) when I leave it in. So my question is: is there a way to tell Tableau not to add this OR clause?

       

      Thanks!

        • 1. Re: LOD query in EXASOL (OR clause in JOIN statement)
          Yuriy Fal

          Hi Daniel,

           

          Please try using [Profile ID] from your Dimension Table (not your Fact one) --

          if you got a kinda star scheme (and with Tableau Assume Referential Integrity flag set to ON).

           

          Hope this could help.

           

          Yours,

          Yuri

          1 of 1 people found this helpful
          • 2. Re: LOD query in EXASOL (OR clause in JOIN statement)
            Daniel Teo

            Thanks for the reply Yuriy.

             

            It's not really clear from my query, but I am using the dimension table. "t0" is the subquery Tableau generates when using the LOD, but it's still going to the dimension table in that case. I've actually been discussing this issue with Tableau Technical Support for the past couple of weeks, and it will probably be a bug fix for EXASOL live connection.

            • 3. Re: LOD query in EXASOL (OR clause in JOIN statement)
              Yuriy Fal

              Hi Daniel,

               

              What version of Tableau are you using?

               

              My testbed was Tableau 10 Beta 4 & EXASolo 5.0.7

              The datasource is a classic star scheme -- a fact table

              with some dimension ones, everything inner-joined on PK-FK.

               

              The "right" (fast) select statement has been generated only when using this LOD:

              {INCLUDE [MSA_ID (DIM_MSA)] : MIN( {EXCLUDE [TRANS_DATE]: MIN( [TRANS_DATE]) }) }

               

              As you can see above, [MSA_ID] has come from a DIM_MSA dimension table,

              and it is a Primary Key in it. [MSA_ID (DIM_MSA)] is the alias name given by Tableau.

               

              When [MSA_ID] has come from a fact table, the resulting select was way too slow.

              Same thing (slow) when [MSA_NAME] field from a DIM_MSA dimension table

              (which is not a PK, of course, just an ordinary dimension attribute field) has been used.

               

              Please find the attached Performance Recording workbook (Tableau version 10 Beta 4).

              Hope this could help a bit.

               

              Yours,

              Yuri

              1 of 1 people found this helpful
              • 4. Re: LOD query in EXASOL (OR clause in JOIN statement)
                Daniel Teo

                Hi Yuriy,

                 

                Yes indeed: we came to the same conclusion The actual problem I found is that the ID has to be part of a join in the data model. However, we join on Profile_Key instead of Profile_Id, where one Profile_Id can have multiple Profile_Keys over time. We solved this for now by creating a Profile_Current_Dim view which has only one record per Profile_Id, and join that to the first dimension table. When I use the Profile_Id from that CURR dimenstion view, it removes the OR clause.

                 

                This is not an ideal solution yet: if I want to calculate the min date per channel by dragging in the channel name for example, it will add the OR clause on the channel name join, since that field is not part of an actual join in the data model. Obviously I want to use channel_name instead of channel_key when using it in an actual viz. Fortunately channel_name contains <10 unique values, so a CROSS JOIN still performs ok.

                 

                By the way, I tested it in both 9.1 and 10 Beta 3. Same behaviour in 10, but interesting to note that in 9.1 the OR is always there regardless which profile_id I use in the LOD, implying that this issue was worse in the past and has been partially solved now. I haven't tested it myself, but the engineer indicated that other databases do not exhibit this behaviour, hence our suspicion that this will become a bug fix.

                 

                Regards,

                Daniel

                1 of 1 people found this helpful
                • 5. Re: LOD query in EXASOL (OR clause in JOIN statement)
                  vita nesvetailov

                  юрий, вы можете помочь со сложной задачей или обучить advance level Tableau?

                  • 6. Re: LOD query in EXASOL (OR clause in JOIN statement)
                    Yuriy Fal

                    Hi Vita,

                     

                    I've replied with a DM.

                     

                    Yours,

                    Yuri