MongoDB Overview

The ElastiCube Manager enables easy and quick access to databases, tables and views contained within MongoDB databases.

Sisense provides a MongoDB connector for the ElastiCube.

Note: Before connecting to MongoDB database with Sisense, please note that MongoDB is an unstructured database, and therefore tables may be flattened with additional tables being created for nested items.

To connect and define MongoDB, follow these steps:

  1. Download the MongoDB ODBC Driver and connect to the data source in the ElastiCube Manager.
  2. Add a DSN.
  3. Review and edit the DSN schema.
  4. Preview and select the tables generated by the MongoDB ODBC Driver.
  5. (Optional) Change advanced DSN configurations.
  6. (Optional) Connect to MongoDB ODBC Driver using a Connection String (alternative method to step 2 above).
  7. Versions

Upgrading to MongoDB v2.0

The latest version of MongoDB provides enhanced security and improved handling for complex MongoDB models.

The latest version of MongoDB makes significant breaking changes, if you use a version of MongoDB prior to version 2.0, and you are satisfied with your service, you can continue without upgrading.

When to Upgrade?

In certain circumstances it is recommended or necessary that you upgrade your driver for  MongoDB version 2.0, for example:

  1. You are new to Sisense and MongoDB
  2. You want to connect with the Sisense Native REST Connector
  3. You experience security-related issues in MongoDB v1.0
  4. You have a really complex MongoDB model that v1.0 can’t handle

Before Upgrading

When upgrading to MongoDB v2.0, your current DSN names configured locally may be deleted. As a workaround, you can export the ODBC registry entry and then import after completing the upgrade.

To migrate your ODBC Data Source registry file:

  1. In Windows, open the Run box with by pressing the Windows key + r.
  2. In the Run line, enter regedit.
  3. Click OK.
  4. Navigate to the ODBC Data Source registry entry at the following location:
    HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources
  5. Right click the entry and select Export.
    regexport
  6. Save the registry file on your machine.
  7. After installing MongoDB v2, click the exported registry file. The Register Editor warning message is displayed.
    regedit
  8. Click Yes to import the entry.

After Upgrading

After upgrading to MongoDB v2.0, note the following:

  1. The new ODBC schema creation is different than v1.0. MongoDB v2.0 will not create virtual tables (_vt_ tables) in the ElastiCube Manager. As a result, your current ElastiCube schema will not match schemas created before upgrading. Create a new schema using the new v2.0 DSN.
  2. The MongoDB v2.0 driver saves a JSON format of the extracted schema, as opposed to the v1.0 driver which uses XML.
  3. The MongoDB v2.0 driver stores the schema extracted in MongoDB by default. This allows  multiple users to work with the same extracted schema from different servers without extracting a new schema individually each time. However, any changes to the schema affects the schema for everyone else.
  4. Storing the schema in the MongoDB means your DB is not ReadOnly. Administrators should be aware of this and protect their database with authentication and access control, or communicate this store action and its effects to all users.

Downloading and Connecting to the MongoDB ODBC Driver

To connect to MongoDB, complete the following procedures:

  1. Download and install the MongoDB ODBC Driver.
  2. Install the MongoDB driver. Note: If you have previously installed an earlier version of the MongoDB driver, you need to uninstall it first, and then install this version.
  3. In the ElatiCube Manager, click Add Data, and then Generic ODBC Driver.
  4. Select DSN. If a DSN file has already been created, look for it in the dropdown list, and click Test Connection.
  5. If you need to add a DSN, see the next section. If your connection has been set up, you can click OK and review the tables generated by the MongoDB ODBC Driver.

png;base644de15833e2ace415

Adding a DSN

If a DSN has not yet been created, you will need to add it as follows:

  1. Click Add DSN.

Note: To add a DSN you must run the Sisense ElastiCube Manager as an administrator.

  1. Select the System Data Source option.  The created file will apply to all users in a specific machine only. Click Next. 
    png;base644de15833e2ace415
  2. Select the previously installed Sisense MongoDB ODBC Driver, and click Next, and then Finish.

create new data

  1. Open the MongoDB ODBC Driver from the ODBC Data Source Administrator.
  2. Enter the following information:
    FieldDescription
    Data Source NameEnter your name for the DSN that is to be displayed in the DSN dropdown list.
    Description(Optional) Enter your description of the DSN.
    ServerEnter the name or IP address of the host where your MongoDB instance is running.
    PortEnter the number of the TCP port that the server uses to listen for client connections.
    DatabaseEnter the name of the database that you want to access.
    Connect to Replica SetIf you are connecting to a replica set in your MongoDB implementation, select the Connect to Replica Set check box and then do the following:

    Replica Set Name: Enter the name of the replica set (this is a required field).
    Secondary Servers: Enter a comma-separated list of the servers in the replica set. You can indicate the TCP port that each server is using to listen for client connections by appending a colon (:) and the port number to the server name or IP address.
    Read Preference: Select the appropriate option to specify how the driver routes read operations to the members of a replica set.
  3. (Optional) If the database that you are connecting to requires authentication, then use the options in the Authentication area to configure authentication as needed. For more information, see Authentication Options.
  4. (Optional) To configure advanced driver options, click Advanced Options. For more information, see Advanced Options.
  5. (Optional) To configure logging behavior for the driver, click Logging Options. For more information, see Logging Options.
  6. Click Test. A pop window is displayed that indicates the connection was successful.
  7. Click OK.
  8. In the ODBC Connectivity Properties window, click Test Connection.
  9. Click OK to save and close the ODBC Data Source Administrator.

DSN Schema Definition

  1. Open the MongoDB ODBC Driver from the ODBC Data Source Administrator.
  2. Click Schema Definition to review the schema and make changes if needed:
  • External schema file: The driver automatically generates the schema if not manually defined (path to file is displayed). To edit an existing DSN schema, click Browse to locate and open the file in the ODBC Data Source Administrator.
  • Export options include:
    • Export Existing: Exports metadata that has already been generated.
    • Generate All: Exports metadata for all the tables in the database.
    • Generate Missing: Exports metadata for tables that have not been included in the schema.

pngbase64b9edec97add7b373_1

  • Edit Schema File: Use this option to edit the schema before updating metadata (change field types, hide columns etc.).

edit schema

  • Upload Metadata: Updates metadata after settings are complete. Current metadata source displays the source that has been set in Advanced Options.
  • Clear Existing Metadata: Deletes all the metadata that the driver has generated for the MongoDB instance.
  • Virtual Tables Options: Sisense enables virtual tables creation by default. Virtual tables are created when Sisense detects an array within a field of the main table. Virtual tables can be disabled or custom configured. By default, virtual tables will receive the name MAINTABLE_vt_FIELD and the main table will receive the suffix “main”. We recommend leaving these settings as is.
    png;base642f771fe853862cb2
  1. When done, click Test Connection, and OK.

Selecting Tables in the ElastiCube Manager

After setting up the DSN, you can preview and customize the query (similar to other ODBC connections) of the imported MongoDB table.

In the ElastiCube Manager You can view the SQL syntax in the Query Preview section. Click Edit to customize it.

Select the tables created by the Sisense MongoDB ODBC Driver (tables including MAINTABLE_vt_FIELD), as opposed to the table that includes all the raw data (with suffix main).

mongo table

Connecting to Sisense MongoDB ODBC Driver using a Connection String

If you have a driver already defined, then you can connect to Sisense MongoDB ODBC Driver using strings (without using a DSN).

  1. Select Connection String (DSN-Less).

png;base64fcec2941347a2a69

  1. Type in the connection details as strings, as follows:

Example with minimum required attributes

Driver=Sisense MongoDB ODBC Driver;
Host=localhost;
Port=27017;
Database=aff;

Example with with advanced options:
Driver=Sisense MongoDB ODBC Driver

Host=192.168.100.100;
Port=27017;
Database=MyDatabase;
UID=MyUsername;
PWD=MyPassword;
RowsFetchedPerBlock=4096;
DefaultStringColumnLength=255;
DefaultContainerColumnLength=511;
UseSqlWVarchar=0;
CacheMetadata=1; VirtualTableDetection=1
  1. When done, click Test Connection, and OK.

Authentication Options

Some MongoDB databases require authentication. You can configure the Simba MongoDB ODBC Driver to provide your credentials and authenticate the connection to the database using one of the following methods:

Using SCRAM-SHA-1

You can configure the driver to use the SCRAM-SHA-1 protocol to authenticate the connection. SCRAM-SHA-1 is the default authentication protocol used by MongoDB.

To configure SCRAM-SHA-1 authentication:

  1. To access authentication options, open the ODBC Data Source Administrator where you created the DSN, select the DSN, and then click Configure.
  2. In the Mechanism drop-down list, select MongoDB User Name and Password.
  3. To use a database other than the admin database to check your credentials, type the name of the database in the Authentication Source field.
  4. In the Username field, type an appropriate user name for accessing the MongoDB database.
  5. In the Password field, type the password corresponding to the user name you typed above.
  6. Encrypt your credentials by doing one of the following:
    • If the credentials are used only by the current Windows user, select Current User Only.
    • Or, if the credentials are used by all users on the current Windows machine, select All Users Of This Machine.
  7. To save your settings and close the dialog box, click OK.

Using Kerberos

You can configure the driver to use the Kerberos protocol to authenticate the connection.

Kerberos must be installed and configured before you can use this authentication mechanism. For information about how to install and configure Kerberos, see the MIT Kerberos Documentation: http://web.mit.edu/kerberos/krb5-latest/doc/.

To configure Kerberos authentication:

  1. To access authentication options, open the ODBC Data Source Administrator where you created the DSN, select the DSN, and then click Configure.
  2. In the Mechanism drop-down list, select Kerberos.
  3. In the Service Name field, type the service name of the MongoDB server.
  4. To save your settings and close the dialog box, click OK.

Using LDAP

You can configure the driver to use the LDAP protocol to authenticate the connection.

To configure LDAP authentication:

  1. To access authentication options, open the ODBC Data Source Administrator where you created the DSN, select the DSN, and then click Configure.
  2. In the Mechanism drop-down list, select LDAP.
  3. In the Username field, type an appropriate user name for accessing the MongoDB database.
  4. In the Password field, type the password corresponding to the user name you typed above.
  5. Encrypt your credentials by doing one of the following:
    • If the credentials are used only by the current Windows user, select Current User Only.
    • Or, if the credentials are used by all users on the current Windows machine, select All Users Of This Machine.
  6. To save your settings and close the dialog box, click OK.

DSN Advanced Options

You can configure advanced options to modify the behavior of the driver.

To configure advanced options:

  1. To access advanced options, open the ODBC Data Source Administrator where you created the DSN, then select the DSN, then click Configure, and then click Advanced Options.
  2. To retrieve data using double-buffering instead of single-buffering, select the Enable Double-Buffering check box. You can configure the buffer size using the Documents to fetch per block field.
  3. In the Documents to fetch per block field, type the maximum number of documents that a query returns at a time. This setting also determines the buffer size used when double-buffering is enabled.
  4. To return MongoDB String data as SQL_WVARCHAR instead of SQL_VARCHAR, select the Expose Strings as SQL_WVARCHAR check box.
  5. In the String Column Size field, type the maximum data length for String columns.
  6. To return MongoDB Binary data as SQL_LONGVARBINARY instead of SQL_VARBINARY, select the Expose Binary as SQL_LONGVARBINARY check box.
  7. In the Binary Column Size field, type the maximum data length for Binary columns.
  8. To configure the driver to optimize joins between virtual tables and pass filtering and aggregation optimizations to the MongoDB database for handling, select the Enable Passdown check box.
  9. Use the options in the Metadata area to specify the schema definition to use when connecting to the database:
    • To configure the driver to use a schema definition stored in a JSON file, select Local File from the Mechanism drop-down list, and then click Browse and select the JSON file that you want to use.
    • To configure the driver to use a schema definition stored in the MongoDB database that you are connecting to, in the Mechanism drop-down list, select Database.
  10. Use the options in the Sampling area to configure how the driver samples data to generate temporary schema definitions:
    • In the Sampling Method list, select the direction in which the driver reads data during sampling. For example, if you select Forward, the driver samples data starting from the first record in the database, then samples the next record, and so on.
    • In the Documents to sample field, type the maximum number of documents that the driver can sample to generate the schema definition. To sample every document in the database, type 0.
  11. In the Step Size field, type the interval at which the driver samples a record when scanning through the database. For example, if you set this option to 2, then the driver samples every second record in the database starting from the first record.
  12. To configure write-back behavior in the driver, click Writeback Options.
    • In the Batch Size field, type the maximum number of documents that the driver can handle at one time during a write operation.
    • Use the options in the Write Concern Settings area to configure how the driver reports the success of a write operation:
      • In the Write Concern field, type the total number of primary and secondary servers that must acknowledge a write operation in order for the driver to report a successful write operation.
    • In the Timeout field, type the maximum number of seconds that the driver waits for a secondary server to acknowledge a write operation before reporting that the operation has failed.
    • To require the data to be committed to the journal before a write operation can be acknowledged, select the Journaled Writes check box.
  13. To save your settings and close the Advanced Options dialog box, click OK.
  14. To close the Simba MongoDB ODBC Driver DSN Setup dialog box, click OK.

Logging Options

To help troubleshoot issues, you can enable logging. In addition to functionality provided in the Simba MongoDB ODBC Driver, the ODBC Data Source Administrator provides tracing functionality.

To enable driver logging:

  1. To access logging options, open the ODBC Data Source Administrator where you created the DSN, then select the DSN, then click Configure, and then click Logging Options.
  2. From the Log Level drop-down list, select the logging level corresponding to the amount of information that you want to include in log files:
    FieldDescription
    Log LevelSelect the Log Level. There are seven possible options:

    LOG_OFF: Disables all logging.
    LOG_FATAL: Logs very severe error events that lead the driver to abort.
    LOG_ERROR: Logs error events that might still allow the driver to continue running.
    LOG_WARNING:Logs potentially harmful situations.
    LOG_INFO: Logs general information that describes the progress of the driver.
    LOG_DEBUG: Logs detailed information that is useful for debugging the driver.
    LOG_TRACE: Logs more detailed information than the DEBUG level.
    Log PathEnter the full path to the folder where you want to save log files.
    OR
    Click Browse and select the folder where you want to save log files.
    Log RotationEnter the maximum number of log files to keep in the Max Number Files field.

    Note: After the maximum number of log files is reached, each time an additional file is created, the driver deletes the oldest log file.

    Enter the maximum size of each log file in megabytes (MB) in the Max File Size field.

    Note: After the maximum file size is reached, the driver creates a new file and continues logging.
  3. In the Log Path field, specify the full path to the folder where you want to save log files. You can type the path into the field, or click Browse and then browse to select the folder.
  4. In the Max Number Files field, type the maximum number of log files to keep.
  5. In the Max File Size field, type the maximum size of each log file in megabytes (MB).
  6. Click OK.
  7. Restart your ODBC application to make sure that the new settings take effect.

The MongoDB ODBC Driver produces a log file named mongodbodbc_driver.log at the location that you specify in the Log Path field.

SSL Options

If you are connecting to a MongoDB server that has Secure Sockets Layer (SSL) enabled, then you can configure the driver to connect to an SSL-enabled socket. When connecting to a server over SSL, the driver supports identity verification between the client and the server.

Configuring an SSL Connection without Identity Verification

You can configure a connection that uses SSL but does not verify the identity of the client or the server.

To configure an SSL connection without verification:

  1. To access the SSL options for a DSN, open the ODBC Data Source Administrator where you created the DSN, then select the DSN, then click Configure, and then click SSL Options.
  2. Select the Enable SSL check box.
  3. Select the Allow Self-Signed Certificates check box.
  4. To save your settings and close the dialog box, click OK.

Configuring One-way SSL Verification

You can configure one-way verification so that the client verifies the identity of the MongoDB server.

To configure one-way SSL verification:

  1. To access the SSL options for a DSN, open the ODBC Data Source Administrator where you created the DSN, then select the DSN, then click Configure, and then click SSL Options.
  2. Select the Enable SSL check box.
  3. Choose one:
    • To verify the server using a certificate from a specific PEM file, in the Certificate Authority File field, specify the full path of the PEM file.
    • Or, to verify the server using certificates stored in multiple PEM files, in the Certificate Authority Directory field, specify the full path to the directory where the PEM files are located.
  4. In the Certificate Revocation List File field, specify the full path of the PEM file containing the list of revoked certificates.
  5. To save your settings and close the dialog box, click OK.

Configuring Two-way SSL Verification

You can configure two-way SSL verification so that the client and the MongoDB server verify each other.

To configure two-way SSL verification:

  1. To access the SSL options for a DSN, open the ODBC Data Source Administrator where you created the DSN, then select the DSN, then click Configure, and then click SSL Options.
  2. Select the Enable SSL check box.
  3. In the PEM Key File field, specify the full path of the PEM file containing the certificate for verifying the client.
  4. If the client certificate is protected with a password, type the password in the PEM Key Password field.
  5. Choose one:
    • To verify the server using a certificate from a specific PEM file, in the Certificate Authority File field, specify the full path of the PEM file.
    • Or, to verify the server using certificates stored in multiple PEM files, in the Certificate Authority Directory field, specify the full path to the directory where the PEM files are located.
  6. In the Certificate Revocation List File field, specify the full path of the PEM file containing the list of revoked certificates.
  7. To save your settings and close the dialog box, click OK.

 Versions

VersionRelease DateImprovements
6.6.1.2May 19, 2017Support added for MongoDB 3.4
For more information, see the complete MongoDB Release Notes