Managing Joins Between Multiple Data Sources in Spotfire 14 LTS and Above

Keywords:
Spotfire 14 LTS, Information Link, SQL join, temporary tables, data source configuration, Revvity Signals Support

Introduction: This article addresses an issue in Spotfire 14 LTS where joining tables from different data sources may result in an additional, unexpected data source being added to the SQL query, causing failures in Information Links (ILs).

Problem: When creating an Information Link (IL) that joins tables from multiple data sources, Spotfire 14 LTS may automatically add an extra data source to the SQL query. This can cause errors when opening or executing the IL.

Symptoms:

• Unexpected database references in SQL queries.
• IL execution failures with errors like "Invalid object name" or "Database not found".
• Performance degradation during join operations.

Cause: This behavior is due to a design change in Spotfire 14 LTS. When joining tables from different data sources, Spotfire requires a database to create temporary tables for the join operation. The ability to use the Spotfire database as the default join database was deprecated in version 11.3 and removed in 11.7.

Solution:

1. Option 1: Enable "Allow writing in temporary tables" on an existing data source

   • Open the data source configuration for one of the data sources used in the join.
   • Navigate to Advanced Settings.
   • Enable the "Allow writing in temporary tables" option.
   • Permissions: Ensure the database user account has:
   • CREATE TABLE permissions.
   • WRITE access to the target schema.

2. Option 2: Create a new dedicated data source for temporary tables

   • Create a new data source in Spotfire (e.g., Join_TempDB).
   • Enable "Allow writing in temporary tables" in its configuration.
   • Set this data source as the default join database:
   • Go to Tools Administration Manager Database.
   • Select the new data source under Default Join Database.

Best Practices:

• Testing: Always validate changes in a non-production environment first.
• Performance: Ensure the temporary database has sufficient resources (CPU, memory, storage) to handle large joins.
• Cleanup: Schedule periodic cleanup of temporary tables to avoid storage bloat.

Limitations:

• Not all databases support temporary table writes (e.g., read-only data sources).
• Joins involving cloud databases (e.g., Snowflake, Redshift) may require additional configuration.

Documentation:

• Spotfire Documentation: Configure Default Join Database
• Revvity Signals Support

Next Steps: If issues persist after implementing these solutions, contact Revvity Signals Support.