Define Joins between Columns in Database Tables

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.

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.

MicroStrategy automatically generates SQL when retrieving data from the database. Creating joins now helps prevent cross joins during this automated process.

See the Advanced Reporting Help for a detailed explanation of joins.

To Define a Join Between the Columns of Two Tables

1. You can define joins between columns on the Import from Tables dialog while building a query for importing your data.

2. Drag the column from the first table onto the column from the second table. An inner join is created and a line representing the join appears in the Query Builder pane, running from the column in the first table to the column in the second table.
3. You can specify additional options for the join, such as the join type or join operator. Click the line representing the join to display the available options.
4. Select Inner Join to only include records in which the joined columns from both tables satisfy the join condition.
5. Select Left Outer 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.

6. Select Right 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.

   Right outer joins are not supported for Google BigQuery data.

7. Select Outer Join to include all records from both tables.
8. Select More Options to choose a join type and operator for comparing the columns.

Related Topics

Import Data from a Database, Hadoop, or Google BigQuery by Building a SQL Query