10 Replies Latest reply on Oct 8, 2013 7:10 AM by Toby Erkson

    Excel problem with Outer Joins- is there an alternative? (0x8004E14: JOIN expression not supported)

    C Evans

      Newbie question here.

       

      I have just downloaded the Tableau trial. I have set up a mock-up "data warehouse" using tables in an Excel sheet.

       

      Problem-

       

      Two of my tables are Projects and Actions. Every Action is against a Project, but not every Project will have an Action, so this is a LEFT OUTER JOIN. If I add this to my data source definition and test it I get the error-

       

           Database error 0x8004E14: JOIN expression not supported

       

      It looks as if Tableau passes the SQL query to the underlying database. The Microsoft JET database engine has many foibles and hopeless handling of outer joins is one of them.

       

      If I use an INNER JOIN I will potentially lose records for Projects that have no Actions. If I omit the join entirely Tableau complains "Please add a join clause".

       

      Can I pull in the tables as separate data sources and then do the JOIN inside Tableau, and if so will I be able to specify proper outer joins?

       

      Otherwise it looks as If Tableau needs a more capable relational data source to handle even this modest requirement, which rather raises the stakes to even do some testing.

       

      Any tips much appreciated.

       

      Regards: colin_e

        • 1. Re: Excel problem with Outer Joins- is there an alternative? (0x8004E14: JOIN expression not supported)
          Toby Erkson

          I believe what you could do to solve your issue is what's called "data blending" or just "blending".  So what you would do is have two data sources, one for your Projects and the other for your Actions.  Just make sure that the field they join on (which will display in your Dimensions section) is named EXACTLY the same in each data source -- this will allow the blending to work as Tableau will see it as a common field/dimension.

           

          Read up about more of this here --> http://onlinehelp.tableausoftware.com/current/pro/online/en-us/help.htm#multipleconnections_understanding.html

          • 2. Re: Excel problem with Outer Joins- is there an alternative? (0x8004E14: JOIN expression not supported)
            C Evans

            Thanks. "Blending" is not a word I would have know to look for!

             

            I'm a little unclear from the description in the docs exactly what blending does, it says it uses Left joins, but that rather depends which table is on the left. Anyway i'll give it a try.

             

            Thanks for your help.

            • 3. Re: Excel problem with Outer Joins- is there an alternative? (0x8004E14: JOIN expression not supported)
              Richard Leeke

              JET does support outer joins.

               

              My guess is that you have defined the connection as a custom SQL connection and you haven't managed to guess quite the variant of SQL syntax that you need for JET.

               

              I just opened up the Superstore Sales Excel sample workbook and added a left join between [Orders] and [Returns] as a Multiple Table Connection. That worked fine and generated this SQL (from the Tableau logs):

               

              2013-10-05 08:55:48.983 (-,-,-,-) 2584: SELECT [Orders$].[Customer Name] AS [none:Customer Name:nk],

              2013-10-05 08:55:48.983 (-,-,-,-) 2584:   [Returns$].[Status] AS [none:Status:nk]

              2013-10-05 08:55:48.983 (-,-,-,-) 2584: FROM [Orders$]

              2013-10-05 08:55:48.983 (-,-,-,-) 2584:   LEFT JOIN [Returns$] ON [Orders$].[Order ID] = [Returns$].[Order ID]

              2013-10-05 08:55:48.983 (-,-,-,-) 2584: GROUP BY [Orders$].[Customer Name],

              2013-10-05 08:55:48.983 (-,-,-,-) 2584:   [Returns$].[Status]

               

              Converting the Multiple Tables connection to a custom SQL connection the syntax looks like this:

               

              SELECT [Orders$].[City] AS [City],

                [Orders$].[Customer Name] AS [Customer Name],

              ...

                [Returns$].[Status] AS [Status]

              FROM [Orders$]

                LEFT OUTER JOIN [Returns$] ON [Orders$].[Order ID] = [Returns$].[Order ID]

               

              I'm not sure why the "OUTER" doesn't make it through to the eventual query.

               

              Hope that helps.

              • 4. Re: Excel problem with Outer Joins- is there an alternative? (0x8004E14: JOIN expression not supported)
                C Evans

                Oook, I tried that and it "sort of" works.

                 

                It certainly allows data from the different tables to be shown in a single worksheet, so that's a step forward. However it seems to cause problems at the next level with things like filtering.

                 

                Tableau treats the two sets of tables as separate data sources.

                 

                Issues i've found so far-

                 

                1. Originally I found a dimension from the "foreign" table group couldn't't be made a Context Filter. (the Context Filter option was not on the dropdown menu). So for example if the "Owner" field applies to both Projects and Actions, I can't show the projects and Actions belonging to one Owner by using a Content filter.

                  However having removed and re-added the filter the option magically returned. Odd.

                2. Quick filters on Dashboards have the options to Apply To-
                  1. "Only This Worksheet" or
                  2. "All Worksheets using this data source"

                    As the two table groups are treated as separate data sources, again they can't share a common filter.

                 

                Perhaps i'm missing a separate mechanism to apply a common set of filters to all the Views on a Dashboard.

                 

                However it looks as if if I want the combination of outer joins between tables AND straightforward data source management and filtering in Tableau, what I really need is a better database than Excel (or Access I assume as they are both based on the JET engine).

                • 5. Re: Excel problem with Outer Joins- is there an alternative? (0x8004E14: JOIN expression not supported)
                  C Evans

                  Richard,

                   

                  Re:

                  >> My guess is that you have defined the connection as a custom SQL connection and you haven't managed to guess quite the variant of SQL syntax that you need for JET.

                   

                  Not knowingly. I just used the option to Connect to Data -> Microsoft Excel, so I certainly didn't choose a custom SQL connection.

                   

                  >> JET does support outer joins

                   

                  True. In my limited experience with Access though what JET struggles with is compound joins (multi-table joins) where one or more of the joins is an outer join. In Access you're told (in the Microsoft help) to workaround this by building an intermediate table (actually a query) with just the outer join, then join the other tables to the result.

                   

                  That's not an option in Excel because it doesn't DO joins. (Unless Tableau could query a PowerPivot table embedded in an Excel sheet? now there's a thought...)

                   

                  Because Tableau wants a join relationship between every table it imports in a single data source, in my case I need to join 6 tables (so far) where at least two of them have an outer join relationship.

                   

                  I wonder if you adapt your example to add another table whether it will then succeed?

                   

                  Regards: colin_e

                  • 6. Re: Re: Excel problem with Outer Joins- is there an alternative? (0x8004E14: JOIN expression not supported)
                    Richard Leeke

                    Hmmm, not sure what is happening. I've definitely had horrendous great joins with lots of OUTERs in JET - probably more often with CSV files, but I'm sure I've done it with Excel and not had any problems.

                     

                    I've put a 3 table, 2 outer join example together for you with Superstore Sales.

                     

                    If that doesn't give you a clue post a simple example that gives the error (anonymise your data of course, and make sure you save it as a packaged workbook - *.twbx).

                    1 of 1 people found this helpful
                    • 7. Re: Re: Re: Excel problem with Outer Joins- is there an alternative? (0x8004E14: JOIN expression not supported)
                      C Evans

                      I've set up a test case as requested.

                       

                      I hope to attach the twbx file and the source Excel sheet (I'm not sure if that's necessary, but it's synthetic data and tiny). The Tableau file is empty of data because (of course) the data import fails.

                       

                      I have a feeling that it's a RIGHT join that throws JET. Maybe a LEFT join would work, but because Tableau chooses one table from the file as the starting point and INSISTS that you define a join for it, it might well be that you have to define a Right join because that's the shape of your data.

                       

                      Regards: colin_e

                      • 8. Re: Re: Re: Re: Excel problem with Outer Joins- is there an alternative? (0x8004E14: JOIN expression not supported)
                        Richard Leeke

                        I had a quick play with this.

                         

                        The way that you have the join expressed in the multi-table connection translates to this if you convert it to a Custom SQL connection:

                         

                        SELECT [Actions$].[Action] AS [Action],

                        ...

                        FROM ( ( ( [Actions$]

                          INNER JOIN [Dates$] ON [Actions$].[Date] = [Dates$].[Date] )

                          LEFT JOIN [dimOwners$] ON [Actions$].[Initials] = [dimOwners$].[Initials] )

                          RIGHT JOIN [Projects$] ON ([dimOwners$].[Initials] = [Projects$].[Initials]) AND ([Dates$].[Date] = [Projects$].[Date]) )

                          INNER JOIN [dimStatuses$] ON [Actions$].[Status] = [dimStatuses$].[Status]

                         

                        I had a quick hack around at the custom SQL and I think this is probably what you are after:

                         

                        SELECT [Actions$].[Action] AS [Action],

                        ...

                        FROM ( ( ( [Projects$]

                          INNER JOIN [Dates$] ON [Projects$].[Date] = [Dates$].[Date] )

                          INNER JOIN [dimOwners$] ON [Projects$].[Initials] = [dimOwners$].[Initials] )

                          LEFT JOIN [Actions$] ON ([Projects$].[Date] = [Actions$].[Date]) AND ([Projects$].[Initials] = [Actions$].[Initials]) )

                          LEFT JOIN [dimStatuses$] ON [Actions$].[Status] = [dimStatuses$].[Status]

                         

                        Not every project will have an action, so the join of action needs to be a left join. I think the joins to dimStatuses also need to be a left join because they are only there if there's an action (there may be a way to bracket it differently that avoids the extra left join - not sure).

                         

                        I also recreated the join as a multi-table join - that came out in a slightly different order, but I think equivalent:

                         

                        SELECT [Projects$].[Date] AS [Projects$_Date],

                        ...

                        FROM ( ( ( [Projects$]

                          INNER JOIN [Dates$] ON [Projects$].[Date] = [Dates$].[Date] )

                          LEFT JOIN [Actions$] ON ([Projects$].[Initials] = [Actions$].[Initials]) AND ([Projects$].[Date] = [Actions$].[Date]) )

                          INNER JOIN [dimOwners$] ON [Projects$].[Initials] = [dimOwners$].[Initials] )

                          LEFT JOIN [dimStatuses$] ON [Actions$].[Status] = [dimStatuses$].[Status]

                        • 9. Re: Re: Excel problem with Outer Joins- is there an alternative? (0x8004E14: JOIN expression not supported)
                          C Evans

                          Thanks,

                               I clearly need to find out how to convert an automatic connection to a custom SQL one so i can see the generated code.

                           

                          My superficially, given that my file had a RIGHT join of Actions with Projects, and your had a LEFT join of Projects with Actions, you'd think that syntactically at least they would be equivalent.

                           

                          I think when you look into the meaning of the data you've corrected an error in my join structure, and maybe that's the underlying issue.

                           

                          I think that with any moderately complex data structure (lets face it this is just 5 tables in Excel), the automatic data connection interface doesn't really give you much of a clue as to what's going on at the SQL level, and you need to know, so I guess i need to get familiar with the the custom SQL interface (to the extent i can before my 14 day trial runs out...).

                           

                          Thanks for your help. Much appreciated!

                           

                          Regards: colin_e

                          • 10. Re: Excel problem with Outer Joins- is there an alternative? (0x8004E14: JOIN expression not supported)
                            Toby Erkson

                            Colin_e

                            If you need to extend your trial for further testing don't be afraid to contact a Tableau sales rep. and ask for an extension.  It's obvious you're actively testing out the software and need more time   When I was first trying out the software they had a 30-day trial period, much nicer.  Yes, the company did purchase some licenses afterwards