KB483226: Intelligence Server may fail when run by a user with assigned security filters.

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

Symptoms

• Intelligence Server crashes when running a dossier or never finishes loading
  
• User who ran the dossier has a Security Filter applied
• Dossier has a Visualization with Primary Dataset set to "None"

Glossary

• Security Filter - an object that you assign to users or groups, which limits the result set when users execute reports or browse elements. Security filters enable you to control what warehouse data users can see, at the Strategy level
• Primary Dataset - Determines which attribute elements appear in the visualization; if the visualization has no primary dataset, all attribute elements appear in the visualization, regardless of the dataset to which they belong.

Steps to Reproduce

We've set up an example using Strategy Tutorial objects:

• Open Strategy Developer, and log in as a user with administrative privileges (e.g. Administrator).
• Create a Security Filter on any user using the following definition (detailed instructions here): 

• Open Strategy Web. Log in as a user that does NOT have the Security Filter applied.
• Create a new dataset with the following tables and relationships:

• Create a second dataset with the following tables and relationships: 

• Create a new dossier using the cube you just made:
  
• Drag Quarter Id and Prev Month Id to a Grid Visualization.
• Change the Primary Dataset of the Visualization to "None".
  
• Right-click the three dots in the top-right corner of the visualization > Data Source > None.
• Save the dossier, and log out.
• Log in as the user with the Security Filter applied.
• Run the dossier you just created.
• See how the dossier never loads or eventually crashes.

Cause

This is a known defect in Strategy 10.11 and earlier.

• The join path is determined by the defined relationships between the attributes. As there is no primary dataset chosen, the visualization will use attributes from every dataset in the visualization.
  
• In some cases, such as the example, a Security Filter can complicate the join process and generate a large intermediate table whose size is too large for the Intelligence Server to process correctly.
• This causes the Intelligence Server to slow down and sometimes, crash completely.

Solution

• Upgrade to Strategy 11.0 or above. We recommend the latest version.
• Set Data Engine Version setting to 11 or above.
  
• 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, Data Engine has rewritten the process for joining tables when applying Security Filters. This prevents the creation of large intermediate tables that slow down SQL execution.
 
Strategy Internal Reference number for this technical note is KB483226 and DE101087.