Data Blending Limitations in Tableau
- Benoite Yver
- January 11, 2020
Occasionally when working in Tableau, you will have to perform a function called data blending, which involves combining data from different sources.
When blending data, you merge data from a secondary data source and display it alongside data from a primary data source in a view (i.e., a visualization).
This is a bit different from data joining, which involves combining related data from common fields.
“Unlike joins, data blending keeps the data sources separate and simply displays their information together,” Tableau explains. “This is ideal when the data is at different levels of granularity. For example, you have transactional data in one source and quota data in another. If we were to join this data, some quota information would be duplicated for each transaction because joins are row-level. Instead, data blending is a better method. Each data source is queried independently and the results are aggregated to the appropriate level then visualized together.”
The Six Principles of Data Blending
- Data blending is best used when you need to analyze data from different data sources: For example, you need to view sales by location – it can be the name of a state or city, and sales data stored in different tables, and to visualize data by city, then you need data blending.
- Investigate your data sources for common data types: both data sources must have a field for linking tables (city or state ID in our example).
- Data blending works only as a left join operation and does not work with any other join types. See this article to familiarize yourself further with the joins in Tableau.
- Data blending is granular independent: Since you get data from different sources, this data can have different levels of detail. For example, in a table with cities, you might have State, City, and ID, and in a table with sales, you might have many more fields.
- Data blending is typically used when your data needs to be cleansed and your tables do not match up correctly after merging.
- Use data blending when you have duplicate rows after combining data.
Tableau Data Blending Limitations: A Closer Look
Data blending limitations often occur when working with “non-additive aggregates” like MEDIAN, RAWSQLAGG, and COUNTD.
“Non-additive aggregates are aggregate functions that produce results that cannot be aggregated along a dimension,” says Tableau. “Instead, the values have to be calculated individually. All Number functions, except for MAX and MIN, are non-additive aggregates.”
Under certain circumstances, these limitations can cause certain fields in the view to become invalid, according to Tableau.
When this happens, you will see the following error message appear over the invalid fields:
“Cannot blend the secondary data source because one or more fields use an unsupported aggregation.”
Causes and Workarounds for a Blending Limitation Errors
1. A Level of Detail (LOD) expression is being taken from the secondary data source
LOD expressions are used when running complex queries with multiple dimensions at the data source level. According to Tableau, this error can also appear when using a LOD expression in a view with data blending.
Remove all dimensions from the secondary data source and confirm that the linking field in the primary data source is in the view. Then, try using the LOD expression from the secondary data source.
2. A published data source is the primary data source
Certain tables in SQL Server are temporary. However, not all versions of Tableau Server support them. This can restrict what you do with non-additive aggregates.
If you are using Tableau 9.0 and later, you can try using MEDIAN and COUNTD with blending in a published primary data source.
If you are using Tableau 8.3 or earlier, Tableau Server won’t support temporary tables. As such, you cannot use blending with non-additive aggregates when using a published data source as your primary data source.
3. There are non-additive aggregates from a multi-connection data source with a live connection
Temporary tables aren’t supported by multi-connection data sources that utilize live connections.
Extracts—or saved subsets of data—support temporary tables. Try using an extract of your multi-connection data source and this could solve the problem.
4. Non-additive aggregates from the primary data source
In order to use non-additive aggregates in the primary data source, the data source must originate from a relational database that allows temporary tables to be used.
Again, try creating an extract of your data source.
5. Non-additive aggregates from the secondary data source
To use non-additive aggregates in the secondary data source, the linking field from the primary data must be included in the view. As Tableau explains, no non-aggregated dimensions from the secondary data source can be included in the view.
Using an additive aggregation will enable some number functions to be used, e.g., using ROUND(SUM([SALES]),1 instead of ROUND([SALES],1).
6. Mixing data reduces the performance of high-granularity queries
To solve such problems, use additional data joining paths. For example, it can be Join data, or if you use SQL as a source, you can process the data using an SQL query. Always consider optimizing scenarios in advance, so you do not encounter a problem at the end of the project.
7. Calculated field does not appear in the Field drop-down list of the Sort dialog box when the calculated field uses data blending
There are several workarounds to solve this problem. Try the following two procedures:
- Manually apply a calculated sort. To do this, you need to go to the sorting properties and try changing the sorting parameters.
- In the secondary data source, create a calculated field. In a calculated field, try writing a measure that combines the data you want to sort. For example, it might be the SUM () function. Then change the calculation type to discrete. Then transfer the created measure to the Rows or Columns shelves. By default, the lines will be sorted in ascending order; you can make the measure negative to change this. To do this, add “-” in front of the measure.
8. Cube data sources (datacubes) can only be used as the primary data source for data blending
None. Datacubes cannot be used as a secondary data source.
9. Cannot publish a data blending source as a single server data source
To solve this, publish the data sources separately on the same server and then merge the published data sources.
10. Tableau times out when working with blended data
When performing calculations in Tableau workbooks, always aggregate data from secondary data sources to make queries more efficient.
Using Tableau Prep
Tableau Prep provides a slightly more flexible and automated way to prepare your data – blend and transform – for analytics in Tableau. But Tableau Prep has major limitations as you can see in our comparison guide of Datameer and Tableau Prep.
Datameer: A Better Way to Transform and Blend
Datameer SaaS Data Transformation is the industry’s first collaborative, multi-persona data transformation platform integrated into Snowflake. The multi-persona UI, with no-code, low-code, and code (SQL) tools, brings together your entire team – data engineers, analytics engineers, analysts, and data scientists – on a single platform to collaboratively transform and model data. Catalog-like data documentation and knowledge sharing facilitate trust in the data and crowd-sourced data governance. Direct integration into Snowflake keeps data secure and lowers costs by leveraging Snowflake’s scalable compute and storage.
Are you looking for a no-code or low-code data transformation solution for Tableau that your data and analytics team can use?
Learn more about our innovative SaaS data transformation solution with direct integration into Snowflake, Sign up for your free trial today!