Skip to main content
Version Pentaho
Hitachi Vantara Lumada and Pentaho Documentation

Address Component Problems

  1. Last updated
  2. Save as PDF

Overview

Provides information on how to address component problems.

The resolution for several common component problems appear in the following sections.

Manage Multiple Outer-Joins

When you have three or more tables that require outer joins, the order in which the tables are joined is critical. Consider the example below:

48_outer_joins.png

In the sample preview below, the entries, 1, 2 ,3, and 4, in Table4 are taken and outer-joined with the records in the two other tables. The three other tables contain fewer records. The relationships are defined but now the order of execution critical. Relationship A is executed first, followed by B, then C.

49_execution_of_joins.png

Below is the query that is generated:

50_query_outer_joins.png

The nested join syntax that is generated forces the order of execution:

  • Join Table1 and Table2 (shown in red)
  • Join Table3 and A = B (shown in blue)
  • Join Table4 with B = Result

Other orders of execution are just as valid depending on the business context to which they are applied. Another order of execution will generate a different result. To allow business model designers to ensure that user selections are executed in a specific way, a Join Order Key is added to the Relationship Properties dialog box.

51_relationship_properties_dialog_box.png

The join order key is relevant only in instances in which outer joins are deployed in business models. To make the importance of the execution order apparent, this information is displayed in the graphical view of the model, as shown below.

Note: It is not mandatory to use uppercase letters, (A, B, C, as shown in the first image), to set the order in which tables are executed. Any alphanumeric characters (0-9, A-Z) can be used. The system will calculate the ASCII values of each character; the values are then used to determine the order of execution. In the example, A, B, C, AA, AB, Pentaho Metadata Editor will execute the table relationships in the following order: A, AA, AB, B, C.

Use the Delay Outer Join Conditions Property

To force conditions that would ordinarily be processed in the JOIN condition to be processed in the WHERE clause, follow the directions below to create a delay_outer_join_conditions custom property.

  1. Right-click on a business model and select Edit.
  2. Add a property by clicking the green + icon.
  3. Select Add a Custom Property and set its ID to delay_outer_join_conditions and select boolean for the Type, then click OK.
  4. Select the newly-created delay_outer_join_conditions property, then click the checkbox for delay_outer_join_conditions under the Custom heading on the right side of the window, then click OK.

Instead of the conditions being rolled into the JOIN clause, they will be allowed to roll down into the WHERE clause.