11 Replies Latest reply on Apr 22, 2014 10:27 AM by John Sobczak

    Parameters in Custom SQL

    Mak Date

      I am using parameters in Custom SQL to make my query dynamic (to allow users to pull their own data).

      A simple query on our Teradata warehouse like

      "select * from transactions where customerid = <Parameters.id>" is taking forever, but it runs in under a minute using Teradata SQL Assistant (or if I hardcode the customerid)

      A similar dynamic query using Tableau against Oracle did not return data even after an hour!! (and returns results under 5 minutes from a SQL client)

      Is this normal or am I doing something wrong?

      Thanks in advance!

       

      Mak

        • 1. Re: Parameters in Custom SQL
          Dana Withers

          I've no experience with Teradata, but I believe that if you use custom SQL, Tableau cannot optimise the query.

          Is there no way you can use a normal filter on an extract or something like that?

          • 2. Re: Parameters in Custom SQL
            Mak Date

            Hi Dana - Thanks for your reply.

            Here's the Use Case I am trying to address

            • We get a lot of requests for ad-hoc queries against our data (Teradata/Oracle/DB2 etc). The query criteria is usually a combination of Customer ID/Account/Effective Date etc
            • We use SQL clients (Teradata SQL assistant/Access) to extract the data. Most queries take 10 minutes at the most (since the criteria usually hit the indexes)
            • I was trying to check if we can move to a "Self Service" approach, by allowing the Users to enter their search criteria as a paramter (Example - Customer ID) and then asking them to export the "Underlying Data"
            • I think, because of the size of the data normal filters/extracts will not work because there are thousands of Customer Ids/Accounts etc (Please correct me if I am wrong)
            • I found it strange that the query runs fast when I have it as a static query(In a SQL client or even in Tableau) and runs VERY SLOW when I use it as a parameter.

            Any solutions/comments are welcome!

             

            Thanks

            Mak

            • 3. Re: Parameters in Custom SQL
              Joshua Milligan

              Mak,

               

              Have you checked the Tableau logs to see exactly what query is being run?  The custom SQL query itself may run quickly, but it is likely being wrapped inside another query that is based on the fields and their placement in your view and that can have a dramatic impact on performance. 

               

              Regards,

              Joshua

              • 4. Re: Parameters in Custom SQL
                Mak Date


                Hi Joshua - Thanks for your suggestion! I checked the Tableau logs. The one thing, I found different was I see a

                account_number = N'11222' (I enter 11222 as the Account Number, but the log shows a N before it. The Tableau query runs fast on a SQL client if I remove the 'N', but does not complete(within a few minutes). Do you think that is the problem? (I thought I was because I was pasting a null, but I get the same thing even when I manually enter a different account number.!

                 

                ----------

                 

                SELECT "TableauSQL"."ACCOUNT_NUMBER" AS "ACCOUNT_NUMBER",

                   1 AS "Number of Records"

                FROM (

                   select account_number from mytable where

                   platform = 'ABC'

                   and

                   account_number = N'11222'

                ) "TableauSQL"

                group by "TableauSQL"."ACCOUNT_NUMBER"

                 

                Thanks!

                Mak

                • 5. Re: Parameters in Custom SQL
                  Joshua Milligan

                  Mak,

                   

                  A couple of questions:

                  1. What data type is ACCOUNT_NUMBER in the source?
                  2. What data type is your parameter?

                   

                  Are all of the account numbers integers (even if the field itself is a string)?  If so, see if changing the parameter type to integer changes the resulting query.  (Especially do so if the ACCOUNT_NUMBER field is an integer)

                   

                  Regards,

                  Joshua

                  1 of 1 people found this helpful
                  • 6. Re: Parameters in Custom SQL
                    Mak Date

                    Hi Joshua - Changing the parameter type to integer did not work. However, your comment got me thinking and I tried changing my query to use

                    CAST (<Parameters.acct> AS VARCHAR(100))

                    (i.e. explicitly cast the parameter as a string)

                    The query runs very fast after I did that !!

                     

                    Thanks again for your help!

                    Mak

                    • 7. Re: Parameters in Custom SQL
                      Joshua Milligan

                      You're welcome!

                      • 8. Re: Parameters in Custom SQL
                        Robert Morton

                        Hi Mak,

                         

                        For the use case you have described, you should probably be using a direct single-table connection along with Data Source Filters. You can create a calculated field that references a Parameter, and make that calculation part of your data source filters. From the Data menu choose Edit Data Source Filters... to add columns or calculated fields.

                         

                        The reason this will improve performance is that Tableau will no longer be forced to wrap your Custom SQL in a subquery, which is often expensive for databases to evaluate, and instead your queries will be much simpler.

                         

                        I hope this helps,

                        Robert

                        • 9. Re: Parameters in Custom SQL
                          John Sobczak

                          Hi Robert,

                           

                          I know this is several months old, but I like this idea of passing the parameter inside the data source filter rather than a custom sql.  However when I tried this and then exported to a packaged workbook (.twbx),  it contains all the data as can be accessed by changing the parameter inside the twbx.  I was hoping to EXCLUDE all the other data in the twbx file for security purposes.

                           

                          I did notice though when I change the data source to use as extract AFTER I add the data source filter, it does exclude all the other data when I export to packaged workbook.

                           

                          So it seems to control only exporting the filtered data into a packaged workbook, the data source filter must be applied to live connection data source and then that data source changed to an extracted data source.  Is that correct?

                           

                          What I'm trying to accomplish is to use one data source and one dashboard template, but yet create packaged workbooks to send to each supplier with only their data in the packaged workbook as they will not have access to our Tableau Server.

                           

                          Using above steps, I would have to toggle my data source from extract to live, change the data source filter by way of parameter, then extract the data source again and then export packaged workbook.  4 steps for each supplier dashboard for each data refresh.  With hundreds of suppliers this becomes a labor intensive task. 

                           

                          Of course I could create a dedicated data source for each supplier which will necessitate having hundreds of seperate data sources.

                          • 10. Re: Parameters in Custom SQL
                            Robert Morton

                            Hi John,

                            Given your needs, you should be using Extract Filters only, not data source filters.

                            -Robert

                            • 11. Re: Parameters in Custom SQL
                              John Sobczak

                              Thanks Robert!  I always learn something from you.   In this case I don't think I ever realized there was a difference between extract filters and data source filters. I spent a little time playing around with combinations of both interchangeably using direct data connections, then changing to extracts and then back to direct to reinforce the differences in my mind.

                               

                              I still need to figure out how to automate data source extracts for hundreds of parameter values to avoid creating hundreds of dedicated data sources.