Skip to main content
Pentaho Documentation

Set Up MongoDB for Analyzer

This is a quick set of instructions for setting up an environment to use Analyzer with MongoDB using the foodmart sample data.

Prerequisite: Before you begin, you should have installed Pentaho BA Server and MongoDB.

Import Foodmart Sample Data

Now that you have MongoDB and the latest Pentaho software installed, it is time to import the Foodmart sample data. These steps are for importing on Windows; if you are using Linux, you can modify them to suit your OS.

  1. Create a directory called foodmart_data in your C:\ directory.
  2. Navigate to C:\Program Files\pentaho\server\biserver-ee\pentaho-solutions\system\samples.
  3. Extract the mondrian-data-foodmart-json-0.3.3.zip file into the newly created foodmart_data directory.
  4. Open the command line interface. Import these four collections used in the Foodmart.mongo.xml schema by executing these commands.
C:\mongodb-win32-x86_64-2008plus-[insert current vernum]\bin\mongoimport -db
 foodmart --collection sales --type json --file
  c:\foodmart_data\sales_fact_1997_collapsed.json
C:\mongodb-win32-x86_64-2008plus-[insert current vernum]\bin\mongoimport -db
 foodmart --collection sales_transactions --type json--file
  c:\foodmart_data_sales_transactions.json
C:\mongodb-win32-x86_64-2008plus-[insert current vernum]\bin\mongoimport -db
 foodmart --collection agg_g_ms_pcat_sales_fact_1997 --type json--file
  c:\agg_g_ms_pcat_sales_fact_1997.json
C:\mongodb-win32-x86_64-2008plus-[insert current vernum]\bin\mongoimport -db
 foodmart --collection agg_c_10_sales_fact_1997 --type json--file
  c:\agg_c_10_sales_fact_1997.json

Upload a Schema

After completion of your data model, the easiest way to upload the schema into the Pentaho BA server repository is through the user console. A schema can be deployed in other ways; however, the console is the quickest and easiest method. The Foodmart.mongo.xmlschema can be found at .../pentaho/server/biserver-ee/pentaho-solutions/system/samples.

  1. Login to the User Console using the admin username and password.
  2. Open the Browse perspective by selecting this from the upper-left menu.
  3. In the Folders panel, select the location where you want to store the schema. Click on Upload... in the Folder Actions panel. The Upload dialog box appears.
  4. In the dialog box, click Browse to go to the location of the schema for upload. Double-click on the schema. If needed, specific permissions are set on the schema by using the Advanced Options settings.
  5. Click OK. The schema is uploaded and available to specified users.

Define the Data Connection

After you have uploaded the schema to your BA repository, you need to define your connection to MongoDB.

To do this, you need to locate the olap4j.properties file in the .../pentaho-solutions/system directory. This file provides all of the information about connections that the platform needs to create. There can be multiple connections defined in this file, but each of them must have a unique name.

The connections are created when the BA server is started, but will not be updated if the file is modified while the server is running. If you need to make modifications to the file, you must restart the BA server so that the changes take effect.

These are the properties that you define for each connection.

Property Required Description
[connection name].name Yes A user-friendly caption given to this connection. This is the name which will be shown to users. 
[connection name].className Yes The fully qualified name of the class which will serve as a connectivity driver. To connect to MongoDB through OLAP, use

org.pentaho.platform.plugin.services.connections.

PentahoSystemDriver.

[connection name].connectString Yes This property supplies the connection details to your MongoDB instance as well as the path to your schema file. This is also where you define the credentials to use if your MongoDB instance requires it.  

This template can be used as a starting point for defining your connection.


[connection name].name=mongoFoodmart

[connection name].className=org.pentaho.platform.plugin.

services.connections.PentahoSystemDriver

[connection name].user=aUser

[connection name].password=aPassword

[connection name].connectString=jdbc:mondrian4:Host=localhost;dbname=foodmart;

DataServicesProvider=com.pentaho.analysis.mongo.MongoDataServicesProvider;

Catalog=C:/foodmart_data/FoodMart.mongo.xml;

Follow this naming convention to make multiple connections.


connection1.name=MyFirstConnection
connection1.url=(...)
-------------
connection2.name=MySecondConnection
connection2.url=(...)
-------------
connection3.name=MyThirdConnection
connection3.url=(...)
  1. Open the olap4j.properties file with a text editor.
  2. If applicable, make sure to uncomment the necessary values in the properties file by removing the # symbols from the text.
  3. Define a unique [connection name].name for the data connection.
  4. Enter the fully qualified name for the [connection name].className.
  5. Add the connection details ([connection name].connectString) to your MongoDB by defining the properties shown in this table.
  6. After all properties are defined, save and close the olap4j.properties file.
  7. Test your data connection by launching the user console, clicking on Create New > Analysis Report, then verifying that the MongoDB connection appears in the Select Data Source dialog box.

If your data source does not show up in the Data Sources list, restart the BA Server. It may be necessary to work with your system administrator and check the Analyzer logs for the purpose of troubleshooting the connection.

Property Action
Host=localhost Replace localhost with the address of your MongoDB server. This can be an IP address or a fully qualified URL.
USE_ALL_REPLICA_SET_MEMBERS Governs whether the MongoClient is instantiated with a List of ServerAddresses, indicating the driver should act against a replica set, even if there's only a single server specified. If there are two or more servers specified in HOST, a List of ServerAddress will be used, even if this property is unset or false. This property is useful primarily for the case where a single server is specified but replica-set behavior is desired.
USE_KERBEROS Indicates whether to use GSSAPI credentials. Defaults to false unless the string value can be parsed as true.
PENTAHO_JAAS_AUTH_MODE The variable name that may specify the authentication mode to use when creating a JAAS LoginContext. Possible values are KERBEROS_USER, KERBEROS_KEYTAB, and EXTERNAL.
PENTAHO_JAAS_KEYTAB_FILE The variable name that may specify the location of the keytab file to use when authenticating with "KERBEROS_KEYTAB" mode.
dbname=foodmart Replace foodmart with the name of the database to use that is located on your MongoDB server.
Catalog=/home/admin/pentaho-mongolap/test-data/FoodMart.mongo.xml

Add the path to your schema in this property. If you uploaded your schema into the BA server repository, you can prefix the path to the schema by using solution:/ .

Tip: An alternative method is to replace /home/admin/pentaho-mongolap/test-data/FoodMart.mongo.xml with the path to your schema file.
jdbc This property is ignored and only supplied for compatibility reasons.
DataServicesProvider=com.pentaho... Do not modify this property.
The following list is for optional configuration items.
connectionsPerHost=100 The maximum number of allowed MongoDB connections.
connectTimeout=100000 The MongoDB connection timeout in millis. 0 means no timeout.
maxWaitTime=120000 The max wait for a MongoDB connection to be available (millis).
socketKeepAlive=false Boolean which controls whether MongoDB connections are kept alive through firewalls.
socketTimeout=0 Socket timeout (millis). 0 (default) means no timeout
readPreference=primary Read preference to use: [primary, primaryPreferred, secondary, secondaryPreferred, nearest]. If set to anything besides primary, the preference is "taggable", meaning the tag sets defined in the MongoProp.tagSet property will be used if present.
tagSet=... A comma separated list of JSON docs representing the tag set to associate with readPreference.

For example, { "disk": "ssd", "use": "reporting", "rack": "a" },{ "disk": "ssd", "use": "reporting", "rack": "d" }. This governs the preferred order when determining which MongoDB to read from.

The article Configure Replica Set Tag Sets has more detailed information. Note that the expected syntax for tag sets differs from the format specified for MongoURI, which uses semi-colons as a delimiter.

writeConcern=2 Specifies the number of servers to wait on before acknowledging a write. Corresponds to the String parameter of the constructor {@link com.mongodb.WriteConcern(String, int, boolean, boolean)}.
wTimeout=10000 Specifies the write timeout in millis for the writeConcern.
JOURNALED A true|false property indicating how WriteConcern should be configured. For example, indicating whether to block until write operations have been committed to the journal. This will cause an exception if set to true when connecting to a server without journaling in use.
useSSL A true|false property indicating whether connections to MongoDB should use SSL. It is assumed that the SSL certificate used on the host is signed by a trusted authority. If you are using a self-signed certificate you will need to import your certificate into the keystore for your JVM.
 

Delete the Data Connection

To remove the MongoDB connection from Analyzer, you will need to edit the olap4j.spring.xml file, which is found in the same directory as olap4j.properties.../pentaho-solutions/system.

  1. Open the olap4j.spring.xml file with a text editor and locate the section for olap4jConnectionRemovalList.
  2. Add the name of the connection that you want to remove in the <value></value> tag.
  3. Save and close the file.
  4. Restart the BA Server so that the changes will take effect.

<bean id="olap4jConnectionRemoveList" class="java.util.ArrayList">
    <constructor-arg>
      <list>
          <!--uncomment the value below and change the text to remove 
              existing olap4j connections-->        
          <!--<value>mongoFoodmart</value>-->          
              <value>mongolap2</value>
      </list>   
    </constructor-arg> 
 </bean>

Implement Encrypted Passwords for the Connection

MongoDB connections for Analyzer are configured in the olap4j.properties file. This connection information contains passwords for connecting to your database. There are two ways to encrypt your passwords by default - Base64 Encoding and Cipher Encryption. You may also supply custom Java code for encryption.

Configure Your Password Service

If you do not want to use the default Base64 encoding value for your password service, change it to the Cipher encryption method using these steps.
  1. Navigate to the .../pentaho-solutions/system directory and open the pentahoObjects.spring.xml file with a text editor.
  2. Locate the IPasswordService bean in the pentahoObjects.spring.xml file.
    
    <bean id="IPasswordService"
    
          class="org.pentaho.platform.util.Base64PasswordService"
    
          scope="singleton"/>
  3. Change to Cipher encryption by editing the bean's class attribute.
    
    <bean id="IPasswordService"
    
          class="org.pentaho.platform.engine.security.CipherEncryptionService"
    
          scope="singleton"/>
    
  4. Save and close the pentahoObjects.spring.xml file.

Encrypt Your Password

These steps show you how to use the Pentaho server to obtain the encrypted version of your password.
  1. Make sure that your server is running. Then, open the password encryption page by accessing the following URL, replacing <yourhost:port> with your host and port.
    http://<yourhost:port>/pentaho/api/password/encrypt
    
  2. The password encryption page contains a simple form with a text box for your MongoDB password.  Enter your password and click Submit.
  3. The text that is returned after you click Submit is your encrypted password.

Now you can use the encrypted version of your password in the olap4j.properties. The BA server automatically determines if your password is encrypted or not. For example: username=admin;password=ENC:cGFzc3dvcmQ= .

Test the Connection by Building a Report

The easiest way to test the connection and setup is to build your first report with MongoDB for Analyzer as shown in our walk-through tutorials, under  Get Started with Analyzer Reports.