2 Replies Latest reply on Oct 26, 2017 7:28 AM by Brian Shea

    Data Source - Oracle - passing parameters/filters

    Brian Shea

      I have a rather large data source - Oracle tables, several table joins, and I'm looking for ways to speed this up.  Currently, after I setup my data source tables, and do the joins, it will take something like an hour to refresh the data.


      My workbook will require the user to make two selections - one being a person name, and the other being a stock assigned to that person to research.  The data is a time series of several pieces of information, that I can then chart in Tableau - but again, it will always only be viewed in the context of 1 person/assigned stock at a time.


      If I develop a query to pull the same data (using TOAD for example), I would run the query by specifying these inputs - 1 person and 1 stock.  In this case the query will run in < 5 seconds.  Running the query without any inputs, as I believe Tableau is doing, the query takes a very long time to run and would return several million records.


      So my question, is there a way to get Tableau to not query the full set of data, but only query based on these 2 inputs?  I've looked into trying custom SQL as a source, with the parameters in the SQL, and it works, but I end up with the problem where the parameters are not dynamic/updated, and I need this to always have all analyst/securities available.  And ideally I would select an analyst first, then see only the securities assigned to that analyst - which again the parameters don't seem to handle well now.


      One other piece to note, is that I'd like to stay away from an extract if possible.  This data is protected, and I need to run some "Initial SQL" that will check the users permissions, and only return data that that user has permissions to see, which is controlled at our DB level.


      Please let me know if I can provide more info, any suggestions are appreciated!

        • 1. Re: Data Source - Oracle - passing parameters/filters
          Zach Leber

          Tableau will only send queries for what's in your view, so if you add Analyst and Stock as filters it should be fast. Add and show those filters and set the Stock filter to Only Relevant Values so it will be filtered based on the selected Analyst. Also turn off the Show "All" Value option for both filters. See image below and attached TWBX.

          dependent filters.png

          • 2. Re: Data Source - Oracle - passing parameters/filters
            Brian Shea

            Thank you for the response.  I did try this, and it works fine when the workbook is open - I can change selections and the results render quickly, but it is still get a long running query when I try to reopen the workbook.  I get the "Processing request / Executing query" window, that runs for a very long time.  If I cancel out of the executing query, then my filters are blank and I can't make selections.


            I recreated this workbook and connection, but using custom SQL with parameters for the 2 inputs, and it works well.  But this is not ideal, as my inputs are not a static list, and I do want the 2nd selection (stock) to be only the relevant values based on the first selection (person assigned).


            Ultimately, what I would like to do, is only refresh the filter values when the workbook is opened  (I could create a separate query to do this quickly).  Then, once selections are made, retrieve the whole dataset using these selections.  I want to avoid pulling the whole dataset without using inputs, but I'm struggling with how to accomplish this.


            Any other suggestions or resources on details of how Tableau executes SQL would be appreciated.  thanks