12 Replies Latest reply on Oct 14, 2013 10:04 AM by Mandeep Singh

    Tableau not working efficiently with read-only Databases

    Mandeep Singh

      If I run a custom query on read-only DB's then the results which I am getting are wrong and moreover its taking lot of time for the response.

      Since, they are read-only DB's I am not able to create views for it as I don't have the priviledges for those DB's.

       

      Is there any solution to tackle this issue.

       

      Thanks

        • 1. Re: Tableau not working efficiently with read-only Databases
          Robert Morton

          Hi Mandeep,

          You should consider using the single-table or multi-table connection interface instead of Custom SQL. It's especially important to avoid prematurely grouping and aggregating your data in Custom SQL, since that can lead to mis-computed aggregations in Tableau and can easily cause performance problems. Remove any existing GROUP BY and ORDER BY clauses from your Custom SQL. Furthermore, remove any WHERE clauses and replace them with normal Filters in a Tableau worksheet, or define them as Data Source Filters. If you continue to have bad performance (since many databases have trouble with the subqueries that Custom SQL requires), then consider making a Data Engine extract.

          -Robert

          • 2. Re: Tableau not working efficiently with read-only Databases
            Toby Erkson

            Robert Morton wrote:

             

            ... Furthermore, remove any WHERE clauses and replace them with normal Filters in a Tableau worksheet, or define them as Data Source Filters...

            Robert, so you're saying that using a WHERE clause isn't a good idea to filter the data down?  I would think without them there would be a lot more data coming in if relying upon [sheet] Filters.  Why would Data Source Filters be the better solution?

             

            I'm creating a reference document for my end users and your knowledge is good to have.  The one spot I'm now fuzzy on is the WHERE bit as I though that would be a good way to narrow down a data pull (and subsequently an extract).

            • 3. Re: Tableau not working efficiently with read-only Databases
              Robert Morton

              Hi Toby,

               

              Here's a clarification: If Custom SQL is absolutely necessary, then you should certainly try to reduce the amount of data that the Custom SQL connection works with by including any appropriate WHERE clauses.

               

              However if you are trying to replace your Custom SQL connection with a simple single-table or multi-table connection, perhaps you may be concerned about omitting important filters that existed in the WHERE clause of your Custom SQL. In that case, you can use Data Source Filters to ensure that all queries that Tableau generates will include those filter criteria.


              Last, since Data Source Filters are independent of the underlying connection, they are applied only after changing your connection from a live database connection to a Data Engine extract connection. If you wish to have filters applied at the time of creating a Data Engine extract, you must define Extract filters for this purpose. I believe that Tableau bootstraps this process for you by automatically suggesting that the Data Source filters be used as Extract filters as well, but this is optional.

               

              I hope this helps,

              Robert

              • 4. Re: Tableau not working efficiently with read-only Databases
                Shawn Wallwork

                Robert, thanks for the info. I've been following your posts on Custom SQL, Multiple Tables & Blends instead of Joins keenly, because I'm working with a client who is trying to work all this out. While I have several questions I'll limit it to one for now.

                 

                Background: When I first started working with them they presented me with a data connect with a multi-table join using 6 tables, and literally 75-100 fields. And they only needed 6-10 fields for their workbook. They do NOT have any control or edit permissions to the database (it's third-party vendor). They can enter data, report out data, but they can't create new tables, or edit existing tables. To improve performance I got them to move the connection to Custom SQL and edit it down to the 6-10 necessary fields. This of course greatly improved performance.

                 

                Question: Given these circumstances, is there any other way to do this, without using Custom SQL? Am I missing something obvious? When establishing the live connection to their server, is there anyway to keep that server from serving up the kitchen sink, and thus avoid using Custom SQL just to edit the connection.

                 

                Thanks,

                 

                --Shawn

                • 5. Re: Tableau not working efficiently with read-only Databases
                  Toby Erkson

                  Robert, thanks, that does help clarify it.

                  • 6. Re: Tableau not working efficiently with read-only Databases
                    Toby Erkson

                    Shawn, I'm guessing you use Multiple Tables and thus serve them everything OR you use Custom SQL.

                     

                    Is Custom SQL so bad, can it be good?  I would say it depends on how it's constructed.  But I defer all expertise to Robert

                    • 7. Re: Tableau not working efficiently with read-only Databases
                      Shawn Wallwork

                      Toby, I get the either/or on multiple tables-Custom SQL. So my question really boiled down to:

                       

                      1. Is there a way to 'filter' (eliminate) fields that will get included in the multi-table join (without writing Custom SQL)
                      2. Is there a way to add a field filter (eliminator) when making the initial server connection (before getting to the multi-table bit)

                       

                      I'm a server newbie, so there's a good chance I've botched this whole thing up and don't understand how server connections are made.

                       

                      --Shawn

                      • 8. Re: Tableau not working efficiently with read-only Databases
                        Toby Erkson

                        I totally forgot until I looked but you can exclude fields when you click the Add Table... button for Multiple Tables:

                        terkson - Thursday, October 10, 2013 1_41_02 PM - Clipboard.jpg

                        Simply deselect the fields you do not want   Click the Join tab to make your table JOINs and then OK.  Done!


                        • 9. Re: Tableau not working efficiently with read-only Databases
                          Shawn Wallwork

                          Yeah Toby that's what I (we) thought to, but in their case it didn't seem to work that way. That was how we thought/hoped it would work. I'll double-check tomorrow but I'm pretty sure we tried that. If we did and it didn't work and it's suppose to work this way with a live connection, then this sounds like it's a case for support.

                           

                          I'll let you know tomorrow. Thanks for the help with this.

                           

                          --Shawn

                          • 10. Re: Tableau not working efficiently with read-only Databases
                            Toby Erkson

                            Interesting...and very, very disappointing!  Actually, not at all logical from my point of view.

                             

                            I created two extracts of the same data source.  One with everything and the other with almost all fields "unchecked".  The resultant extracts were 99.9% the same.  The 0.01% difference was a different control character or a line-feed in the output.  So no fields were actually removed as I would have expected.

                             

                             

                             

                            Learned something new.  I think I'll shut up now and wait for someone more knowledgeable...


                            • 11. Re: Tableau not working efficiently with read-only Databases
                              Shawn Wallwork

                              Toby, I greatly appreciate all your experimentation. Especially since I don't have access to server to do these kinds of test. I'm also glad to hear I've not completely misunderstood server connections, and that it doesn't seem I am over looking something completely obvious. Thanks for all your help on this. Really.

                               

                              --Shawn

                              • 12. Re: Tableau not working efficiently with read-only Databases
                                Mandeep Singh

                                Thanks for all the answers.

                                 

                                --Mandeep