Join Options dialog box

When you are importing data from a database, you can use a join to relate data from one table with data from another table. Creating this relationship between data in different tables allows you to include the data from multiple database tables on the same dashboard. The join specifies a column that the two tables have in common. In most cases, an ID column can be used to join two tables. The column used to join tables should keep the records unique, to ensure that the records are combined accurately.

For example, you have a City_ID attribute that uniquely identifies each city in your data source. If your first table contains City_ID and Revenue columns, and the second table contains City_ID and Profit columns, you can relate the data in the tables by creating a join between the two City_ID columns. This allows you to create a dashboard that shows both Revenue and Profit for each city.

Creating a join is important to avoid a Cartesian product, or cross join, when MicroStrategy automatically generates the SQL to retrieve data from your database tables.

Steps are below to create a join between the columns in two database tables and to select additional options for the join, such as the join type and join operator.

The following options are available in the Join Options dialog box:

Join: Determine how the data records from the two tables are included in the join. The following join types are available:
Inner Join: Only include records in which the joined columns from both tables satisfy the join condition.
Left Outer Join: Include all records from the column in the first table and only those records from the column in the second table in which the join condition is satisfied.
Right Outer Join: Include all records from the column in the second table and only those records from the column in the first table in which the join condition is satisfied.
Outer Join: Include all records from both tables.
Join operator: Select the operator used to compare columns in the join, such as greater than (<) or equals (=).

Prerequisite

You are importing data from a database by building a SQL query and have added at least two database tables to the editor pane. For steps to import data from a database, see Importing data from a database by building a SQL query.

To define a join between two tables

1 In the Editor panel of the Import from Tables page, click and drag the name of the column from the first table onto the column from the second table. An inner join is created and a line representing the join is displayed in the Editor panel, running from the column in the first table to the column in the second table.
2 You can specify additional options for the join, such as the join type. Click the line representing the join, then select one of the following:
To delete the join, select Delete.
To only include records in which the joined columns from both tables satisfy the join condition, select Inner Join.
To include all records from the column in the first table and only those records from the column in the second table in which the join condition is satisfied, select Left Outer Join.
To include all records from the column in the second table and only those records from the column in the first table in which the join condition is satisfied, select Right Outer Join.
To include all records from both tables, select Outer Join.
To access more options, select More Options. The Join Options dialog box opens. Select the appropriate options to define the join as described above, then click OK to apply your changes.

Related topic

Importing data from a database by building a SQL query