13 Replies Latest reply on Jun 14, 2011 3:43 PM by Oleksiy Nazarenko

    Linking two queries/data sources.

    Surya Pasumarthi

      Hi,

       

      Below is what i am trying to achieve:

       

      1. I have query1 which is joined from multiple tables/views and has large amount of data.

      2. query2 is a kind of reference data, which is a simple query and very fast to execute.

       

      the output/selected value from query2 need to be used a input to the query1, Once filter value is given to query1 it would run very fast.

       

      actually i can join query1 and query2, but in this case whole query would take very long time and would never execute(i tried in teradata, it have to stop the query after 25mins). So i tried query1 as one data source and query2 as another data source and tried blending them and linking value as my filter criteria.

       

      But it is also taking forever, Am i missing something over here. Are there any better ways to achieve this.

       

      Thank you for your time.

        • 1. Re: Linking two queries/data sources.
          Richard Leeke

          There are various ways to tackle this kind of problem.  Which one is best depends on a few things, including how large the dataset supporting query2 is and how static the reference data is.

           

          One way of doing it is to have 2 sheets, one for query1 and one for query2 and define a filter action on the query2 sheet which limits query1 to the selected value(s).  Those sheets might be on the same dashboard, or might just be separate sheets.

           

          I explored a few ways of tackling what was essentially a similar problem in some articles I wrote on Robert Mundigl's Clearly and Simply site, here.  I then adapted one of them in this viz of New Zealand Earthquakes.

           

          In both of those cases, the equivalent of your query1 was finding all locations within a certain radius of a given location which was selected by the equivalent of your query2.

          • 2. Re: Linking two queries/data sources.
            Surya Pasumarthi

            Hi Richard, Thank you very much thats an intersting learning from your experience.

             

            As per your statment, if my lookup(query 2 output) is static i can use a excel or another sheet to filter out the criteria at the data engine.(by including list of values in where clause).

             

            But Query2(lookup) values changes daily.

             

            The output of query1 is so huge approx 1tb. So there is no point in running that without any criteria.

             

            So i believe there should be some method that filter of data should be done at data engine rather than tablue.

             

            All that i want to stop tableau from trying to fetch the data before blending. As per your blog i understand that this a present limitation that need to fixed.

             

            Can we do this,

            1. fetch the data from query1 for any date(my filter criteria) intially.

            2. From then on apply the filter criteria?

            • 3. Re: Linking two queries/data sources.
              Richard Leeke

              Yes, I have taken that approach sometimes when I don't want the query to be executed unconstrained.  You can even put a completely different filter dummy condition which returns zero rows initially until you have applied an action filter, then simply remove the dummy filter.  You might also be able to achieve the same thing by turning off refresh on the sheet for query1 until you have applied your filter action.  I think that works - but I'd have to try it to make sure I'm not missing something.

               

              I'm sure Joe or someone will chip in if there are better ways to achieve what you are trying to do.

               

              I definitely think there is a need for a "lookup filter" to do what you want, though.  Maybe version 7.  Or 8.  ;-)

              • 4. Re: Linking two queries/data sources.
                Surya Pasumarthi

                Yes, I understand that we can do that if we are doing in multiple sheets. My aim is to achieve this on a single sheet.

                Thank you once again Richard.

                • 5. Re: Linking two queries/data sources.
                  Richard Leeke

                  A single view, or is a dashboard OK?

                   

                  I'm fairly sure I've managed to do this on a dashboard in the way we are discussing - by using one or other of those tricks to avoid executing the big query unconstrained.

                   

                  The only way I've managed to do it in a single view is via that trick with embedding a list of values in a parameter that I talked about on Clearly and Simply - but that only works with static data - and doesn't really scale all that well, either.

                  • 6. Re: Linking two queries/data sources.
                    Joe Mako

                    Why does it need to be a single sheet and not a dashboard?

                     

                    Richards suggestion of using a dashboard with a filter action sounds like the best option to me, with the clearing action set to exclude all values, and likely some other settings.

                     

                    The Filter action can work across data sources when you specify the fields to use, and the action filter is sent to the data source filtering at the same time as other normal filters.

                    • 7. Re: Linking two queries/data sources.
                      Surya Pasumarthi

                      I mean a single Worksheet not a Dashbboard. I dont want to implement this in a dashboard because i am not going to publish them my main aim is to create the data connections and share the data connections. So that appropriate people will use the attributes/dimension/measures and make themself the useful graphs in way they want... Still we are process of evaluation of this approach so i am trying myself to deal with these scenarios.

                       

                      This project is very early phase....

                      • 9. Re: Linking two queries/data sources.
                        Surya Pasumarthi

                        Yes, With this approach i would like to filter criteria of query1 with values selected in query2.

                        • 10. Re: Linking two queries/data sources.
                          Richard Leeke

                          Unless I'm missing something, you can't achieve the filtering you are after with data blending, which is what that knowledge-base article covers.

                          • 11. Re: Linking two queries/data sources.
                            Joe Mako

                            Since your aim is to create the data connections and share the data connections, and your main data that changes daily is in Teradata a table about 1tb, are using custom SQL, and your Excel file is small and static, I would recommend talking with your DBA about incorporating this into your Teradata Data Warehouse to make the distribution of the .tds (a Tableau Data Connection) or .tbm (a Tableau bookmark) simple and improve performance.

                            • 12. Re: Linking two queries/data sources.
                              Surya Pasumarthi

                              My reference data and actual data is also in teradata.

                               

                              Infact my query1 has 6 tables t1,t2,t3,t4,t5,t6. - T1 is table which has my date, but i dont want to query this directly because of this join it would take very long time.

                               

                              So i have added another data connect Which would access my only T1 - and fetch the distinct of dates from T1. - call it query 2

                               

                              And moreover there is no static data. Every day one unique new date would be added to the Query2 List. and there is a chance one date would be deleted too.

                               

                              So all that i am trying to achieve is Get dates from query2, select the date and with that date i am trying to fetch query1.

                               

                              But now i understand that may not be possible with out following the Richard's method.

                               

                              Thank you Everyone.

                              • 13. Re: Linking two queries/data sources.
                                Oleksiy Nazarenko

                                I agree with Joe Mako - to get from the database(s) exact data that you need to work with in Tableau - you could use query2 as a sub-query RIGHT JOIN'ed to query1