3 Replies Latest reply on Apr 15, 2018 7:11 AM by Andrew J

    where clause in data source panel

    Andrew J

      Hi Guys ,

       

      I am trying to get some large volume of data from mssql server . For that i need to use a where clause while pulling the data to tableau . I do not want to use custom query or a calculated field in the worksheet or a tde file.  While joining the tables in the data source panel it will pull all the data to tableau so i want to restrict using a data field . is there anyway to add where clause something similar down here  while joining the tables, I am trying to test out using mssql adventure . I have attached the twbx to get an idea . 

       

      WHERE Start Date BETWEEN 05/31/2011 AND 04/29/2012

        • 1. Re: where clause in data source panel
          Ritesh Bisht

          Hi Andrew,

           

          Why don't you create a VIEW of JOINS outside of TABLEAU and connect to that ?

           

          That will help you to connect to the relevant data only.

           

          . Take advantage of Tableau’s query optimization

          • Blend on low-granularity dimensions. The more members in a blend, the longer it takes. Blending aggregates the data to the level of the relationship. Blends are not meant to replace row-level joins.
          • Minimize joined tables. Lots of joins take lots of time. If you find yourself creating a data connection with many joined tables, it may be faster to materialize the view in the database.


          Read more at https://www.tableau.com/about/blog/2016/1/5-tips-make-your-dashboards-more-performant-48574#LPLkV1QwcG1GJAmC.99

           

          CREATE VIEW V_REGION_SALES
          AS SELECT A1.Region_Name REGION, SUM(A2.Sales) SALES
          FROM Geography A1, Store_Information A2
          WHERE A1.Store_Name = A2.Store_Name and Date A1.BETWEEN 05/31/2011 AND 04/29/2012
          GROUP BY A1.Region_Name;

           

           

           

          Thanks,

          Ritesh

          • 2. Re: where clause in data source panel
            Chris McClellan

            Views are a good solution (completely outside Tableau).

             

            You could create extracts with a filter on the date, or you could create a live connection with a data source filter.

             

            These 3 options will give you the result you're after, obviously just choose the one that works best for you

            • 3. Re: where clause in data source panel
              Andrew J

              Thanks Chris and Ritish .   Definitely customized view is my first option . However we have a DB policy of not creating views .   So that's why I am looking for this option .  

               

              Also , I want to see the sql query fired to the DB server when using tableau data source  . From the tableau log file i can the sql query  . however it will have some characters where i cant just and copy and paste it on my data base . Is there any other place other than log file I can get the sql query . thanks guys