Setting Up a Complex Join

You can join tables that have different column names for the same information in different sources. For example, one table might have a column called ShipVia but the same column in another table is called ShipperID. To join these two tables, you can create a complex join.

Birst supports complex joins for:

Live Access sources. See Live Access.
Inherited sources. An inherited source can be joined to another inherited source, to a dimension table or to a fact (measure) table. See Creating an Inherited Source.
Imported packages in a child space for a Managed Data Mashup. See Parent to Parent Joins.

To set up a complex join for a Live Access or inherited source

Go to Admin - Define Sources - Data Flow.
Right-click a source and select Complex Join to a Related Source.
Drag the line to the dimension or measure you want to create the join to. The Complex Join Definition dialog opens.
Note: If you employ a Left or Right Outer join type, the source that you start with (select and right-click) is always the left hand side of the join. For example, if you select Source A and specify a Left Outer join type for Source A, the return values will be all of the rows of source A plus any matching values with source B (the source you are joining with). If you select source A and specify a Right Outer join type, all of the rows for source B are selected and only the matching rows of Source A (the selected source) are returned.
In the Complex Join Definition dialog box, complete the join condition. The table names are provided but not the column names so you need to provide the column names.

Use the following syntax for Live Access joins:
"logical_table_name_1".physical_column_name_1JOIN_OPERATOR"logical_table_name_2".physical_column_name_2

For data store joins surround the logical table name in double quotes and add a dollar sign ($) after the physical column name. For example:
"Orders".ShipVia$="Shippers".ShipperID$

To reference a compound table source, use the following syntax:
"logical_table_name".physical_table_name$.physical_column_name$

Syntax Tips:

When referencing a table in the data store (warehouse), include a dollar sign ($) at the end of the physical column name (the only exceptions are SCD_END_DATE, SCD_START_DATE, and LOAD_ID). To determine the physical column name, convert the logical column name by replacing any spaces or punctuation with an underscore and adding a $. For example, ShipperID becomes ShipperID$ and First Name – Customer becomes First_Name___Customer$.

When there is a dollar sign ($) in a physical column name, convert it to the capital letter D. For example, $ Sales – Gross becomes D_Sales___Gross$.

When there is a hash sign (#) in a physical column name, convert it to the capital letter N. For example, # Users becomes N_Users$.

The logical table name of an inherited source is the name of the inherited source (the name that was entered when it was created).

To see the logical and physical table names for a dimension or measure, right-click the dimension or measure and select Properties.

You can change the Join Type. Check the Invalid Join box to ignore the join. Check the Redundant Join box to mark the join as a self-join. In this case the join condition is not used so you can use a dummy join condition like 1=1. See Redundant Joins.
Click OK. The two sources are joined.

Note: If you working with a complicated data model that employs more than one complex join, you may observe red dots on some of the sources for which you created a complex join. The red dot indicates that the source is a child of the parent table or the many of the one-to-many relationship.

Note: Birst must adjust the direction of the join in the physical query depending on the way the join is being written in the query. For example, if you create a left outer join from an Inherited Dimension (A) to a Fact (B), you can right-click on Inherited Dimension (A) and create the complex join by selecting LEFT OUTER. However, when Birst writes this query, it will always start with the fact table, so what you will see in the physical query is "Fact B RIGHT OUTER JOIN Inherited Dim A."