KB19866: What are the criteria used by the MicroStrategy SQL Engine to build the join tree of a report’s SQL in MicroStrategy Developer 9.x?

In order to perform a calculation, the Strategy SQL Engine needs to determine which tables are needed to support it. These tables include:

• Base table: the base tables are the logical tables that best support the calculation of metrics
• Lookup tables: the lookup tables are the logical tables that need to be used in order to get the required attribute forms of the attributes used in the report

The logical tables are placed by default in the Schema Objects/Tables folder. A logical table contains two views:

• Logical View: this view shows all Strategy objects mapped to the table. This objects include Attributes and Facts
• Physical View: this view shows all the warehouse table's columns existing in the logical table.

The Strategy SQL Engine uses the Logical View to determine which tables support calculations:

1. Base tables
   
1. The Fact object must be mapped to the Logical table
2. The Attribute objects needed to perform the calculation (represented in the Dimensionality and Conditionality of the calculation) must be mapped to the Logical table.
   
1. If the attributes needed are not present in the table, the table will still support the calculation if the child attributes are mapped to it.
2. If no attributes nor child attributes are mapped to the table, the table will still support the calculation if there is a fact extension defined for the required attributes
3. If there is more than one table that supports the calculation, the Logical table size property is used to select one. The table picked will be the one with a smaller value for this property
2. Lookup tables.
   
1. In every intermediate SQL pass, only the ID forms of each attribute are required. The Engine will include extra attributes forms only if the forms are available in tables already present in that pass
2. If there are different attributes from the same hierarchy in the Dimensionality of the intermediate SQL pass, only the ID form of the lowest-level child will be used
3. There are different VLDB properties available in order to manipulate the attribute form selection in intermediate passes.
4. All the attribute forms not yet selected will be obtained from the Logical table defined as the Lookup table for the specific attribute.

Once the tables needed to support the calculation, the Strategy SQL Engine needs to determine how to build the Join tree for the report's SQL:

• The base table is the first table (root) of the Join tree.
• To join the base table(s) with the lookup table(s), find a link between the attribute keys from both tables:
  
• Search-down: if the link is not direct, the SQL Engine will search down using existing parent-child relationships between the Attribute objects
• Search-up: if no children were found, the search goes up, searching for links based on parent attributes.
• The tables used to connect attributes depend on relationship table set in attribute relationship
• Many-to-Many relationships
• Example:
  
• Base table key is Item, Dimensionality is Category
• Must search DOWN from Category to Item