Version 2021

Setting join types for metrics

When you execute a report, data is often retrieved that has come from more than one table in your data source. The final results on any report are greatly dependent on the organization and structure of your data source tables and how data is stored within them.

When data is pulled from two or more tables of metric data in your data source, a metric join determines the way that data is combined into a single set of data. The order in which the data is joined from the different tables can affect the outcome of the data calculation, just as the order of operations in any arithmetic expression can affect the result.

  • An inner join includes only data that are common across all metrics in a report. This is the default value for report display. For example, to show sales and budget information for all regions, an inner join on the Sales and Budget metrics will yield a report showing only those regions for which there is both sales and budget information.

  • An outer join includes data that apply to every metric in a report. For example, to show sales and budget information for all regions, if Sales has an outer join and Budget has an inner join, the combination will yield all those regions for which there is both sales and budget information, and will also include all rows for sales. Outer joins are useful with rank metrics, because an attribute element will have a rank applied whether it has values or not. You want to see all ranked elements in a report's results, not just elements that had values.

Knowing how data is calculated for metrics on a given report is an important part of the data analysis process. Several decisions go into determining rules for how data is calculated when that data is pulled from different tables in your data source. Calculation rules for metrics are defined at several organizational levels:

  1. How data is calculated by default is usually decided first by the project designer, who implements several settings on a project-wide basis that affect how SQL handles your organization's data during calculation. These decisions are often driven by the type of database your organization owns; most databases process SQL differently. These settings are generally made within the VLDB properties for your project's database instance within MicroStrategy. For a list of metric-specific VLDB properties, and instructions to change them, see Defining metric-specific VLDB properties.

  2. Next, the person who designs a metric can override the project-level default settings described above when she creates the metric. When a metric designer applies settings to a specific metric, these settings are effective for that metric no matter which reports the metric is used in. For instructions to define the metric calculation settings at the metric level, see Setting the metric join type at the metric level. For instructions to define the metric calculation settings for a compound metric, see Setting the metric formula join type for compound metrics.

  3. Finally, report analysts can change how a metric is calculated for a single report with which the analyst is concerned. You can view the existing settings for a metric, as well as change various settings, within the Report Data Options dialog box. (For steps to do this, see Setting the metric join type at the report level, which also contains examples and a reference on when to use an inner join vs. an outer join.) Any changes made to metric joins in the report will override any join settings that were made by the metric designer or by the project designer, described above. However, changes made to the join type using the Report Data Options dialog box affect this metric on this report only. When the metric is used on another report, it uses its default metric join type.

You can also set how database tables are joined in a report. For more information, see Performing an outer join against a lookup table.

For additional information and join type examples, refer to the Advanced Reporting Help.