Relating Column Data with SQL: Joins

The table below summarizes the Joins VLDB properties. Additional details about each property, including examples where necessary, are available by clicking on the links in the table.

Property	Description	Possible Values	Default Value
Attribute to Join When Key From Neither Side can be Supported by the Other Side	Controls whether tables are joined only on the common keys or on all common columns for each table.	Join common key on both sides Join common attributes (reduced) on both sides	Join common key on both sides
Base Table Join for Template	Controls whether two fact tables are directly joined together. If you choose Temp Table Join, the Analytical Engine calculates results independently from each fact table and places those results into two intermediate tables. These intermediate tables are then joined together.	Temp table join Fact table join	Temp table join
Cartesian Join Evaluation	Allows the MicroStrategy SQL Engine to use a new algorithm for evaluating whether or not a Cartesian join is necessary.	Do not reevaluate cartesian joins Reevaluate cartesian joins	Do not reevaluate cartesian joins
Cartesian Join Warning	Action that occurs when the Analytical Engine generates a report that contains a Cartesian join.	Execute Cancel execution Cancel execution only when warehouse table is involved in either side of cartesian join If only one side of cartesian join contains warehouse tables, SQL will be executed without warning	Execute
Downward Outer Join Option	Allows users to choose how to handle metrics which have a higher level than the template.	Do not preserve all the rows for metrics higher than template level Preserve all the rows for metrics higher than template level w/o report filter Preserve all the rows for metrics higher than template level with report filter Do not do downward outer join for database that support full outer join Do not do downward outer join for database that support full outer join, and order temp tables in last pass by level	Do not preserve all the rows for metrics higher than template level
DSS Star Join	Controls which lookup tables are included in the join against the fact table. For a partial star join, the Analytical Engine joins the lookup tables of all attributes present in either the template or the filter or metric level, if needed.	No star join Partial star join	No star join
From Clause Order	Determines whether to use the normal FROM clause order as generated by the Analytical Engine or to switch the order.	Normal FROM clause order as generated by the engine Move last table in normal FROM clause order to the first Move MQ table in normal From clause order to the last (for RedBrick) Reverse FROM clause order as generated by the engine	Normal FROM clause order as generated by the engine
Full Outer Join Support	Indicates whether the database platform supports full outer joins.	No support Support	No support
Join Type	Type of column join.	Join 89 Join 92 SQL 89 Inner Join and Cross Join and SQL 92 Outer Join SQL 89 Inner Join and SQL 92 Outer Join and Cross Join	Join 89
Lookup Table Join Order	Determines how lookup tables are loaded for join operations.	Partially based on attribute level (behavior prior to version 8.0.1) Fully based on attribute level. Lookup tables for lower level attributes are joined before those for higher level attributes	Partially based on attribute level (behavior prior to version 8.0.1)
Max Tables in Join	Maximum number of tables to join together.	User-defined	No limit
Max Tables in Join Warning	Action that occurs when the Analytical Engine generates a report that exceeds the maximum number of tables in the join limit.	Execute Cancel execution	Cancel execution
Nested Aggregation Outer Joins	Defines when outer joins are performed on metrics that are defined with nested aggregation functions.	Do not perform outer join on nested aggregation Do perform outer join on nested aggregation when all formulas have the same level Do perform downward outer join on nested aggregation when all formulas can downward outer join to a common lower level	Do not perform outer join on nested aggregation
Preserving Data Using Outer Joins
Preserve All Final Pass Result Elements	Perform an outer join to the final result set in the final pass.	Preserve common elements of final pass result table and lookup/relationship table Preserve all final result pass elements Preserve all elements of final pass result table with respect to lookup table but not relationship table Do not listen to per report level setting, preserve elements of final pass according to the setting at attribute level. If this choice is selected at attribute level, it will be treated as preserve common elements (that is, choice 1)	Preserve common elements of final pass result table and lookup/relationship table.
Preserve All Lookup Table Elements	Perform an outer join to the lookup table in the final pass.	Preserve common elements of lookup and final pass result table Preserve lookup table elements joined to final pass result table based on fact table keys Preserve lookup table elements joined to final pass result table based on template attributes without filter Preserve lookup table elements joined to final pass result table based on template attributes with filter	Preserve common elements of lookup and final pass result table