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 MonetDB and other problems.

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

Troubleshooting MonetDB for Instaview

Instaview loads ETL models, dimensional schema, and data in a MonetDB-based database. When a new Instaview project is created or an existing one is opened, the MonetDB server is started. Most issues that can occur relate to the way that Pentaho controls MonetDB.

Connection Errors

Connection errors sometimes occur if there are MonetDB port conflicts. This sometimes occurs if you have installed Instaview in the past using one method, then reinstalled it using a different method. Check the share.xml file and modify the MonetDB port number in the Agile BI section so that it is not 50000, but another number such as 50006. Exit from Instaview and Spoon and try to start them both again.

General Errors

The Monetdb server is started when a new Instaview project is created or an existing one is opened. The server is shutdown when the project is closed. Pentaho provides scripts to start and stop the server. These scripts are located in the data-integration/plugins/spoon/agile-bi/platform/pentaho-solutions/system/instaview/scripts directory.

For testing or debugging, these scripts can be run with no arguments passed in. However, defaults are used for parameters that are not passed in. Each operating system has different defaults, the configuration definitions in the previous examples drive the values passed in to these scripts when executed from within PDI/Instaview. When running the scripts, take care to pass in the correct values, otherwise the database may not start up properly. Open the script in a text editor before calling it to determine what parameters you might need. Here are some example paramaters. None of the parameters are required, but are key if you stray from the default configurations.

For Windows

startMonetDb.bat DB_NAME MONETDB_INSTALL_DIR
Parameter Default Value
DB_NAME pentaho-instaview
MONETDB_INSTALL_PATH MONETDB_INSTALL_DIR environment variable

For Mac or Linux

./startMonetDb.sh DB_NAME DB_FARM_LOCATION MONETDB_CMD MONETDBD_CMD MONETDB_PORT PASSPHRASE
Parameter Mac Default Value Linux Default Value Comment
DB_NAME pentaho-instaview    
DB_FARM_LOCATION $HOME/.kettle/instaview/dbfarm $HOME/.kettle/instaview/dbfarm  
MONETDB_CMD /usr/local/monetdb/bin/monetdb /usr/bin/monetdb  
MONETDBD_CMD /usr/local/monetdb/bin/monetdbd /usr/bin/monetdbd  
MONETDB_PORT 50000 50000 To avoid port conflicts, this port number is sometimes changed to 50006.
PASSPHRASE monetdb monetdb Required for remote control of Monetdb

Also, check the AgileBI shared connection in PDI that Instaview creates and uses to talk to Monetdb. You can open this connection in PDI and test or explore it. This connection is used by the templates. If you open the .ktr file used by Instaview you can run it that way as long as Monetdb is running.

 

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.