Setting Up Joins

If you have a Discovery space, after uploading data you need to define the relationships between your sources by creating joins.  See About Joins.

If you have an Advanced space and are using the Update Warehouse Model data model automation tool to create the dimensional data model, you also need to create joins between your sources.

You can also create joins between Discovery sources in an Advanced space to existing measure and dimension tables in the Birst data store.

If you are using Managed Data Mashups and import a package, you will need to set up joins between your existing sources and the new sources you imported.  

To create joins between different types of sources, see Setting Up Federated Joins.

Prerequisites for Joins

For a list of the types of joins that are permitted in Birst, see Permissible Joins.

The following criteria must be satisfied in order to join two sources:

  • The primary key in the source you are joining from must exist, having the same name, in the source you are joining to.
  • The primary key in the source you are joining from and the primary key in the source you are joining to must be the same data type. For example: Integer to Integer or Varchar to Varchar.
  • The column width of the primary key must be the same. For example, if the column width of the primary key in the source you are joining from is 30 and the column width of the primary key in the source you are joining to is 35, a join will not be permitted.

Note: If you rename a column that is the primary key in a Live Access space, joins no longer work. In addition, new joins, even though they are visible in Data Flow, do not work. The work around is to delete the join first, then rename the column and join again.

To create a join from one Birst data source to another

  1. Go to Admin - Define Sources - Data Flow. The diagram shows your uploaded and enabled data sources. If you have set up hierarchies and defined the grain of your data sources, Birst automatically creates join relationships between sources with matching key columns. A join is indicated by a line connecting two data sources.

     

    Tip: If you have imported a package, the imported sources are identified with an asterisk (*) in front of the name and the name is italicized (for example: *Categories).

  2. Right-click on a data source and select Join to a Related Source. The sources you can join to will be displayed. Sources that cannot be joined will disappear.

    Tip: If the data source you want to join to does not appear when you select Join to a Related Source, you may need to change a column property, such as the name, so that it matches the other source. Right-click on the source and select Manage Sources to view or change the column properties of the source, such as the name, data type or width. See Defining Column Properties.

    Tip: There is a Complex Join to a Related Source option for Live Access sources and inherited sources.

  3. Drag the line to the source or dimension or measure you want to create the join to. The join is created (an inner join is the default).

  4. There are three types of relationships: Inner Join, Left Outer Join and Right Outer Join. To modify the type of relationship between two data sources, click on the line and select Properties from the pop-up menu.

    Tip: To delete a join, click on the line (the relationship) you want to remove and select Delete Relationship from the pop-up menu.

  5. In Infor Express, after creating the joins, you can click the Process and Analyze link at the top of the page to process your data. After your data has been successfully processed, it will be available for analysis and use in Designer and Dashboards.

To create joins between tables from the same Live Access source

  1. Go to Admin - Define Sources - Manage Local Sources.
  2. Click the Joins dropdown list.
  3. In the table list, click the Join Condition field and enter the join condition.

    The syntax to use when joining tables is:
    "Logical table name1".physical_column_name1[operator]"Logical table name2".physical_column_name2

    For example:
    "Orders".ShipVia="Shippers".ShipperID

    Tip: To reference a compound table source, the syntax to use is:
    "Logical table name".physical_table_name.physical_column_name
  4. Click Save.

Tip: You can create federated joins between Live Access and the Birst data store, and between two Live Access sources. See Setting Up Federated Joins.

See Also
About Joins
Permissible Joins