This is a quick set of instructions for setting up an environment to use Analyzer with MongoDB using the foodmart sample data.
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.
- Create a directory called foodmart_data in your C:\ directory.
- Navigate to C:\Program Files\pentaho\server\biserver-ee\pentaho-solutions\system\samples.
- Extract the mondrian-data-foodmart-json-0.3.3.zip file into the newly created foodmart_data directory.
- 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.
- Login to the User Console using the admin username and password.
- Open the Browse perspective by selecting this from the upper-left menu.
- 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.
- 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.
- 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.
| ||Yes||A user-friendly caption given to this connection. This is the name which will be shown to users.|
| ||Yes||The fully qualified name of the class which will serve as a connectivity driver. To connect to MongoDB through OLAP, use |
| ||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=(...)
- Open the
olap4j.propertiesfile with a text editor.
- If applicable, make sure to uncomment the necessary values in the properties file by removing the # symbols from the text.
- Define a unique
[connection name].namefor the data connection.
- Enter the fully qualified name for the
- Add the connection details (
[connection name].connectString) to your MongoDB by defining the properties shown in this table.
- After all properties are defined, save and close the
- 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.
| ||Replace |
| ||Governs whether the |
| ||Indicates whether to use GSSAPI credentials. Defaults to |
| ||The variable name that may specify the authentication mode to use when creating a JAAS |
| ||The variable name that may specify the location of the keytab file to use when authenticating with "KERBEROS_KEYTAB" mode.|
| ||Replace |
| || |
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
| ||This property is ignored and only supplied for compatibility reasons.|
| ||Do not modify this property.|
|The following list is for optional configuration items.|
| ||The maximum number of allowed MongoDB connections.|
| ||The MongoDB connection timeout in millis. 0 means no timeout.|
| ||The max wait for a MongoDB connection to be available (millis).|
| ||Boolean which controls whether MongoDB connections are kept alive through firewalls.|
| ||Socket timeout (millis). 0 (default) means no timeout|
| ||Read preference to use: |
| ||A comma separated list of JSON docs representing the tag set to associate with |
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.
| ||Specifies the number of servers to wait on before acknowledging a write. Corresponds to the String parameter of the constructor |
| ||Specifies the write timeout in millis for the |
| ||A |
| ||A |
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.
- Open the olap4j.spring.xml file with a text editor and locate the section for olap4jConnectionRemovalList.
- Add the name of the connection that you want to remove in the <value></value> tag.
- Save and close the file.
- 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 ConnectionMongoDB connections for Analyzer are configured in the
olap4j.propertiesfile. 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 ServiceIf 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.
- Navigate to the .../pentaho-solutions/system directory and open the
pentahoObjects.spring.xmlfile with a text editor.
- Locate the
IPasswordServicebean in the
<bean id="IPasswordService" class="org.pentaho.platform.util.Base64PasswordService" scope="singleton"/>
- Change to Cipher encryption by editing the bean's class attribute.
<bean id="IPasswordService" class="org.pentaho.platform.engine.security.CipherEncryptionService" scope="singleton"/>
- Save and close the
Encrypt Your PasswordThese steps show you how to use the Pentaho server to obtain the encrypted version of your password.
- 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.
- The password encryption page contains a simple form with a text box for your MongoDB password. Enter your password and click Submit.
- 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:
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.