4 Replies Latest reply on Apr 12, 2011 12:31 PM by Richard Leeke

    Data blending - using filters on aggregated dimensions

    Robin Kennedy

      Hello.

       

      I am blending two data sources together, using a single common field.

       

      The relationship between the two sources is many to many. The results show asterisks * in the secondary datasource dimensions when there are more than one result to display, which is just fine for summing up the number of records or some other measure.

       

      However, when I try and use a filter on the secondary dimension, it incorrectly returns no results, even though the '*' are composed of the items that I am putting into the filter. Is there a way around this? I have noticed that the dimension from the secondary datasource has been converted into an attribute and I cannot change it back... does this have something to do with it?

       

      I have tried to create one datasource, by using a join on the single field instead, but what happens here is I get multiple duplicate rows because of the many to many relationship. Is there a way in Tableau to select distinct results, or group them together or similar?

       

      Thanks!

      Robin.

        • 1. Re: Data blending - using filters on aggregated dimensions
          Robin Kennedy

          Can nobody help with this?

           

          ta.

          • 2. Re: Data blending - using filters on aggregated dimensions
            Richard Leeke

            Can you provide some sample data (made up data is fine) showing how your data is structured and what result you are trying to achieve - it's a bit difficult to give specific advice without understanding your situation a bit better.

             

            But a couple of general comments.

             

            1) Data blending will only return data aggregated at the level of the defined relationship fields.  So in your many to many case - you can only get aggregates at the level of your one common field.  The ATTR() aggregate will return the actual value if there is only one value, otherwise "*".  You won't be able to filter on the underlying elements.

             

            2) Joining a many to many like this in your data connection will give you all permutations, as you observe.

             

            It's likely that you will be able to do what you want with a custom SQL connection.  If you can provide some sample data and what you hope to see I'm sure someone will post an answer.

            • 3. Re: Data blending - using filters on aggregated dimensions
              Robin Kennedy

              Thanks for your response, Richard.

               

              I understand your comments, and the inability of being able to filter the underlying elements of a "*", as you state in comment 1), is indeed my primary problem. It's a real shame that this isn't possible in Tableau! I really would have thought this would have been possible... :(

               

              The only workaround I have managed to come up with is to do a full join using SQL, and then assign percentage proportions to each combination so that when you sum them up you get back to 1, but this is very messy and quickly gets very complicated.

               

              Here is some sample data of what I am trying to blend...

               

              Datasource 1:

               

              Incident    Outage    Duration

              T10001    Server A    2

              T10001    Server B    3

              T10001    Server D    5

              T10002    Server A    12

              T10002    Server C    1

              T10003    Server B    0.5

              T10003    Server D    1

              T10003    Server E    3

              T10004    Server A    7

              T10005    Server A    2

              T10005    Server B    7

               

               

              Datasource 2:

               

              Incident    Cause

              T10001    Human error

              T10001    Code error

              T10002    Power fault

              T10003    Code error

              T10004    Power fault

              T10004    Human error

              T10005    Hardware failure

               

               

              I get a set of results showing the * where there is more than 1 cause per incident, but when I start to use the filters on the causes, it never returns any results for T10001 or T10004. What I would expect to see is when I use the filter on Human Error, it returns the data associated with T10001 and T10004, and when I filter for Power fault I would expect to see data on T10002 and T10004 (all I get is T10002)

               

              Thanks again in advance.

              • 4. Re: Data blending - using filters on aggregated dimensions
                Richard Leeke

                I think a SQL join is the correct way to go.  I don't quite understand your counting problem - I would have thought you should be able to achieve what you want with distinct counts (COUNTD() - unless of course your datasource is text files, the MS JET engine doesn't support COUNTD().  If that is the problem, you could create an extract.

                 

                Or am I still missing the point?