The behavior you are descibing is expected behavior due to a one-to-many, with the many in your secondary data source. Unlike a join, where you would have what you describe as expected outcome, with data blending you have some limitations, e.g. you can only work with aggregates from the secondary datasource, and slice and filter by the dimensions in the primary datasource. Thus what happens in your case is, you see the available dimension from the secondary datasource, where you have a single option, and an asterisk, meaning you have multiple matches, wherever you have the one-to-many. You can have a look at the following KB for further information and instructions how to tackle this type of problem: http://kb.tableausoftware.com/articles/knowledgebase/how-create-primary-groups-secondary-source
Hope this helps a little!
Thanks, this answers my question. Unfortunately, my data exists on separate servers.
Is there any way to JOIN data on different servers directly in Tableau? Or am I going to have to go do this work on one of the SQL servers?
The JOIN assumes data exists in a single data source, and server respectively, but comes from different tables that are related by certain field(s). So unfortunately you will have to do some heavy lifting upfront in the SQL, at least to my level of understanding. The other option of data blending might be useful, but you have to plan your slicers/filters accordingly. You migh resort to a third option, where you genrate some cross-products of dimensions fom both data sources, and use that as a starting point for your analysis, by further using dat blending.
Hope this helps,
@Christian - data blending is similar to a left join, but not exactly. What Tableau does is generate the query for the primary, then generate a query for the secondary, and then inside Tableau merge the results as a form of left join. The level of detail used for the merge is based on all the dimensions used in the view (Columns and Rows Shelves, and the shelves on the Marks Card), all other fields (dimensions and measures) from the secondary are aggregated. So, as Vlad wrote, if you are requesting a dimension that is at a finer level of detail than is in the level of detail in the view, Tableau will have to aggregate the dimension and it returns a * in that case.
@Vlad - beyond the solution you provided, there's an additional solution to use table calculations, and a third option via custom SQL and/or using ODBC or other connectivity to connect the data sources. Here's a thread from June on this: http://community.tableau.com/thread/118319
I'm always amazed by the variety of different ways Tableau offers to tackle a single subject. A big "thank you" goes for all of you guys, who share that knowledge with the community!
You're welcome! An awful lot of what I know I learned from other users, and I'm happy to share what I know and continue to learn myself.
I have the same issue.
I am doing data blending between multiple tables though they are from the same source to avoid the 1:N join issue.
I have one table Table A which is master table and Table B which is more of reference table.
I have an account number which I am taking as a key between them.
And there is a filter on account number.which is actually coming from primary datasource and this filter is global and applicable to the sheet I created from secondary datasource.
But in my secondary datasource for each account number there are different product group and for each product group there are different subproduct group.
Since the aggregation is happening during datablending product group and sub product groups are showing ' * '.
I saw the article on creating primary group.But how this can be applied on my case.
Is there any other solution to get the data instead of "*".
Many thanks in advance.
Be aware that "global" filters are global at th data connection level - i.e. for all views that are based on the same data source. Global filters do not apply across multiple data sources, due to the fact that the filter shelf is not considered part of the view. Hence you have filtered information in your primary connection, but not in the secondary from which you are trying to blend data in.
You could try adding the field you are filtering on the lvel of detail and make sure there is a linked (related) field in the secondary datasource, to see if it works for you.
Another option is to use paramters instead of quick filters, because parameters are visible across multiple data sources.
Yet a third option is to use actions in a dashboard setup: you may have a sheet that is actually used as a quick filter, and you set up a filter action from it to the two datasources in two separate views.
Last but not least with custom SQL you may be able to "pre-aggregate" the "many" part of your relationship and basically convert it to one-to-one, so that you are able to do everything with one data source only.
Hope this helps a little, or please post a sample of what you are trying to achieve, so that we can dig deeper!
2 of 2 people found this helpful
@Vlad - I have a slightly different take on how filters work with blended data. As you wrote, the filter works on the primary data source and not the secondary. However, since Tableau only "joins" the secondary table in after the query, effectively both tables get filtered to the combination of a) only what data is returned in the primary and b) only what matches based on the linking fields.
@Philip - I think what's going on here is that you currently have a finer level of detail in the secondary than the primary, that's indicated by the *. If you can add additional linking fields to the view from the primary as Vlad suggested, then you can avoid the *, you'll just need to be careful about your aggregations.
If that level of detail doesn't exist in the primary, then you might consider using Custom SQL, or switching the primary to be the secondary and vice versa. Then you would do set your global filter on the secondary.