3 Replies Latest reply on Dec 28, 2012 8:07 AM by Catherine Rivier

    Filtering across multiple data sources - what is the best solution?

    Ian Pytlarz

      Hi all,

       

      I'm suffering from a bit of a conundrum. I need to get a whole bunch of data from many different sources onto the same dashboard, and filter across all of them simultaneously. I have a few 'sections' but to keep it simple I'll focus on the undergraduate part of what I need to do. Here is the data I have, along with fields shared by the datasets and unique fields that I will be using in my dashboard:

       

      Applications

                Description: All students that had an application

                Common Fields: ID, College, Major, Academic Period

                Unique Fields: Decision (admitted, etc)

      New Beginners

                Description: All students that are newly enrolled in a given academic period

                Common Fields: ID, College, Major, Academic Period

                Unique Fields: HS GPA, Standardized Test Scores

      Enrollment

                Description: All enrolled students

                Common Fields: ID, College, Major, Academic Period

                Unique Fields: Demographic Fields

      Degrees

                Description: All degrees earned

                Common Fields: ID, College, Major, Academic Period

                Unique Fields: Award Category

       

      And lastly:

       

      Mapping

                Description: A file to map college/major combinations to the relevant HR department codes

                Common Fields: College, Major

                Unique Fields: HR Department

       

      There are various reasons why these are separate. For instance, someone could be admitted to one college but enroll in another upon arrival. A degree may also be granted to someone who's application is too far back to be grabbed by my pull. Etcetera.

       

      What I need to do is be able to filter all application, enrollment, and degree data on the same dashboard by College, Major, and HR Department. What is the most efficient way to do this? If I try to put these data sets in separate excel tabs, connect in tableau, and add tables to the mapping connecting College and Major, it just keeps going into the millions of millions of rows and never finishes. I'm not sure what to do, as I believe this data suits itself to being in joined tables and that is the way to do what I need to do.

        • 1. Re: Filtering across multiple data sources - what is the best solution?
          Ian Pytlarz

          I think I've figured this out. I believe I need to add a flag for each data set (so Admissions_Count would be 1 in all admissions records, 0 otherwise) then merge all the datasets on all of their common criteria to get a flat file that can be a single datasource for tableau. I'll work on this...

          • 2. Re: Filtering across multiple data sources - what is the best solution?
            Robert Royer

            The millions of rows are probably coming from improper joins. You should be able to join all of the tables together using ID. Just make sure that you use the appropriate join (left or center).

            • 3. Re: Filtering across multiple data sources - what is the best solution?
              Catherine Rivier

              If you can link your data sources before bringing into Tableau, of course that would be great.  As said above, that can be more of a data issue of how your links work.

               

              However, I definitely understand if all of your data needs to remain separate and won't join as you need.  I've created a lot of dashboards with multiple separate data connections and filters that work on all of them.  Here are some of the things I've learned in setting these up:

               

              If you want to join data sources on a single field, best advice is to make sure they all have exactly the same (linking) Dimension field name, and the values in those fields are exactly the same in each.  It will make everything much easier.

               

              For filtering, actions work well (and it's good to have a few actions set up to highlight the same department in each view, for example).  But I did a lot of speed tests, and parameters are by far the best way to apply filters to multiple sheets in an entire dashboard.

               

              The ideal scenario for parameters is if you have a relatively fixed list of values in the filter - for example, if you had a total of 20 Majors to list that wasn't likely to change much.  If your field has hundreds of values, and is rapidly changing, you likely have to consider other options (probably Actions, or different filters, in your situation).

               

              The other current drawback with parameters is they don't allow for multiple selections without predefining all of these combinations.  I say current, because it is an idea you can vote on http://community.tableau.com/ideas/1313

               

              But, if you want to select the same College and/or Major in all of your views no matter the data source, I put up an example, with a sample of how to do it here:

              http://community.tableau.com/thread/121495

              For every field that you want to filter on, make a parameter - here it seems like College, Major, Academic Period (maybe ID too, depending on whether you want to filter).  Show the single parameter control for each on the page.  As long as it's set up right, this is a great way to control multiple data sources, and apply the "partially-universal filters" that so many have been asking for!

               

              Please let me know if this doesn't work out for you, I'm happy to help more if needed!

              Catherine