KB483211: Dossier using a Live Connection ignores Cartesian Join Settings if the database instance is not set as the primary in Project Configuration.

Starting with the release of Strategy ONE (March 2024), dossiers are also known as dashboards.

Symptoms

• Dossier is using a Connect Live data source which is NOT the primary source for this specific project
• Cartesian Join Warning set to "Cancel Execution" for that Database instance
• Cartesian Join is not stopped (query executes when it should not)

Glossary

• Database instance - configuration object that represents a connection to a data source
• Cartesian Join Warning - VLDB setting that sets the desired behavior for handling Cartesian joins
• Live Connect

Steps to Reproduce

• Open Strategy Developer.
• Set your Database instance to non-primary (pick another database instance to set as primary) for your project. We use the Strategy Tutorial project.
  
• Right-click the project and select ""Project Configuration".
• Change the Primary Database instance to a DIFFERENT Database instance.

• Open the VLDB Properties of your Database instance.
  
• Navigate to "Database Instances" under Administration > Configuration Managers in Strategy Developer.
• Right-click your Database instance, and open "VLDB Properties".
• Change Cartesian Join Warning from default to "Cancel Execution".

• Restart your Intelligence Server for the changes to apply.
• Create a new dossier.
  
• Add new data.
  
• Select tables: LU_YEAR, YR_CATEGORY_SLS.
• Click "Finish", and select "Connect Live".
• Add Category Id  to the "Rows" of a Grid Visualization.
• Add Tot Dollar Sales to the "Columns".
• Create a new metric: Percent to Total by Grand Total of Tot Dollar Sales.
  
• Right-click Tot Dollar Sales, and select "Create Shortcut Metric".

The grid should not return data as the calculation for Percent To Total involves a Cartesian join. However, the actual result does not match the expected result.
Expected Result:

Actual Result:

Cause

• Cartesian Join Warning setting is ignored when the Database instance used is a Connect Live data source and is NOT the primary source for the specific project

This is not an issue for data coming from a Database instance that has been set as primary.

Solution

An easy work-around is to set the primary database instance to the one you are using. However, a project may use multiple database instances at once, so when this is not a feasible solution, you can:

• Upgrade to Strategy 11.0 or above. We recommend the latest version.
• Set Data Engine Version setting to 11.
  
• The project level setting Data Engine Version can only be enabled through Workstation. For more details on enabling the setting, see Change Data Engine Version.

In the new fix, the Intelligence Server honors the Cartesian Join Warning setting from the current Database instance, regardless of if it is the primary one or not.
NOTE: This fix is not applicable to Datamart reports that use the multi-source feature.
 Strategy Internal Reference number for this technical note is KB483211 and DE94709.