KB8831: Filters do not get updated after making changes to the lookup tables

SYMPTOM:
When executing a report after a warehouse data load, users may see incorrect data, however, the Structured Query Language (SQL) appears to be correct.
 
If the warehouse load algorithm modifies the IDs or descriptions of attributes elements then the report results or prompt values may be incorrect for subsequent executions of reports even though the Structured Query Language (SQL) appears to be correct. This is a result of the way that Strategy is designed to store certain types of prompt selections.
 
Consider the following examples:
Example 1
Two columns are associated with the Category attribute:
 

• category_ID - which is the primary key and is also a numeric field.
• category_desc - which is the description and is also a text field.

Users qualify on the category attribute elements with the operator `In List`. Selections are chosen and they are saved in the metadata.
 
If a reload of the warehouse occurs on a report which filters on the Category attribute, the key value for category_ID may be changed. By executing a report or viewing the elements in the prompt, an incorrect value may be returned (presumably the old ID value). The SQL would be the same but the IDs will reference different DESCs.
 
Steps to reproduce in the Strategy Tutorial project:

1. Create a prompt: Filter Definition Prompt > Choose from an attribute element list > Select the attribute `Category` > Select the option `Choose default prompt answers` or 'Pre-defined list of elements'
2. Add the attribute element `Books` as default prompt answer or pre-defined element
3. Save the prompt.
4. Include the prompt in a report.
5. Execute the report > `Books` is displayed as default prompt answer
6. In the warehouse change the attribute element description from `Books` to `Textbooks`.
7. When in 2-tier mode, disconnect and reconnect to the project source.
8. Execute the report again > `Books` is still displayed as default prompt answer in the prompt resolution although it should be 'Textbooks'.
9. The report display will show the updated description.

Note: This has not been updated because the default prompt answer does not go back against the warehouse when resolving a pre-defined prompt answer or default answer.
 
Example 2
Filters were previously created using pre-defined elements. A filter was originally defined as City = 'New York'. Previously, New York`s ID was 1, but this has since changed on the database to an ID of 2. ID 1 now refers to 'Washington'. When the SQL is being generated for this filter, the old key/ID value is referenced and pulls data back for 'Washington' instead of 'New York.'
 
Steps to reproduce in the Strategy Tutorial project:

1. Create a prompt: Filter Definition Prompt > Choose from an attribute element list > Select the attribute `Category` > Select the option `Choose default prompt answers` or 'Pre-defined list of elements'.
2. Add the attribute element `Books` as default prompt answer or pre-defined element.
3. Save the prompt.
4. Include the prompt in a report.
5. Execute the report > `Books` is displayed as default prompt answer.
6. In the warehouse change the attribute ID from `Books` = 1 to `Books' = 2.
7. When in 2-tier mode, disconnect and reconnect to the project source.
8. Execute the report again > `Books` is still displayed as default prompt answer in the prompt resolution.
9. The report results display will now show a different element that corresponds to ID 2.

CAUSE:
By default, filter definitions are saved using the ID form of an attribute. Although the element name is stored in the filter definition, the metadata contains the ID and Description when creating a filter and qualifying on an attribute element list. The Strategy SQL Engine will then use the ID to qualify the attribute even though the ID was not specified anywhere in the filter definition. By design, the SQL Engine qualifies on the ID to optimize the SQL query. It is much faster to retrieve the attribute ID than it is to retrieve the attribute description from the database.
 
The Strategy SQL Engine is not designed to support warehouses that have ever-changing IDs or descriptions. It is designed to work with static IDs which are unique to each associated description. Environments should create static ID that match static Description.
 
Refer to the following Strategy Knowledge Base document for more information on primary keys :
KB7857: Rules and recommendations for creating primary keys in MicroStrategy Engine
ACTIONS:

• Rebuild the filter. Unselect and re-select the attribute elements and save and close.
• Create filters that qualify on the DESC form. That is, instead of choosing the `In List` for the filters, choose to qualify on the description (DESC) form and type in the desired qualification. When viewing the SQL for this report, observe that it will contain the SQL syntax (from the example above) Category_DESC = `Books` instead of Category_ID in (1). This is assuming the attribute descriptions do not change.
• Create a new attribute, which will only contain the Description(DESC) form expression of the element. Then create another attribute with only the ID form expression. Create a parent/child relationship between these attributes designating the parent as the ID attribute and the child as the DESC attribute.
• Import filtering elements from excel, text or flat file so that the IDs and Descriptions can be updated by the users before report executions.
• Define the original attributes' ID form as the DESC form and the DESC form as the ID form. However, by swapping the ID and DESC, the SQL engine would join this table on the DESC field instead of the ID and could lower performance.