11 Replies Latest reply on Jun 20, 2011 5:16 PM by Tom Walter

    Multiple aliases for dimensions

    Tom Walter

      Hi,

       

      I have a table that holds multiple aliases for one of my dimensions. I'd like to be able to use it as a quick filter / lookup where someone can type in or search for one of the aliases and have it mapped to the correct dimension item.

       

      I can't use a join, as mentioned, there are multiple aliases per each dimension, so a join would cause duplication of my measures. Also, the join would be extremely costly for performance.

       

      I looked into using parameters and custom SQL to look up the alias and map it to the correct dimension ID. This works, however parameters seem to only allow one entry at a time for text entry fields, and I'd like this to allow multiple entries, as per a quick filter 'Custom Entry' field.

       

      Cheers

      Tom

        • 1. Re: Multiple aliases for dimensions
          Richard Leeke

          Yes, I often want to do this too.  I think it really needs to be a posting in the "wouldn't it be nice if" forum.  I've probably posted something similar at some stage.

           

          The nearest I have been able to get is either to use a parameter (you can define multiple display values for the same data value), but as you say, that only supports single select.  The other option is a dashboard with a quick filter on your alias table - but then you need to have a filter action on that sheet and multi-select the filtered results.  Really you just want to pick from the filter on the alias values and have that flow straight through to your target sheet.

           

          Maybe Joe (or someone) can think of another way?

          • 2. Re: Multiple aliases for dimensions
            Joe Mako

            I agree it would be nice if there was a way for a parameter to hold multiple values. My current work around is to duplicate the data. So If I wanted the effect of a multi select parameter with 5 values, I would duplicate the data 5 times, and then I can use a quick filter. This works well when the data source is the Tableau Data Engine, but not so well with custom SQL and a large live data source.

             

            Here is an example where I duplicated the data with a cross join to enable a filter action to have the effect of a parameter:

            Re: Status Bar

            • 3. Re: Multiple aliases for dimensions
              Richard Leeke

              I'm not quite sure I understand how you are suggesting this would work with Tom's alias requirement, Joe.

               

              Are you suggesting that you replicate each row for each alias for the dimension value in the row?  I can see that allowing selection by alias - but I think it could get quite complex in terms of avoiding duplicates in the case where someone selects several of the aliases for the same value.  Or did I miss your point.

               

              Just to be explicit, here's a trivial example of what I understand Tom to be asking for and what I understand Joe to be suggesting.  Of course I may be wrong on either or both counts.

               

              Imagine we have a size dimension which can take the values :small, medium and large.  We might have the following aliases defined:

               

              small: tiny, petite, bite-sized, incy-wincy

               

              medium: average, normal

               

              large: giant, huge, enormous

               

              Given this data set:

               

              ID,size,value

              1,large,10

              2,small,250

              3,small, 7

              4,medium,2

              5,large,17

               

              I understand that Tom wants to be able to display a multi-select filter for size with these values:

               

              small

              tiny

              petite

              bite-sized

              incy-wincy

              medium

              average

              normal

              large

              giant

              huge

              enormous

               

              I would expect that if the user selected 'giant' and 'huge', they would get just row IDs 1 and 5.

               

              What I think I understand you to be suggesting Joe is to replicate the rows like this:

               

              ID,size,value

              1,large,10

              1,giant,10

              1,huge,10

              1,enormous,10

              2,small,250

              2,tiny,250

              2,petite,250

              2,bite-sized,250

              2,incy-wincy,250

              3,small, 7

              3,tiny, 7

              3,petite, 7

              3,bite-sized, 7

              3,incy-wincy, 7

              4,medium,2

              4,average,2

              4,normal,2

              5,large,17

              5,giant,17

              5,huge,17

              5,enormous,17

               

              If that is what you're suggesting it looks quite tricky to work with the results to me.  Of course I may be missing something.

              • 4. Re: Multiple aliases for dimensions
                Joe Mako

                Yes, that is what I am thinking, combined with table calculations. The question then becomes what is desired for a final result.

                 

                Attached is an example using this size data, and one possible result, the sum of the values.

                • 5. Re: Multiple aliases for dimensions
                  Richard Leeke

                  OK.  So it's really just the approach Tom mentioned but dismissed (joining to the alias lookup table) - and using table calcs to get around the resulting duplicates.

                   

                  I definitely works - but makes a good argument for having a built-in lookup option, I think.

                  • 6. Re: Multiple aliases for dimensions
                    Tom Walter

                    Thanks guys. Yeah as Richard mentioned, this is what I was alluding to when I mentioned using a join. I didn't realise it was possible to make the aggregate calculations work despite the duplication, so that is useful, thanks Joe! However the real problem is simply that the join required is very large and costly. We are using Greenplum on a 30TB-odd DB, and joins require communication between nodes and are therefore pretty much the most expensive operation we can do!

                     

                    I might try your idea of having a quick filter in a dashboard Richard. I assume you are talking about data blending or something? I haven't had much luck getting data blending to work so far, I am not sure why... but I'll have a crack at it.

                    • 7. Re: Multiple aliases for dimensions
                      Tom Walter

                      The quick filter / data blending idea doesn't seem to work. For some reason Tableau runs a query on the secondary source basically trying to fetch every possible value in it, rather than limiting it to only the dimensions that should be returned based on the blended quick filter. Richard, do you have any examples of how you have made this work?

                       

                      I can never seem to get data blending to work the way I want it to.

                      • 8. Re: Multiple aliases for dimensions
                        Tom Walter

                        Joe, I am trying out your suggestion, the join isn't too bad as long as the filter is actually in place.

                         

                        But the calculations you have used aren't quite what I am after. Is there a way to only have the 'size' dimension in the view and have the values aggregated only on the distinct values for that dimension?

                        • 9. Re: Multiple aliases for dimensions
                          Joe Mako

                          How about the attached? From the Total Value sheet, I just added the "size" field to the Rows shelf.

                           

                          There are other options as well, other methods to get this result that may be better suited depending on what you want for a final display.

                          • 10. Re: Multiple aliases for dimensions
                            Richard Leeke

                            I took Joe's original workbook with my sample data and added a few sheets demonstrating the pros and cons of the various things I talked about in this thread.

                             

                            There are two dashboards showing how you can use a view of your alias table as a filter into you main table.  In one case I set it up to be a "select filter action", so as soon as you select the row or rows in the aliases that you want it queries your main table.  This fires the filter actions as you click - which could result in repeated re-querying of your massive table with progressively more values in the IN list in the WHERE clause.  In the other case I made it a menu filter action - so you can progressively select the ones you want and then right-click to select the filter.  In that example I also included a quick filter on the alias table.  that is completely superfluous with this many aliases, but might (?) just be useful if you had a lot of members in your alias table.

                             

                            I've included one sheet which uses data blending - which really demonstrates why blending isn't any good for your needs here.  You need to make the primary table the list of aliases and then blend in aggregates from the main data table.  Note that you get repeated data if you select multiple aliases for the same size.  Also (not demonstrated here) you wouldn't be able to see any other dimensions not present in your alias table - you can only aggregate at the level of the join fields.  The blend has to be this way round in order to be able to filter on the aliases, though.

                             

                            Lastly I included a sheet showing what you can do with a parameter.  This only allows single select, but does support the aliases by embedding them in the parameter list of valid values (i.e. it doesn't refer to the alias table at all).

                             

                             

                            Given your comment about join cost on your Greenplum database, I think maybe the dashboard filter action approach may be the best.

                            • 11. Re: Multiple aliases for dimensions
                              Tom Walter

                              Wow, that is excellent thanks Richard. I am sure that one of these options will do the trick, I will try them out.

                               

                              It really seems like Tableau needs a better way for doing some of these things though. I guess that is a post to the 'wouldn't it be nice if' forum. :-)