Follow the suggestions in these topics to help resolve common issues with Pentaho Metadata including the Metadata Editor and the Pentaho User Console components which auto-generate metadata models:
- Managing Multiple Outer-Joins
- Slow Responses or Inconsistent Results in Reports
- Metadata-Based Reports Generated in Version 4.8 Produce Different Results in Version 5.x
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.
- Right-click on a business model and select Edit.
- Add a property by clicking the green + icon.
- Select Add a Custom Property and set its ID to delay_outer_join_conditions and select boolean for the Type, then click OK.
- 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.
Slow Responses or Inconsistent Results in Reports
Your reports may produce slow responses or generate inconsistent results. If a report contains inconsistent or incorrect results, and the SQL command contains several joins, make sure the join order has been specified for that report.
Beginning in version 5.0, inner joins are resolved before outer joins. If you have several tables that require outer joins, the order in which the tables are joined is critical because different join orders produce different results. To make the join order consistent, set the Join Order Key field in the Relationship Properties window. For more information, see Managing Outer Joins.
Metadata-Based Reports Generated in Version 4.8 Produce Different Results in Version 5.x
In version 5.0 of the software, inner joins are resolved before outer joins by default. If you are migrating a report from version 4.8 to 5.x, you can specify which method to use by setting the legacy_join_order parameter in the report’s .xmi file. Set legacy_join_order to true to use the 4.8 method. Set legacy_join_order to false to use the 5.0 method.
In addition to setting the legacy_join_order parameter, set the Join Order Key field in the Relationship Properties window.