Thursday, April 24, 2014

Tableau Data Blending with No Common Dimensions

Sometimes when you're working with data, there is a need to compare datasets that don't really have anything in common.  This issue came up with my Sports Viz Show Me The Money: I had a dataset that contained the top 100 earning athletes in 2013 and I wanted to show what their pay could buy for them at an individual item level with out creating an unnecessary Cartesian product..  
Athlete data: a single row per athlete
As Gru would say: Light bulb!  If I create a data set containing a row for each purchasable item repeated the maximum amount of times it would appear (aka the number of times the top earning athlete, in this case Tiger Woods, would be able to purchase it), then I could write a calculated field that blended the two sources and did my filtering for me.

Stuff to buy data: note there is no column mentioning athlete
Doing this gave me two data sets: one that was 100 rows deep and the other just over 5,000 rows deep.

Next, after bringing in both data sets into Tableau, I created a cross dataset calculated field on the 'Stuff to Purchase' dataset that would allow me to filter out when the number of items exceeded the athletes pay.
running_sum(sum([Cost]))<=sum([Forbes 100 Athletes].[Pay $])
This way when I add the purchase able items at a data level, it will continue to referenced the accumulated value of the purchases and restrict them to only total that is less than the salary of the specified athlete.

Finally, I added
- RowID into the details
- 'How Many' true false filter to the filter section
    - IMPORTANT: Change the 'Compute Using' to use RowID
- Add the purchase category and item to the filter section
- Added the object's description to shape
- Filtered it for the specified athlete (from the secondary source).
   - This ultimately ended up being a quick filter that allowed me to select different athletes

Now I had my sheet that showed each of the items that the person could buy.
 If I had joined them ahead of time, I would have had a Cartesian product of over 160,000 rows!  This not only saved me data space, but also cut down on the data prep I needed to get dashboard built.  

So the answer all along (to the question that was never asked) was yes, it will blend...even if there are no common dimensions.  Let me know if you have any other questions and feel free to comment.  Thank you!