Strategy ONE

Metric Join Type

Metric Join Type is used to determine how to combine the result of one metric with that of other metrics. When this property is set to Outer Join, all the result rows of this metric are kept when combining results with other metrics. If there is only one metric on the report, this property is ignored.

There are multiple places to set this property:

  • At the DBMS and database instance levels, it is set in the VLDB Properties Editor. This setting affects all the metrics in this project, unless it is overridden at a lower level.
  • At the metric level, it can be set in either the VLDB Properties Editor or from the Metric Editor's Tools menu, and choosing Metric Join Type. The setting is applied in all the reports that include this metric.
  • At the report level, it can be set from the Report Editor's Data menu, by pointing to Report Data Options, and choosing Metric Join Type. This setting overrides the setting at the metric level and is applied only for the currently selected report.

There is a related but separate property called Formula Join Type that can also be set at the metric level. This property is used to determine how to combine the result set together within this metric. This normally happens when a metric formula contains multiple facts that cause the Analytical Engine to use multiple fact tables. As a result, sometimes it needs to calculate different components of one metric in different intermediate tables and then combine them. This property can only be set in the Metric Editor from the Tools menu, by pointing to Advanced Settings, and then choosing Formula Join Type.

Both Metric Join Type and Formula Join Type are used in the Analytical Engine to join multiple intermediate tables in the final pass. The actual logic is also affected by another VLDB property, Full Outer Join Support. When this property is set to YES, it means the corresponding database supports full outer join (92 syntax). In this case, the joining of multiple intermediate tables makes use of outer join syntax directly (left outer join, right outer join, or full outer join, depending on the setting on each metric/table). However, if the Full Outer Join Support is NO, then the left outer join is used to simulate a full outer join. This can be done with a union of the IDs of the multiple intermediate tables that need to do an outer join and then using the union table to left outer join to all intermediate tables, so this approach generates more passes. This approach was also used by MicroStrategy 6.x and earlier.

Also note that when the metric level is higher than the template level, the Metric Join Type property is normally ignored, unless you enable another property, Downward Outer Join Option. For detailed information, see Relating Column Data with SQL: Joins.

Levels at Which You Can Set This

Database instance and metric