KB10296: Users see duplicate values when creating an element list prompt in MicroStrategy Developer 9.x

SYMPTOM:

A user sees duplicate values when creating an element list prompt in Strategy Developer 9.x.
 
Consider the following example:

• Suppose attribute "Child" is a child of attribute "Parent". The logical table structure will show the "Child" attribute as the only key:
  

• The user then creates a prompt on "Child" attribute. The SQL generated omits the "distinct" clause and the duplicated values are returned, as seen below:
  select count(a21.ChildId) WJXBFS1 from ParentChildGrandchild a21
  
• select a11.ChildId ChildId, a11.ChildDesc ChildDesc from ParentChildGrandchild a11 order by 1 asc
  
• The elements returned show duplicated values, just as in the data warehouse:
  

•  
  CAUSE:
  When selecting elements for the key attribute for the table, the SQL generated does not use a "distinct" clause, but when not selecting elements for the key attribute, the SQL generated uses a "distinct" clause. This is because the key attribute should always be unique in the warehouse table or it should not be used as the key for the table.
   
  ACTION:
  The lowest child attribute of a hierarchy should always have unique IDs.
   
  There are two workarounds to create prompts on "Child" with unique prompt values.
   
  
• Create an attribute with unique values and make it the child attribute of "Child".
  For example, a user creates another attribute, "Grandchild" to be child of "Child". "Grandchild" is mapped to a field with unique values in the warehouse. The logical table will list "Grandchild" as the key attribute:
  

•  
  Creating an element prompt on "Child" will again generate a "distinct" clause and return unique values.
   
  select count(distinct a21.ChildId) WJXBFS1 from ParentChildGrandchild a21
  
• select distinct a11.ChildId ChildId, a11.ChildDesc ChildDesc from ParentChildGrandchild a11
  
• Remove the parent-child relationship for "Parent" and "Child".
  
• This will force both attributes to be keys of the table.
• The prompt will show unique values and the SQL generated has a "distinct" clause again.

•  
  select count(distinct a21.ChildId) WJXBFS1 from ParentChildGrandchild a21
  
• select distinct a11.ChildId ChildId, a11.ChildDesc ChildDesc from ParentChildGrandchild a11 order by 1 asc