One to many relationship using 2 data sources |Tableau Community Forums
The target data has the date of 1st of the month but the actuals are different every actual date to be the 1st so that I could create a relationship. Before you can create relationships between data sources, you must ensure that there is a common field between the data sources you're mapping. The fields. Last Modified Date: 05 Oct If a workbook uses data from more than one data source, you can use data blending or perhaps name and data type match, and it automatically creates a data relationship between them.
This topic lists them and describes how you can respond to each situation.
Join Your Data
Common warnings and errors when blending data sources No relationship to the primary data source When you drag a field from a secondary data source to the view, you might see a warning that says: Fields cannot be used from the [name of secondary data source] data source, because there is no relationship to the primary data source. In the Data pane, switch to the [name of secondary data source] data source, and click at least one link icon to blend these data sources.
This warning occurs when you have no active links in the secondary data source For example, suppose you have two data sources that are related by the State and Date fields. At least one of these fields must have the active link icon next to it in the secondary data source. You can make the link active by clicking the link icon in the Data pane or by using the related field from the primary data source in the view.
The secondary data source may not have any relationships to the primary data source. Look in the Data pane for the link icon. Tableau automatically links fields that have the same name.
If your fields do not have the same name, you must define a relationship between them. For more information, see Step 4: Optional Define or edit relationships. Primary and secondary connections are from tables in the same data source When you drag a field from a secondary data source to the view, you might see a warning that says: The primary and secondary connections are from tables in the same data source.
Instead of linking the connections, use the Data menu to join the data. Joins can integrate data from many tables and may improve performance and filtering. This warning occurs when the workbook contains separate data sources that connect to the same database. Though you can combine data in this way, Tableau recommends that you use a join to combine data from the same database instead. Joins are typically handled by the database, which means that joins leverage some of the database's native performance capabilities.
Non-additive aggregates are aggregate functions that produce results that cannot be aggregated along a dimension. Instead, the values have to be calculated individually. For more information, see Tableau Functions by Category. These limitations cause certain fields in the view to become invalid under certain circumstances.
If you hover your mouse cursor over one of these invalid fields, you see the following error: Cannot blend the secondary data source because one or more fields use an unsupported aggregation. This error can occur for one of the following reasons: Non-additive aggregates from the primary data source: Non-additive aggregates are only supported in the primary data source if the data in the data source comes from a relational database that allows the use of temporary tables.
To work around this issue, consider creating an extract of your data source. Extracts support temporary tables. Non-additive aggregates from the secondary data source: Non-additive aggregates are only supported in the secondary data source if the linking field from the primary data source is included in the view. Some number functions can still be used if they include an additive aggregation. Multi-connection data sources that connect to data using a live connection do not support temporary tables.
Therefore, using a multi-connection data source that connects to data using a live connection prohibits the use of blending functionality with non-additive aggregates. To work around this issue, consider creating an extract of your multi-connection data source.
LOD expressions from the secondary data source: This error can also appear when you use a level of detail expression in a view that uses data blending. To resolve the error, make sure the linking field in the primary data source is in the view before you use an LOD expression from the secondary data source and remove any dimensions, including dimension filters, from the secondary data source. Published data sources as the primary data source: Because certain versions of Tableau Server does not support temporary tables, there are some limitations around non-additive aggregates.
Therefore, using a published data source as your primary data source prohibits the use of the blending functionality with non-additive aggregates.
However, the other limitations listed above still apply. Asterisks show in the sheet When you blend data, make sure that there is only one matching value in the secondary data source for each mark in the primary data source.
If there are multiple matching values, you see an asterisk in the view that results after you blend data. For example, suppose you have two data sources: The primary data source, Population, has a field called State. The secondary data source, Superstore, contains fields called State and Segment.
Data blending limitations Data blending is a method for combining data that supplements a table of data from one data source with columns of data from another data source. Usually you use joins to perform this kind of data combining, but there are times, depending on factors like the type of data and its granularity, when it's better to use data blending. For example, suppose you have transactional data stored in Salesforce and quota data stored in an Excel workbook.
The data you want to combine is stored in different databases, and the granularity of the data captured in each table is different in the two data sources, so data blending is the best way to combine this data.
Data blending is useful under the following conditions: You want to combine data from different databases that are not supported by cross-database joins. Cross-database joins do not support connections to cubes for example, Oracle Essbase or to some extract-only connections for example, Google Analytics. In this case, set up individual data sources for the data you want to analyze, and then use data blending to combine the data sources on a single sheet.
Data is at different levels of detail. Sometimes one data set captures data using greater or lesser granularity than the other data set. For example, suppose you are analyzing transactional data and quota data. Transactional data might capture all transactions. However, quota data might aggregate transactions at the quarter level. Because the transactional values are captured at different levels of detail in each data set, you should use data blending to combine the data.
Use data blending instead of joins under the following conditions: If your tables do not match up with each other correctly after a join, set up data sources for each table, make any necessary customizations that is, rename columns, change column data types, create groups, use calculations, etc.
Joins cause duplicate data. Duplicate data after a join is a symptom of data at different levels of detail. If you notice duplicate data, instead of creating a join, use data blending to blend on a common dimension instead. You have lots of data. Typically joins are recommended for combining data from the same database.
Creating relationships using dates |Tableau Community Forums
However, if you're working with large sets of data, joins can put a strain on the database and significantly affect performance. In this case, data blending might help. Because Tableau handles combining the data after the data is aggregated, there is less data to combine. When there is less data to combine, generally, performance improves. When you blend on a field with a high level of granularity, for example, date instead of year, queries can be slow.