Creating Complex Joins

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:

  • Lice access sources
  • Inherited sources. An inherited source can be joined to another inherited source, to a dimension table or to a fact (measure) table
  • Imported packages in a child space for a Managed Data Mashup
  1. From Modeler Relate, select View Data Flow.
  2. Create a complex join by either:
    • Right-clicking on the source and selecting Make Complex Join.

    • Selecting the source from Dim. and Fact Tables and selecting the Make Complex Join icon.

  3. Select the Join Type.
    • Inner Join
    • Left Outer Join
    • Right Outer Join
    • Full Outer Join


  4. Complete the join condition. If you specify characters in the Search, the application displays possible results based on those characters. You can use the icons to filter for Dimension Tables, Fact Tables, or Sources.
    The table names are automatically added when the table is selected but you must specify the column names.


  5. Specify the column names:
    • For data store joins surround the logical table name in double quotes and add a dollar sign ($) after the physical column name. "Orders".ShipVia$="Shippers".ShipperID$
    • 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
    • To reference a compound table source, use the following syntax:"logical_table_name".physical_table_name$.physical_column_name$
  6. Optionally, select Redundant Join to mark the join as a self-join. This should be used when a physical table or table source serves as both a source for measures and for dimension columns.
  7. Click Save.
  8. After your join is created, you can click on the join to Edit or Delete the join.

Syntax Guidelines:

  • 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 table from either the complex join or compound table interface:
    • When using complex joins, the logical table name will be pre-filled in the join condition
    • When using the compound table interface, the logical and physical table names can be found in the interface (logical being the top name, physical the bottom one).
Note: Users can copy the physical table name by hovering over the table and selecting from the tooltip.