Setting the metric join type at the report level

Before you begin

An understanding of your organization's data source storage structure is helpful to understand the details of metric joins.

Metric join types: An overview

Setting a join specification allows you to place conditions on rows selected for display. Setting the metric join type at the report level determines how metrics are joined together in the report. It overrides the metric join settings for that report only. This setting is defined in the Report Data Options dialog box of the Report Editor or Template Editor.

A metric is often calculated based on data that comes from more than one table in your data source. Data coming from multiple tables must be joined together in some way during data calculation.

A metric join setting determines how data is combined by applying a specific type of join, inner or outer. The MicroStrategy SQL Engine applies the designated join type to the data pulled from your data source's tables. The join type places conditions on the data to be displayed in the report.

Inner and outer joins are discussed with examples below.

• Inner join: An inner join includes in the calculation only the data common to all the tables from which data is being gathered in your data source.

• Outer join: An outer join includes in the calculation all of the data in all of the tables from which data is being gathered in your data source.

You can also set join types at the metric and formula (for a compound metric) level. For a description of how the different levels work together, see Setting join types for metrics.

Examples of inner and outer joins

Inner joins are generated by default for all metrics in a report. The resulting report contains only those rows that have data returned for all the metrics.

For example, review the data in the following table. The Sales Information and Budget Information columns show whether data exists in the data source for that type of data in that region.

Region	Sales Information?	Budget Information?
North	Yes	No
South	Yes	Yes
East	Yes	Yes
West	No	Yes

A report is created that contains the Sales and Budget metrics, and the Region attribute. The default inner join is not changed, because you want to view metric values that are common to both metrics and that are therefore not empty for either metric. Since the North region does not have any budget data, as shown in the table above, no data is displayed for the North region on the report. Similarly, the table above shows that sales data has not been tracked for the West, so all data for the West region is also omitted from the report. The resulting report, with an inner join between metrics, displays only those regions that have both sales and budget information, or data that is common to all components of the join. The result looks like the following report:

However, assume that you need to change your analysis and you want to display all of the data from the tables in your data source, whether or not data exists for all the metrics at all levels in the report. (For a definition and examples of levels, see The level of data aggregation.) You apply an outer join to both metrics because you know there is some incomplete or empty data for some regions in your data source. The outer join results in the following report, in which the North and West regions appear even though they have no data for one of the metrics.

Finally, you can specify different joins for each of the metrics on a report. You want to see all sales data even if budget data has no values for some regions in your data source, so you apply an outer join to the Sales metric and an inner join to the Budget metric. All regions (all report rows) with information on sales are displayed. The following report is created:

West is not displayed because it does not contain sales information. It is irrelevant whether data exists for the Budget metric or not.

When to use an inner or outer metric join

Inner joins

An inner join is generally more commonly used for metric data than outer joins. (The exception is with rank metrics; see Outer joins below for details.) Inner joins are effective in many situations, including the following:

• Inner joins provide effective results when you know the metrics on your report are closely related to each other, such as the Revenue metric and the Profit metric.

• Inner joins are most effective if your data source contains relatively complete metric data, without empty values.

• Inner joins require less processing time than outer joins, so they are useful to lessen the processing load on your MicroStrategy Intelligence Server machine.

Outer joins

Outer joins are effective if your data source contains empty values for some metric data in some tables. Outer joins are also necessary for metrics that show rank. Outer joins allow you to see all data that is available for a metric.

For example, your revenue data may be completely up to date, but several profit values have not been reported and entered in the data source for certain days during the past week. When the Revenue and Profit metrics are both included on the same report, you can apply an outer join to the Revenue metric so that you can see all values for Revenue for each day of the past week, even if the Profit value for a given day is currently empty.

When a metric calculates rank, it is important to use an outer join on the rank metric. If the default inner join is used on a rank metric, some of the ranks (and therefore, the ranked attribute elements) may not appear on the report because an inner join does not include elements with null values in the result set. But an element with a null value may have a rank. With an outer join, all rows are displayed on the report even if there is no result displayed for some of the elements for some of the metrics on the report. The goal for a rank metric is to display all rankings, so all elements must appear whether they have values or not.

To set the metric join type at the report level

Prerequisite

Join type selection, in this context, applies solely to metrics; the procedure described below, therefore, is based on the assumption that the selected report contains at least one metric.

1. Open the report in the Report Editor. (How?)

   You can also set the metric join type for a template. Open the template in the Template Editor; the rest of the instructions remain the same.

2. From the Data menu, select Report Data Options. The Report Data Options dialog box opens.

3. Under Categories, expand Calculations, and then select MetricJoin Type. Calculations - Metric Join Type appears on the right side of the editor.

4. Select the metric to change. Use the drop-down menu in the Join Type column to choose one of the following join types:

   • Default uses the metric join type set on the individual metric. The metric's join type is listed.

     To restore default values to all joins in the report, click Restore Metric Defaults. For more information on metric defaults, see Restoring metric default values.

   • Inner includes only the elements common to both tables, regardless of the individual metric join type setting.

   • Outer includes all of the elements in both tables, regardless of the individual metric join type setting.

5. Click OK. You are returned to the Report Editor.

Related Topics

• Report Data Options: Metric Join Type dialog box