Criteria used by the MicroStrategy SQL Engine to build the join tree of a report’s SQL

In order to perform a calculation, the MicroStrategy 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 tables and views are logical tables.By default Logical tables are placed in the Schema Objects/Tables folder. A logical table contains two views:
  • Logical View: This view shows all MicroStrategy 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.
We can have multiple logical view for one physical view in case we have table alias. 

The MicroStrategy 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 MicroStrategy 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
It would take time and requires a relationship table to resolve the problem.

Hope these points helps to understand the SQL engine behavior more better. Please comments and provide the feedback.

Comments

Post a Comment