SYMPTOM
After running a cache enabled report with Security Filter assigned users, it is seen that multiple Report Cache files are generated on the Strategy Intelligence Server. This behavior is specific to a case where the Security Filter is defined based off a User Login System Prompt. Any new users assigned to the same Security Filter will not hit the existing cache created by a different user and the job runs against the warehouse. This will in turn generate a new Report Cache for that user which can then be accessed later by the user without the need for the full execution. The report cache interaction with 'User Login' System Prompt is seen even when the result set does not differ between two separate users with the User Login System Prompt.
After enabling the Report Server Cache Trace and the Report Server Security Filter Trace the following statements will be seen in the traces when a second user (with the same security filter) executes the report:
[Report Server][Cache Trace] Failed to find a matching cache since prompt answer is not matching. [Report Server][Security Filter Trace] User:User, Test is running report using security filter with name=System Prompt Security Filter and ID=****
CAUSE
Strategy Secure Enterprise works to provide information in the fastest way possible, but also to maintain complete information security. To do this the engine resolves the second instance of the report execution by first comparing the report that is run and finding any matches. If a match is found it will then determine if a Prompt or Security Filter exists for the report, and if they do it will compare and ensure those match perfectly. In a final step, the engine will compare the Report resolution which contains any System Prompts and their answers. For the 'User Login' prompt it will find that within the Resolution, there exists a System Prompt and that System Prompt's answers. Since the System Prompt has a different answer coming from the different users who have logged in, it will then determine that the new user does not have access to the cache for security reasons and go through the execution process for this user.
The reason that Strategy operates in this manner is twofold in nature; Security and Performance. From the Security stand point the system will perform every step necessary to ensure that no information is improperly sent to any recipient. This does include the integration with other environments and the Data Warehouse being referenced. When a report execution request comes in, the environment does not know the result set until execution has been completed, so will use a series of defined points in order to determine how to proceed correctly. This was found as the most secure method for integration into additional systems where the Strategy environment is not aware of third-party user based security definitions.
From the performance side it is possible to do a SQL comparison between the two executions, however this was chosen against as the performance impact would be too great for the average user. All of the object comparisons take place before the SQL is generated as they are part of the determination in how to correctly generate the needed SQL. If a SQL comparison was utilized for this procedure any user who does not have the User Login system prompt would add the time it takes to generate SQL and then do a series of lengthy string comparisons to their cache execution time. This would end in a very large performance loss across the entire caching procedure of the Strategy environment.
ACTION
In order to take advantage of report caching, the specific user will need create a distinct cache update subscription to have the cache prepared in advance in the case of long running documents, or utilize a different Security Filter or ACL design to allow for group level cache access.
An Intelligent Cube structure could also be utilized instead of caching to allow for access to User Login System Prompt data in a faster method. However, there is a known issue with the usage of System Prompts for Intelligent Cube publication as discussed in the following Knowledge Base article:
KB438734: In MicroStrategy Secure Enterprise 9.4.x - 10.x, duplicate Intelligent Cubes are published when using a User Login System Prompt
