Creating a Join Project - trillium_discovery - trillium_quality - 17.1

Follow the procedure below to create an join project defining n-way joins producing multiple output entities. These output entities can be instantly joined again with other entities to create multi-way joins.

To create a join project:

1. In the Control Center Home tab, click Join Projects. Alternatively, go the Discover > Analysis tab, right click the Join Projects node and select Create.

   The Create Join Project dialog opens.

2. In the Project Details section, verify the New option is selected.
3. In the Name text box, enter an appropriate name for the project.
4. In the Description text box, you can optionally enter a brief description of the join project.
5. In the Join Details section, verify the New option is selected.
6. In the Name text box, add a descriptive name for the join.
7. In the Description text box, you can optionally enter a brief description of the join.
8. Click Next. The Add Join: Select Entities page opens listing all loaded entities available to you. By default, the first LHS entity and the second RHS entity are selected.

   The list of entities varies and depends on the context of the create join wizard. For example, when creating a join in a new project, the list of entities shows all loaded real entities accessible to the current user. When editing a join, the wizard will show all the entities in the current project that are not upstream of the current join (except for the immediate parent entities).

   Note: When adding a new join to an existing project, it will additionally show all the entities in the current project. You cannot select the outputs from a previous join as inputs for a new join. If you try, you will get the following message: LHS and RHS entities cannot be derived from the same input entities.
9. From the LHS Entity list , select an entity.
10. In the LHS Entity Filter section, click the ellipsis button to open the Filter Entity window. The title of this window reflects the name of the entity on which it is invoked.
11. Select an attribute of the entity and define the filter value. The filter dialog allows you to create a filter predicate using the standard expression builder. You can use as many attributes as required.
12. Similarly, from the RHS Entity list , select an entity and then in the RHS Entity Filter section, define the filter value.
    Note: The expression builder on the Select Entities page allows an optional predicate to be added to each of the two selected entities so their rows can be filtered prior to being joined.

    For example, suppose there is a huge "order" table that you want to join with a "customer_order" table, and you are only interested in a limited number of orders over a certain age. In this case, you can add a filter to the order table "order.number <= 1000" to prevent orders with a number > 1000 from being included in the join. This ensures that:

    • Only the rows you are interested in will be joined, reducing noise in the join results.
    • The time taken to join the tables is reduced.
    • The space taken by the join index is reduced.
13. Click Next to open the Add Join: Join Criteria dialog.
14. Select the LHS Entity, then define the LHS Expression for the join by clicking the ellipsis button to configuring the Join Criteria window.
15. Similarly, select the RHS Entity, then define the RHS Expression for the join.
16. Click Add to add the join criteria to the project.
    Note: The left-hand-side and right-hand-side expressions must produce the same values in order to join a row.
17. Click Next to open the Add Join: Output entities page.
18. In the Job name text box, enter the name for the job.
19. Select one or more segments in the Venn diagram to specify which join segments to create as output entities.
    Note: Each input entity is labeled in the venn diagram. In case,the name is too big and not fully displayed, hover over to see the full entity name in the tooltip. Once you select an area, it is shaded.

    

     

20. Click the Add button to add the selected output entity. Alternatively, double-click the selected segment to add the output entity. There are six combinations for join segments:
    • Left Non Matching
    • Right Non Matching
    • Left Outer
    • Right Outer
    • Non Matching
    • Inner
    • Full Outer
    Note: There is no limit to how many entities you can create or the segments to which they can belong.
21. Optionally, click Modify to Editing Join Output.
    Note: To delete an output entity, select the entity in the Output Entities section and click Remove.
22. Click Next to open the Add Join: Summary page.
23. Click Finish to create the join project. The scheduler notification bar opens.
24. (Optional) Change the job name.
25. Do one of the following:
    • To schedule the job to run immediately, click Now. The message closes and the job begins running in the background.
    • Click Later. The Set Date & Time scheduler window opens. To immediately run the job in the background, click Run Now. To schedule a time, select a starting date on the calendar and select a time you want the job to run on the selected date. Click Submit to save the scheduled time.
    • Click Cancel to cancel the task.
26. After the job runs, go to the Join Projects node. Review the Join Project metadata and verify the newly created project is listed as an icon.