3 Replies Latest reply on Jul 1, 2015 11:44 AM by Jonathan Drummey

    Filtering Large Data Source

    Mory Padilla

      I am looking to create a report on a fairly big data source (about 10 million rows).  Originally my plan was to have a parameter in the where clause in a custom sql to decrease the amount of data I'm pulling.  However, the parameter would have to show new values when new entries are made to the data source.  For example, if I am filtering on usernames and more usernames are added to the database, the parameter would need to show those new usernames.

       

      Because parameters don't update dynamically, I don't see this method working.  Is there a viable way for me to use this data source?

        • 1. Re: Filtering Large Data Source
          Jonathan Drummey

          A couple of questions:

           

          1) Are you using Custom SQL for anything else besides that filtering? If so, then another technique would likely have higher performance (such as a data source filter, a materialized view or stored procedure, etc.).

          2) What are the criteria for determining new entries?

           

          Jonathan

          • 2. Re: Filtering Large Data Source
            Mory Padilla

            At the moment I am only using the Custom SQL for filtering.  I didn't use a data source filter because I wanted the user to be able to select what data they wanted.

            I'm a little confused about what you mean by "criteria for determining new entries". 

            • 3. Re: Filtering Large Data Source
              Jonathan Drummey

              FYI, you can use a calculated record-level field as a data source filter and have that field be dependent on a parameter. That enables you to get the same effect as the Custom SQL without the overhead of Custom SQL.

               

              However, that doesn't solve the original problem of having the list of items in the parameter dynamically update as the data changes. Here are some basic ideas that get you a dynamically updated list:

               

              1) Use a filter worksheet that displays a list of usernames, then a Filter Action that targets your destination worksheets.

               

              2) Use a quick filter on the username and make it a context filter.

               

              3) Use the filter worksheet idea and then make the filter from the Filter Action a context filter.

               

              Also check out http://community.tableau.com/ideas/1178, there are various child ideas. The type of situation you are talking about is in one sense a kind of cross-data source filtering.

               

              Jonathan