Version 2021

Performing an outer join against a lookup table

You can set how to join lookup tables in a report. By default, inner joins are used, which retrieve only the data common to all the tables accessed by the report. If you need to display all the data in all the tables, use an outer join.

Inner joins retrieve only the data common to all the tables accessed by the report. For example, a lookup table includes the 4 years from 2001 to 2004, but the revenue fact table contains no data for 2004. When a report containing year and a Revenue metric is run, only 2001, 2002, and 2003 are displayed. Because the SQL engine is accessing a fact table that does not contain data for 2004, a row for 2004 is not displayed.

Use an outer join against the lookup table to display a row whether or not the fact table includes data for that row. Before you can perform an outer join, you must allow outer joins to be supported. This setting is a VLDB property at the project level. See Details for All VLDB Properties in the System Administration Help for more information. Then you can set the report to use an outer join. This VLDB property is located at the report level. See VLDB Properties at the Report Level in the Advanced Reporting Help for more information.

This join type is not the same as metric joins, which determine how metrics are joined. That is, they allow you to choose between inner and outer joins. Table joins determine how tables are joined in a report. For more information on metric joins, see Setting join types for metrics.

Prerequisite

Before you can perform an outer join, you must allow outer joins to be supported. The following procedure includes these steps.

To perform an outer join against a lookup table

  1. Open the Project Configuration Editor. (How?)

  2. Expand Database instances. Select SQL Data Warehouses. The right side of the interface displays the Database instances-SQL Data Warehouses screen.

  3. Click VLDB Properties on the right. The VLDB Properties (Database Instance) dialog box opens.

  4. Select Show Advanced Settings from the Tools menu.

  5. Click the Joins folder to expand it and then choose Full Outer Join Support. The Full Outer Join Support interface opens.

  6. Clear the Use default inherited value check box. The options on the top become active.

  7. Select Support.

  8. Click Save and Close. You are returned to the Project Configuration Editor.

  9. Click OK. After the project information is processed, you are returned to MicroStrategy Developer.

  10. Open your report in the Report Editor. (How?)

  11. Select VLDB Properties from the Data menu. The VLDB Properties (Report) dialog box opens.

  12. Click the Joins folder to expand it and then choose Preserve all lookup table elements. The Preserve all lookup table elements interface opens.

  13. Clear the Use default inherited value check box. The options on the top become active.

  14. Select the appropriate setting. Note that the first setting, Preserve common elements of lookup and final pass result table, does not allow outer joins. For more information on the settings, see Report Data Options dialog box: Attribute Join Type.

  15. Click Save and Close. You are returned to the Report Editor.

Now when you execute the report, you will see the results of an outer join.

Related Topics