KB19863: How is a Many-to-Many (M:M) relationship between two attributes converted into SQL in MicroStrategy 9.x and 10.x.

When users are creating the logical schema (i.e. schema objects), one of the most important steps is the definition of the parent-child relationships between the different attributes of a project.
 
The Strategy SQL Engine uses these relationships when defining the join tree that will appear in the FROM section of a SQL pass (how to connect the different tables needed to support the calculation through joins, and which table columns will be used to join them), when the tables contain the columns specified in the SELECT section of the SQL pass or when a table is joined as a bridge in the process of getting to the appropriate table.
 
There are three different types that can be set in a parent-child relationship:
 

• One-to-one: for an specific attribute (parent) element, there can be one and only one attribute (child) element
• One-to-many: for an specific attribute (parent) element, there can be one or many attribute (child) elements
• Many-to-Many: for an specific attribute (parent) element, there can be one or many attribute (child) elements, and vice versa.

When a user specifies a M:M relationship, the following considerations must be taken into account:
 

1. If a M:M relationship is defined between two attributes, and the two attributes are mapped to a logical table, the Strategy SQL Engine will mark them both as key attributes
2. When building reports involving attributes contained in a M:M relationship, the Strategy SQL Engine, in consequence, will use both ID columns of the parent and child attributes to perform a join between the two tables containing the attributes defining the M:M relationship (i.e. if Table X and Table Y have Attribute A and Attribute B mapped, and A and B are defined as a parent-child with a M:M relationship type, the A and B keys will be used to join the tables)
   In the SQL below, an example of the joining based on the ID columns is presented (where Student and Teacher have an M:M relationship defined and both are mapped to tables a11 and a12):
    
   Select a11.student_id student_id,
   a12.Student_name student_name,
   Sum(a11.grade) grade
   From fact_grades a11 join lu_student a12
   On (a11.student_id = a12.student_id
   AND a11.teacher_id = a12.teacher_id)
   
3. If a report filter and/or metric conditionality is based on one of the two attributes defining the M:M relationship, setting the option 'Remove related report filter elements' in a metric having a Dimensionality related to these attributes will not remove the filter from the SQL pass. In a M:M relationship, both attribute ID forms are considered as independent keys. Since the ID form defining the report filter and/conditionality is an independent key, Strategy will determine that removing the filter conditions is not safe for data integrity, and will apply all the filters to the metric calculation

For more information regarding this, review the following technical notes:
 

• KB3860 : The dimensionality of a metric is ignored when running a report in Strategy Desktop 9.x and above where there is a many-to-many relationship between the attributes in the template and the dimensionality of the metric 
• KB9731 : When should attribute relationships be modeled as separate attributes in a parent-child relationship and when should they be modeled as forms of the same attribute?