Create Live Access Compound Tables with Modeler Connect

The Modeler Connect options includes a Create/Edit Compound Table option.

Then assemble your Compound table in a similar fashion as the merge transformation.

Note: There is also an option to enter native SQL to create your compound table.

Example using Query option:

You can add multiple join conditions to build your desired compound table.

After creating the joins, you can click on Proceed or “Select Columns” to edit the columns you would like to include in your compound table, or click “Save” to have Birst automatically assemble the Compound Table with all columns from all joined tables included by default.

The system selects the columns and lets you manually remove any duplicate columns or whole tables be de-selected the checked items.

If you click Save, Birst will know to ignore duplicate columns from the joined tables.

You can also edit a Compound Table.

The edits can be at the columnar level where in the following example we de-selected the CategoryID column from the Categories parent table and selected the CategeoryID column from the Products child table.

When deleting Joins

Important: If you need to remove a join, you must do so linearly or by the last level. For example, if I wanted to remove the join between Categories and Products in the above example, I would need to first remove the Products and Order_Details join, then remove the Categories and Products table joins.

Notice that the first join cannot be deleted in this case because all further joins are based on the result of the prior joins. Deleting “join #1” could break joins #2, join #3, etc. The join conditions can however be edited.

Live Access sources including compound tables can be related.